How do I display a calculated field in read only grid based on a date less days

[lisaanderson]

I hope someone can help me. I've been working on this problem for 2 days and have been reluctant to post because I didn't get a single response on my last 4 cries for help. If someone can get me going on this, I'd really appreciate it.

This is my problem. I have a read only grid that alerts the user if certain tasks are past due. An administrator can maintain the value for number of days used to determine date past due so I have that stored in an administrative table. Initially I set up my grid (just to get it going) with this value hard-coded in my calculated fields expression in my dbf query and everything worked great. I got a result set with a "varnotifydt" for each record that was calculated by subtracting the hard-coded number of days. It displayed nicely in the grid.

I went in to clean this up and found that I needed to set up the number of days in a session variable. All good. Then I found I couldn't use that variable in my dbf query definition. I did set it up as an argument and was able to get the grid to filter correctly. I still want to show the date in my grid that was the calculated date. I've tried to use the calculated field on one of my grid variables. I can get a lot of values to work here, but not varnotifydt - number of days. I understand that this is because it is looking for javascript (varnotifydt - numberofdays works in xbasic). I've tried adding a button, creating a javascript function (ajaxcallback) and using the e._set object and can't get that to work. I don't even know if I should be using the calculated fields area to accomplish this.

I'd really like to have the calculated value in my query results. If I can't have that, I'd like to have the date calculated as the grid is being built (I did try server side and client side events to no avail). I really don't want a button. This seems like it should be such a common and easy thing to do. I'm still learning.

Thanks, (a very discouraged) Lisa

[chadbrown]

Re: How do I display a calculated field in read only grid based on a date less days

Maybe this will help you

http://javascript.about.com/library/bladddays.htm

or maybe

http://www.javascriptkata.com/2007/0...in-javascript/

[Keith Hubert]

Re: How do I display a calculated field in read only grid based on a date less days

Lisa,

Why cant you create a calculated field in the grid builder, where you Query the table? You can then use that result in the grid.

[lisaanderson]

Re: How do I display a calculated field in read only grid based on a date less days

Keith,

That's what I wanted, but I can't access the session variables in the expression builder. I get an error if I try to force it by typing the variable name and then pressing OK. I've read some posts about session variables not being available at that time because the program isn't running. Am I missing something here?

Lisa

[Keith Hubert]

Re: How do I display a calculated field in read only grid based on a date less days

Hi Lisa,

I've read some posts about session variables not being available at that time because the program isn't running.

That is correct, that is why you create a calculated field with values from fields in the table.

Do you want to know "how many days" before or after data is entered into the grid?

[Davidk]

Re: How do I display a calculated field in read only grid based on a date less days

Lisa,

Can you post a screen shot of your grid? I'd love to see what you need but can't quite get a grasp of it.

[lisaanderson]

Re: How do I display a calculated field in read only grid based on a date less days

This is the grid that I'm working on. The notify date isn't showing correctly though. The same value (from my session variable) should be subtracted from every row in the grid. The grid is filtered correctly, and I can hardcode a number to subtract and it works. Keith, I can't get this set up as a calculated field in the query for the grid because it's not a linked table. It has one record in one table. If I try to type session.varnotifydt as part of a calculated field in my query definition, it shows an error exists in my expression and will not save it (even though I found in a help screen somewhere where it said that should work). So I abandoned getting this date calculated in my query. Next I've been working with an ajax callback function called in the onRowRender client side event ({grid.Object}.ajaxCallback('G',e.rowNumber,'getnotedt','','_getData=true');).

Here is the code in my function:
function GetNoteDt as c (e as p)

dim ndate as c
dim ddate as d
dim chardate as c
dim nbrofdays as n

nbrofdays = session.varannydue
ndate = e._currentrowdatanew.varnotifydt
ddate = ctod(ndate) - nbrofdays
chardate = cdate(ddate)

e._set.varnotifydt.value = ddate

end function

The session variable isn't available to me here (I get an error). If I hardcode a number, it works perfectly so I know everything else is right. I have thoroughly looked through the e object and can't find any way to get the value I'm looking for.

I found some code on this forum that Selwyn used to solve this situation: <input type="hidden" id="mysessionvar" value="<%a5 ?session.user_id_selected %>" />. I changed the session variable in his example to mine and got the correct value to display on my screen (changing "hidden" to "text"). The only way that works is doing live preview and I get errors using working preview. Even if I accept that working preview will always show errors, I can't find a way to access "mysessionvar" from within the ajax callback funtion. Selwyn's example: (in your javascript, you can now read the value of 'mysessionvar': var ele = $('mysessionvar'); var user_A = $gvs(ele); is for javascript, but I'm trying to use xbasic in the ajax callback. Does that all make sense?

date example.jpg

[Keith Hubert]

Re: How do I display a calculated field in read only grid based on a date less days

Lisa,

I can't get this set up as a calculated field in the query for the grid because it's not a linked table.

Is this a grid or a Dialog? If it is a grid, how can it not be linked to a table? Or, is the table SQL?

[Davidk]

Re: How do I display a calculated field in read only grid based on a date less days

Not sure if I have this right. You're working with DBF tables (although it doesn't matter that much). You have a table that has one record - the number of days used to determine a past due date. You want to take your Plan Year End Date and subtract the "Past Due Days" to create and display a Warning Date on a read only Grid. If that's so, then... excellent.

When your grid opens, put some code into the Server side event, onGridInitialize. I created a simple table named tbl_Days - it has one record with two fields - an ID field and a "daynbr" field which has the number of days to use in the calc. This code opens the table, reads the record, and assigns the "daynbr" to a session variable.

'The following code is for DBF Tables
'debug(1)
dim tbl as p
dim varDays as n
tbl = table.open("tbl_days")
query.filter = "Day_Id = 1"
indx = tbl.query_create()
records = indx.records_get()
If records = 0 then
	'We have a problem... the record is not in the table
