OpenEdge progress database insert row - Beginner

Question

i need to insert new rows into my database.

    OUTPUT TO c:\temp\SMLeiste_bearbeiten.csv.
DEFINE VARIABLE size AS CHARACTER NO-UNDO FORMAT "x(3)"
  LABEL "Size".
for each S_Artikel
where S_Artikel.Selektion matches "KSE*"
or S_Artikel.Selektion matches "ZSE*"
or S_Artikel.Selektion matches "SSE*",
EACH BS_Zuord
     where BS_Zuord.SMLeiste = "SE"
     AND BS_Zuord.Owning_Obj = S_Artikel.S_Artikel_Obj
  EXCLUSIVE-LOCK.

 ASSIGN BS_Zuord.Merkmal = "Größe".

 PUT UNFORMATTED
   'First Loop - set row with Merkmal "Größe"' + '|' + STRING(BS_Zuord.Owning_Obj) + '|' + String(S_Artikel.S_Artikel_Obj)
SKIP.
END.

So the problem now is, that i don't know how to add the row with the Size without going through every item from "BS_Zuord"

one S_Artikel object has multiple BS_Zuord objects

I just want to query which S_Artikel matches my criteria and then add a BS_Zuord with equal Owning_Obj = S_Artikel_Obj

I think i might have to join the tables or something like that, but i have no idea how to do that in progress

Thanks in advance!

edit:

Do i have to replace

EACH BS_Zuord
     where BS_Zuord.SMLeiste = "SE"
     AND BS_Zuord.Owning_Obj = S_Artikel.S_Artikel_Obj
  EXCLUSIVE-LOCK.

 ASSIGN BS_Zuord.Property = "Size".

with

CREATE BS_Zuord.
BS_Zuord.OwningObj = S_Artikel_Obj.
ASSIGN BS_Zuord.Property = "Size".

---

**

Newest version of complete code

**

    OUTPUT TO c:\temp\SMLeiste_bearbeiten.csv.
DEFINE VARIABLE groesse AS CHARACTER NO-UNDO FORMAT "x(7)"
  LABEL "Groesse".
for each S_Artikel
where S_Artikel.Selektion matches "KSE*"
or S_Artikel.Selektion matches "ZSE*"
or S_Artikel.Selektion matches "SSE*",

EACH BS_Zuord
     where BS_Zuord.SMLeiste = "SE"
     AND BS_Zuord.Owning_Obj = S_Artikel.S_Artikel_Obj
  EXCLUSIVE-LOCK.

 ASSIGN BS_Zuord.Merkmal = "Size". 

 PUT UNFORMATTED
   'Erste Schleife - Größe anlegen' + '|' + STRING(BS_Zuord.Owning_Obj) + '|' + String(S_Artikel.S_Artikel_Obj)
SKIP.
END.


for each S_Artikel
where S_Artikel.Selektion matches "KSE*"
or S_Artikel.Selektion matches "ZSE*"
or S_Artikel.Selektion matches "SSE*",
EACH BS_Zuord
     where BS_Zuord.SMLeiste = "SE"
     AND BS_Zuord.Merkmal = "Größe"
     AND BS_Zuord.Owning_Obj = S_Artikel_Obj 
  EXCLUSIVE-LOCK.
    IF SUBSTRING(STRING(S_Artikel.Selektion),8,1) = "/" THEN groesse = SUBSTRING(STRING(S_Artikel.Selektion),5,7).
        ELSE groesse = SUBSTRING(STRING(S_Artikel.Selektion),5,3).

    BS_Zuord.Auspr = groesse.

        PUT UNFORMATTED 
        'Zweite Schleife - Größe ausfüllen' + '|' + string(S_Artikel.Artikel) + '|' + string(S_Artikel.Selektion) + '|' + STRING(groesse) + '|' + STRING(BS_Zuord.SMLeiste) + '|' + STRING (BS_Zuord.Merkmal) + '|' + STRING(BS_Zuord.Auspr)
        SKIP.    
END.

If i replace the part where it says

,
EACH BS_Zuord
     where BS_Zuord.SMLeiste = "SE"
     AND BS_Zuord.Merkmal = "Größe"
     AND BS_Zuord.Owning_Obj = S_Artikel_Obj 
  EXCLUSIVE-LOCK.
    IF SUBSTRING(STRING(S_Artikel.Selektion),8,1) = "/" THEN groesse = SUBSTRING(STRING(S_Artikel.Selektion),5,7).
        ELSE groesse = SUBSTRING(STRING(S_Artikel.Selektion),5,3).

    BS_Zuord.Auspr = groesse.

with yours

/* find the appropriate BS_Zuord if it exists */

exclusive-lock.
  find BS_Zuord where BS_Zuord.SMLeiste = "SE" and BS_Zuord.Owning_Obj =      S_Artikel.S_Artikel_Obj no-error.

  /* if it does not already exist create it and initialize the key */
  if not available BS_Zuord then   
    do:
      create BS_Zuord.
      assign
        BS_Zuord.SMLeiste = "SE"
        BS_Zuord.Owning_Obj = S_Artikel.S_Artikel_Obj
      .
    end.
  /* set the property field */
  BS_Zuord.Property = "Size".

and comment the second for each out

this message will appear: It says "for the Property (in my case Merkmal) "Größe" no "Ausprägung" (this is the actual value of the property "Merkmal". The Value '' is not allowed. https://i.stack.imgur.com/FDL4S.png

So 1 S_Artikel has multiple BS_Zuord with different "Merkmal". Every "Merkmal" has multiple values.

If i run the working code, it Runs for each S_Artikel x-Merkmal times and says if, for example, a S_Artikel has 6 "Merkmal" it creates the correct "Merkmal" with "Größe" as value and then it trys 5 times more to create it but it says it is existent. Then the second for each auto creates the values that will be filled in in BS_Zuord.Auspr.

I hope you understood what i try to tell you. English is not my native language :(

Answer 1

I think this is what you are trying to do:

/* loop through S_Artikel records matching criteria */

for each S_Artikel no-lock where
   where S_Artikel.Selektion matches "KSE*"
      or S_Artikel.Selektion matches "ZSE*"
      or S_Artikel.Selektion matches "SSE*":

  /* find the appropriate BS_Zuord if it exists */

  find BS_Zuord exclusive-lock where BS_Zuord.SMLeiste = "SE" and BS_Zuord.Owning_Obj = S_Artikel.S_Artikel_Obj no-error.

  /* if it does not already exist create it and initialize the key */

  if not available BS_Zuord then   
    do:
      create BS_Zuord.
      assign
        BS_Zuord.SMLeiste = "SE"
        BS_Zuord.Owning_Obj = S_Artikel.S_Artikel_Obj
      .
    end.

  /* set the property field */

  BS_Zuord.Property = "Size".

end.  /* end of loop */

(This assumes that BS_Zuord.SMLeiste and BS_Zuord.Owning_Obj form a unique key.)

Answer 2

Exactly. Remove the

EACH BS_Zuord
     where BS_Zuord.SMLeiste = "SE"
       AND BS_Zuord.Owning_Obj = S_Artikel.S_Artikel_Obj
     EXCLUSIVE-LOCK.
ASSIGN BS_Zuord.Property = "Size". 

as it will change the value of ALL BS_Zuord records that match the loop criteria. Plus, the second EACH will cycle the records you already have. Since you want to create a new, the CREATE and ASSIGN you posted last should do the trick. If the primary key and mandatory fields in the table are supplied accordingly, but assuming you just need the pk from your code, that code you supplied seems to do what you want, as per your explanation.