executeSQL refresh, update

[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 begining 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!!

[GisMo]

Maybe break up all of those SQL calls into separate Set Variable Statements to debug it....

 

I'm shooting from the hip, but  try Something Like This
 

let

[

sql = "SELECT Code FROM Asset WHERE UPPER(code) like ? ORDER BY Code DESC"

myCode = getvalue(executesql(sql;"";"";"SQ%");1);

codeNumber = Filter(myCode;"0123456789") + 1

]

"SQ" & codeNumber'

)

A few things come to mind when doing this. If you're using numbers, create a separate field for Invoice number and then create a separate field InvoiceNumberText = "SQ" & Zero_Padding & InvoiceNumber

If you did that, you could simply query invoice number and sort descending to get the highest number
 

let(

[

sql = "SELECT InvoiceNumber FROM ASSET ORDER BY DESC"

];

getvalue(executesql(sql;"";"");1) + 1

)//end let

 

 

 

Edited November 13, 2015 by GisMo
"ORDER BY"

[Wim Decorte]

First off, it does not make sense to do two identical ExecuteSQL() calls, especially not an "expensive" one such as a LIKE.  So GisMo is on track: separate those out and use the Let() function for what is meant for: set va

As to why it is not working when going from record to record: it should work, but perhaps you have triggers enabled that get in the way?