Concatenating Different Records (Progress)

a.omz 0
Viewed 72

Can you please help me:

I have a database and I need to "concatenate records" based on fields in a table.

Table: Order Field: Order-Number Field: Previous-Order-Number Field: Create-Date

I need to be able to find the first "order based". Then be able to find all the other orders that are related and put them into a variable delimited by ",".

For example:

Record 1:

  • Order-Number is: 123456
  • Previous-Order-Number is: 999999

Record 2:

  • Order-Number is 999999
  • Previous-Order-Number is 111111

Record 3:

  • Order-Number is: 111111
  • Previous-Order-Number is: 777777

and so on and so on.

I want to put it in a variable, so it will have display/output like this: (Order-Number + "|" + Create-date, N) i.e. "123456|01/01/22, 999999|03/03/22, 111111|04/04/22"

Please let me know if you need more information

Thank you

progress-4gl openedge
2 Answers

you can do it with a RecursiveFunction

    // RecursiveFunction
    FUNCTION getOrderNumRecursive RETURN CHARACTER
       (INPUT iOrderNum AS INTEGER):

        FIND FIRST Order No-LOCK
           WHERE Order.Order-Number = iOrderNum
           No-ERROR.
        IF AVAILABLE(Order) THEN
           // in the Return you call the function again with the PreviousOrderNumber
           RETURN STRING(Order.Order-Number) + "|" + STRING(Order.Create-date) + 
                  "," + getOrderNumRecursive(Order.Previous-Order-Number).

        RETURN "".
    END FUNCTION.

    DEFINE VARIABLE cList AS CHARACTER No-UNDO.
    
    //Call the function with the first OrderNumber you want
    cList = getOrderNumRecursive(123456).
    
    MESSAGE cList
    VIEW-AS ALERT-BOX.
FloW0

You want to get this from all the elements from the table? You can run this code to generate it into a CHAR variable...

If you want to add more fields, just add into both of the i_rec into the FOR EACH loop.

DEF VAR i_rec AS CHAR NO-UNDO.

FOR EACH Order NO-LOCK:
    IF i_rec <> "" THEN DO:
        i_rec = i_rec + "," + Order.Order-Number + "|" + Order.Create-date.
    END. ELSE DO:
        i_rec = Order.Order-Number + "|" + Order.Create-date.
    END.
END.
Raphael Frei0
Related