Jump to content

executeSQL refresh, update


This topic is 3033 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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 by GisMo
"ORDER BY"
Link to comment
Share on other sites

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?

 

 

Link to comment
Share on other sites

This topic is 3033 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.