SQL question: get a field value of a grid (billFileId) into a function or a script

[grandchoir]

Can I get a field value of a grid (billFileId) into a function or a script so I can update a date field in the grid when this row is updated?

I created the update statement using ?cn.GenerateUpdateStatement(ti) in the interactive window which appears to do what I’m trying to do. The query below is from the attached results of ?cn.GenerateUpdateStatement(ti).


UPDATE tbl_BillFiles SET dateRejected = :new.dateRejected WHERE billFileId = :old.billFileId


I know I can get the current date into the argument with:

dim dateRejected as t = now()
args.set(“dateRejected”, dateRejected)

But I don’t know how to get the billFileId (the primary key) from the selected row into the variable, and thus into the argument.

dim billFileId as c = ??????????
args.set(“billFileId”, billFileId)

Any help in the right direction would be greatly appreciated.

Daniel

[Jay Talbott]

Daniel,
You can pass the key field to the Xbasic Ajax callback in the javascript that calls the function. For example, if the list is called list1, you can get the key with this:

var billFileId = {Dialog.object}.getValue('list::list1::billFileId');
{Dialog.object}.ajaxCallback('','','XbasicFunctionName','','_billFileId='+billFileId);

and the variable will be available in the Xbasic function as e._billFileId

That should do it.

Jay

[grandchoir]

Jay,

Thank you so much for taking the time to respond to my question. I can't tell you how appreciative I am!.

I'm not as experienced as most, so it will take me a little bit to get this to work.

I've substituted my component names in your instructions:

var billFileId = {Dialog.object}.getValue('grid::RejectBillFiles_grid::billFileId');
{Dialog.object}.ajaxCallback('','','rejectCurrentRecord','','_billFileId='+billFileId);

and added

dim billFileId as c = e._billFileId

in the Xbasic.

I'll work on this until I understand it completely.

Thank you tremendously again!
Daniel

[Jay Talbott]

Make sure to use the Xbasic debugger to ensure that your desired value makes it to the Xbasic Ajax callback.
If you need help on how to do that, let us know.
Jay

[grandchoir]

Jay,

Thank you again for your tremendous help. I had the debug(1) in the function so I could see what was available once I got there. I don't know what I kept doing wrong while typing the JavaScript into the editor. I tried many different combinations, but I could never get to the function call. It seemed to have to do with the double colons.

I finally found a solution. Here is what I did to get the desired results. This was my first time to get this deep working with JavaScript along with Xbasic together. I can't thank you enough for helping me on this challenge. I believe I grew a lot in my experience with Alpha.

'JavaScript
var rn = {grid.object}._selectedRow;
var billFileId = {Dialog.object}.getValue('G','billFileId',rn);
{grid.object}.ajaxCallback('','','rejectCurrentRecord','','_billFileId='+billFileId);
{grid.object}.runAction('Update Grid',this);
'**********************************************************************************

'Xbasic
function rejectCurrentRecord as v (e as p)

'Dim the SQL connection variables
dim cn as SQL::Connection
dim ti as SQL::TableInfo
dim sqlUpdate as c
dim argsUpdate as SQL::Arguments

'Test for open connection
flag = cn.open("::Name::BillMonitorSQL")

'test if table exists
if .not. cn.GetTableInfo(ti,"tbl_BillFiles")
ui_msg_box("Error",cn.CallResult.text)
cn.close()
end
end if

'Dim the SQL query variables
dim billFileId as c = e._billFileId
dim dateRejected as t = now()

'Set the arguments for the SQL update query
argsUpdate.set("dateRejected",dateRejected)
argsUpdate.set("billFileId",billFileId)

'Define the SQL UPDATE query
sqlUpdate = "UPDATE tbl_BillFiles SET dateRejected = :dateRejected WHERE billFileId = :billFileId"

flag = cn.Execute(sqlUpdate,argsUpdate)

if .not. flag then
ui_msg_box("Error",cn.CallResult.text)
else

end if

cn.close(cn)

end function