How to get contents of UDO prior to Add and run a query against data

Answered

Don Maes

• August 09, 2019 03:17

Here is the scenario.  I have a UDO being used to create issues to production.  The user scans in data from a bar code and populates a grid with item, batch, whs and quantity.  They scan in 15 rows of data and the same item - batch - whs could occur multiple times.  When they hit the Add button I create an issues for production document with all of the lines of data (Production order was in the UDO header).

Prior to clicking add I want to capture the grid data and show the user the on hand for each item - batch - whs and compare this to the sum of quantities for this same item - batch - whs.  That tells them which item(s) may not have sufficient on hand to be issued.  (I actually stop an error from occurring by stopping the transaction with sbo_sp_transactionnotification code).  

I use the line loop to capture the item - whs - batch and qty information (using Append).  I get a result using the Message UF function and $[LLRESULT.STRING].  It looks like this: [VENY88.4NI,01,0543262,120] [T3X3284,01,0512311,120]

Okay so since I captured this in the message i thought I could use SQL to parse this information and build a temporary table of the unique item, whs, batch, sum(qty) for each unique instance.  

The SQL script to create and then populate the temp table blows up when I try to write the results to a string variable:  select @result = $[LLRESULT.STRING].  (where @result is varchar(max)).

It is trying to set @result = [VENY88.4NI,01,0543262,120] [T3X3284,01,0512311,120]

If you wrote SQL for this it would be @result = '[VENY88.4NI,01,0543262,120] [T3X3284,01,0512311,120]'

The rest of the SQL report doesn't matter because I never get any further.

 

Any ideas on how to get a SQL report from data in a UDO grid prior to 'Add'?     I thought the $[LLRESULT.STRING] would be the answer because when I reviewed it with 'Message' it showed the data.  But missing the ' ' around the variable messes  me up.

 

I may be missing something elementary but I can't think of it.

Thanks

Vote

0

• Official comment

  

  Yaremi Miranda Lopez

  • February 18, 2020 17:51

  Hello Don,

  This is a very specific scenario, but what I have done with temporary tables is that I use a LineLoop to write/update the table according to my needs and then use that information, which could be a message, macro, etc.

  I´ve never tried using the result of the entire line loop since is just a single value/field.
• 

  Don Maes

  • September 27, 2024 22:33

  Anyone wants to know, Yaremi's solution worked very well.

  Don

Please sign in to leave a comment.