executeSQL force commit record refresh issue

[happymac]

strange issue ... I am running an executeSQL to get a value and store that into a variable, then when the user goes to another record and runs the same executeSQL to get the new value it does not updating correctly. Have tried ... adding a goto field, refresh window, commit record, goto another layout and come back and nothing seems to work. more detail on the issue below ...

 

we have a INVOICE NUMBER field that gets a value that is incrementally higher than the last entry. It is technically not a "serial number" but rather a text field (since the value has text and numbers in it) that users' can alter, so don't confuse it with a primary key. Basically, when the user clicks into the INVOICE NUMBER field, we scripttrigger (on field enter) a popover to show the next value that the user should use, the value that we show the user is generated by an executeSQL (below) that is put into a merge variable for the user to see ... they can then type that value into the field if they want (or ignore the suggestion if they want also.

 

The goal is for the the executeSQL command to finds the highest numerical value of the records in the table, then display that value + 1. So if the last used invoice number (we call the field 'code') is SQ0014, then it would show the user SQ0015.

 

it works one time, but if you then go to the next record and click into the field again, the scripttigger still returns the same value, as if the last entered value never got stored into the database. so, you open the database and click into the field and the merge variable shows SQ0015 ... then click into the field manually set the value, then goto the next record and click into the field again it still shows SQ0015 even though it has been used (it should show SQ0016, since that is the next available code).

 

by the way, at the beginning of the scripttrigger and at the end, I clear the variable by using "set variable = """, so it should be resetting??

the setvariable command is as follows ...

Let(

[

assetcode =

 

GetValue (

ExecuteSQL (

"select code from ASSET where upper(code) like 'SQ%'"

; ""; "" )

;

ValueCount (

ExecuteSQL (

"select code from ASSET where upper(code) like 'SQ%'"

; ""; "" )

)

)

+ 1 ;

codenumber = Filter ( assetcode ; "0123456789" ) ;

] ;

"SQ" & codenumber

)

 

what can i do to force the executeSQL to recalculate and not use the previously stored value? Thank you in advance!!