else
	varDays = tbl.daynbr
end if
	
tbl.close()

Session.varDayNbr = varDays

Next, again in a Server side event, onExistingRowRender, we do all the rest of the work. No need to set up an Ajax Callback.

Dim warnDate as D

planDate = e.rowData.data("Plan_YearEndDate")
warnDate = planDate - Session.varDayNbr

e.javascript = "$('GRID1.V.R" + e.rowNumber + ".PLAN_WARNINGDATE').value = '" + warnDate + "';"

Adjust the code above in two spots...

1. Replace "Plan_YearEndDate" with your actual field name
2. Replace "PLAN_WARNINGDATE" with your actual field name

Here, as each row is rendered on the Server, we get the Year End Date, subtract our session variable from it and generate the javascript code to stuff it into our grid.

I hope that's what you need.

[lisaanderson]

Re: How do I display a calculated field in read only grid based on a date less days

David,

You understand what I'm trying to do perfectly. I actually had done exactly what you described in OnGridInitialize event, but the session variable isn't valid by the time I get to the OnExistingRowRender event?! I get an error that the "property doesn't exist" and "subelement doesn't exist." If I set the session variable (just to see if I could get the display to work) in the OnExistingRowRender event, my warndate variable gets set correctly, but my varNotifyDt doesn't get changed on my screen. Here is the value in e.javascript: $('GRID1.V.R1.VARNOTIFYDT').value = '01/14/2011';. One thing I notice in debug mode is that my varNotifyDt has curly brackets around the value in the date {01/28/2011}. Is that significant? Also, I've tried this but wasn't able to get it to work: varNotifyDt isn't being set to my new warning date value (1/14/2011). More thoughts?

[Davidk]

Re: How do I display a calculated field in read only grid based on a date less days

VARNOTIFYDT is the name of your Grid field?

[lisaanderson]

Re: How do I display a calculated field in read only grid based on a date less days

It is. It's a calculated field where I'm putting together a date from multiple fields in the table. I took your code and have been working with it and found that I was still not getting a value to display on the screen (even a hard-coded value) until I changed the control from a label to a non-editable text field. I have no idea why that made a difference. I'm still having problems with the session variable. I realized I get the "property doesn't exist" and "subelement doesn't exist" errors in working preview mode (with the session variable set in the OnGridInitialize event). With Live Preview it seemed to work, but when I published it, the wrong date was displayed. There's something I don't get about the session variables. I know I could set the session variables in my tabbed ui page and have the correct value show up in my query filter in this grid component, but I can't seem to access it anywhere else. How can I track the value of the session variables? I installed FireFox and the Firebug addin, but still couldn't see the session variables. I'm guessing that I'm not understanding something that is basic because it seems like this shouldn't be this difficult! I really appreciate all the help on this. It helps to be steered in the right direction because I've tried so many different approaches and it's hard to know when I'm on the right track.

[Davidk]

Re: How do I display a calculated field in read only grid based on a date less days

Session variables are not available to Working Preview, but you can put in a Javascript alert statement (if Javascript is available to the event you're in). For OnExistingRowRender you could put

e.javascript = "alert('" + Session.varDayNbr + "');"

If you're setting a session variable in OnGridInitialize, then it will be available to OnExistingRowRender.

As for your "Label" or a Text Box, the difference would be that the label doesn't have a "value" available. Instead, use "innerHTML"

e.javascript = "$('GRID1.V.R" + e.rowNumber + "._UNBOUND_STATICTEXT_1').innerHTML = '" + warnDate + "';"

However, if your Grid is running inside a Tabbed UI then your Grid field names are completely different.

You can see what they are above, running just in a Grid. A Grid in a Tabbed UI is...

e.javascript = "$('TBI_GRD_DBF_CA_71234SJKQ.V.R" + e.rowNumber + "._UNBOUND_STATICTEXT_1').innerHTML = '" + warnDate + "';"

You'll need to get info Firefox, Firebug, while running the Tabbed UI, to get the name of your Grid Field. I'm not sure if the name will change when published and run... I don't think so, but check it.

[lisaanderson]

Re: How do I display a calculated field in read only grid based on a date less days

Hmmm, a couple of things... I'm not sure about the other way of referencing the grid field in a Tabbed UI. I have several grid components that are run from a button on my tabbed ui, but the grid1 reference to the grid fields is working in Live Preview?! That tells me that I am referencing the field correctly, right? Concerning the innerHTML, I did try that when the field in the grid was a label and it still didn't work. The only thing that allowed the new value to display was changing the control to a textbox.

Basically it's doing what I want now except that the new calculated date is only showing up in Live Preview. If I publish it, the old date is shown.

[lisaanderson]

Re: How do I display a calculated field in read only grid based on a date less days

To wrap this up, I'm including the code that ended up working. Could it possibly be true that if I use a Tabbed UI component for my navigation page and have buttons that load grid components, that in the future when I have to change my code I'll have to use a different grid reference for testing in Working Preview and Live Preview, and then change it back for running in a browser? Am I missing something here? David, I really want to thank you for all your help.

Dim warnDate as d
dim planDate as D

planDate = e.rowData.data("varpyedate")

warndate = planDate - e.tmpl.session.varannydue
'Use the following line when testing changes and viewing them in Working Preview or Live Preview
'e.javascript = "$('GRID1.V.R" + e.rowNumber + ".VARCHARDT').innerHTML = '" + warnDate + "';"
'Use the following line when publishing and running the application in a browser
e.javascript = "$('TBI_PYANNYDUE_12643WYOZ.V.R" + e.rowNumber + ".VARCHARDT').innerHTML = '" + warnDate + "';"