output sql query to text field

Answered

Alain Seys

• January 21, 2020 10:25

Hy is it possible to output a sql query result to a text field ? 

 

Vote

0

• Official comment

  

  Yaremi Miranda Lopez

  • February 12, 2020 17:55

  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
• 

  Nadav Caridi

  • January 23, 2020 08:22

  Set isn't working for you ? 

   
• 

  Kate Olszewska

  • January 24, 2021 15:09

  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 

   
• 

  Nadav Caridi

  • January 24, 2021 15:27

  Hi Kate,

  I've only ever tried populating a single value.

  Can you elaborate what you're trying to do ? 

   
• 

  Kate Olszewska

  • January 24, 2021 15:33

  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

   
• 

  Nadav Caridi

  • January 24, 2021 15:39

  Hi, just concat the columns in your sql
• 

  Kate Olszewska

  • January 24, 2021 16:05

  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.

   

   

   

   
• 

  Nadav Caridi

  • January 24, 2021 17:15

  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.