I am trying to auto set activity notes field $[OCLG.Notes] with the results from sql.
Sql returns more than 1 rows and has two columns. When I run macro to set this field with results, only one result from first row and first column is populated. My command looks as following:
Set($[$25.0.0]|SQL:SELECT T0."DocNum",T0."DocDate" FROM ODLN T0 WHERE T0."U_ConComInv" ='Y' and T0."CANCELED" ='N' and ifnull(T0."U_ConComInvComplete",' ') <>'Y' and T0."CardCode"=$[$9.0.0])
Unless is there other way for text field to auto populate with multiple results from SQL query will be great?
Just tried it now but it only take results from first row but I have another few rows from the same sql which need to be populated to the same field. Or they can go one under another or next.
Comments
8 comments
Hello Alain,
As Nadav mentioned, please use the Set command in a Macro.
The following link to the online help includes some examples: https://help.boyum-it.com/B1UP/index.html?command-set.html
Set isn't working for you ?
Hello,
What is the outcome of the original query?
I used command Set($[$item.col.type]|SQL:.....); but it does not populate all records from the query into the text box.
Can you please advise?
Thank you
Regards
Kate
Hi Kate,
I've only ever tried populating a single value.
Can you elaborate what you're trying to do ?
Hi Nadav,
I am trying to auto set activity notes field $[OCLG.Notes] with the results from sql.
Sql returns more than 1 rows and has two columns. When I run macro to set this field with results, only one result from first row and first column is populated. My command looks as following:
Set($[$25.0.0]|SQL:SELECT T0."DocNum",T0."DocDate" FROM ODLN T0 WHERE T0."U_ConComInv" ='Y' and T0."CANCELED" ='N' and ifnull(T0."U_ConComInvComplete",' ') <>'Y' and T0."CardCode"=$[$9.0.0])
Unless is there other way for text field to auto populate with multiple results from SQL query will be great?
Kind Regards
Kate
Hi, just concat the columns in your sql
Hi,
Thank you.
Just tried it now but it only take results from first row but I have another few rows from the same sql which need to be populated to the same field. Or they can go one under another or next.
DO you have any other suggestion that I can try?
Please advise.
Try using some combination of stuff maybe.
You can look here for some examples or just try ti Google it
https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/
Please sign in to leave a comment.