B1UP Line Loop - Use SQL to loop through what user entered

Answered

Chris Clos

• July 27, 2016 16:41

Is there a way in the SQL statement to look through what the user entered?  In my case I need to make sure the data is unique (not duplicated) in the same order.

Vote

0

• Official comment

  

  Naama Aharoni

  • August 01, 2016 07:50

  Hi Chris,

  Are you referring to a having the line loop identify duplicate entries in the rows?

  I believe this is possible.

  If this is not what you mean, please provide more information on the scenario.

  Thanks, Naama

   

   

   

   
• 

  Chris Clos

  • August 01, 2016 12:55

  Yes, Duplicate entries on the rows, but on what the user has filled in.  I can easily check against the column using SQL, but I can't figure out a way to check against what has been filled in.
• 

  Naama Aharoni

  • August 09, 2016 06:50

  Hi Chris,

  I assume you can run through the lines with a line loop and then copy each line into a temp table.

  Then for the next rows in the original document, you will have to check against this temp table and if the row does not exist then it is unique. And you can write it to the temp.

  If you come across a row that is a duplicate, it will already exist in the temp.

  Another alternative, it to do a lineloop within a lineloop. So for each row, you run another line loop that compares against all the previous rows.

  Hope it helps,

  Naama
• 

  D Sommers

  • September 14, 2016 22:34

  You can use SQL code, but your in a validation, but you have to let the users add the data first (BeforeAction set to false).

  Here's code from a validation that is triggered by an "Data Add" event:

  select case when exists
  (
  SELECT        RDR1.ItemCode
  FROM            ORDR INNER JOIN
                           RDR1 ON ORDR.DocEntry = RDR1.DocEntry
  WHERE       docnum= $[$8.0.0] AND (ORDR.DocStatus = 'o')
  GROUP BY RDR1.ItemCode
  HAVING        (COUNT(ORDR.DocNum) > 1)
  )
  Then 'Doit' end

  The UF that gets run then has the message: There were duplicate lines in the document you just added.

   

   

Please sign in to leave a comment.