Update Documents from SQL Report/Query

Carla van Zyl

• November 07, 2023 14:11

Good day, 

I am trying to enable bulk update of Sales Orders'  UDF fields based off a SQL Report/or Query. 

I have tried: 

1. SQL Report that have editable fields. The problem is when I select target I could only use the system syntax and not SQL so I can't seem to update the Sales Order based on the Doc Number in SQL Report. Is this at all possible? 

2. Document Data Manager: This seemed too be a good option, but I can't get my dropdown list from My UDF's to reflect in the value. Is there a way that I can add SQL to the Value field so the user can Select. 

3. I haven't looked at formatted searches yet. But thinking might be a potential. 

Is there any advise on how I can solve the first two problems I am facing or a recommendation on what the best route is to update UDF Fields from a Query

 

Vote

0

• 

  Support

  • November 23, 2023 21:09

  You could try adding an interactive check box to the query, then place a button that runs a line loop UF to update the UDF for the selected lines?
• 

  Geoff Booth

  • November 23, 2023 22:07

  Do you have some sample data to help visualize what you are trying to do?

  Are you trying to update multiple records with the same value?

  Is the value you are trying to update obtained with an SQL query.

   

  Adding the checkbox as Support suggested is definitely workable.
• 

  Carla van Zyl

  • November 27, 2023 10:20

  Currently we have a button that is opening up opel sales orders that our logistics team need to update/allocate trucks. 

  To do that they need to go into each sales order and update the udf. I want to make it that they can input the pallet count and Truck onteh SQL report and then the input data needs to update the UDF's on the relevant Sales order

   

   

  
• 

  Geoff Booth

  • November 27, 2023 14:34

  Two options (similar):

  • SQL Report with the Boyum Line Loop (As suggested by Support)
  • SQL Report with a "Do While loop" as explained here: 

     <https://www.battleshipcobra.com/youtube-download/while_loop_example.txt>

  • I have used both. Lately I have been using more of the Do While loop as it is all contained within one UF.

  Good luck.
• 

  Carla van Zyl

  • November 28, 2023 05:26

  Thanks for advice. Will give it a go!
• 

  Carla van Zyl

  • January 08, 2024 11:53

  Hi Geoff Booth 
  
  Just an update, the while loop worked. Thanks for the help. 
  
  Just one more question, is there a macro function where I can block pop up messages to save or any other prompts to be blocked while the macro runs? 
• 

  Geoff Booth

  • January 08, 2024 14:34

   

  Glad you are making progress!

  Yes - as long as you know  which ones are popping up:. See Boyum help as they must be placed in the macro just before they are needed

  AutoPressNextPopup(); -- most common

  AutoPressNextPopup($[$<item>.0.0]); -- special cases

  AutoPressNextPopup($[$<item>.0.0]|timeout); -- special cases

   

Please sign in to leave a comment.