How to crud 3 levels

This example explains how to create a persistent entity based on a header, lines and sub-lines, and how to manage the CRUD related operations.
Some data types, which are references to other Sage X3 tables are used.
The naming rules are used for vertical elements (names starting with 'Y').
A customized development made for a unique customer should be coded with 'Z'.

Description of requirements

The following requirements need to be managed:

• A specific sales order entity identified by a sales order number and including a customer, sales rep, date, and total.
• Lines including an item reference and a price. The sequence in which the line appears in the sales order is important. It means that the user can insert lines between two existing lines.
• Every line has a sub-detail including a unit, a quantity, and a unit price. The sequence of the sub-lines for a giving line is irrelevant.

The unit price is entered at the sub-detail level, cumulated on the line, and finally at the header level. In the user interface, the user displays the cumulated price when a modification has been sent to the server. As it is done, the line price must not be modified; it is recomputed by cumulating the sub-lines.

Database structure

The database structure used for the persistence of the entity is as follows:

Every table also has the usual technical columns (CREDATTIM, UPDDATTIM, CREUSR, UPDUSR, AUUID).

The indexes needed on the tables are as follows:

The local menu 6520 used has two values: Entered and Validated.

Description of the created classes

Three classes are needed:

• 'YORDERSUBLIN' is a basic class including all the columns of the 'YORDSUBLIN' table. A specific script called 'YORDSUBLIN_CSPE' contains the code that handles the event linked to the sub-lines.
• 'YORDERLINE' is a basic class including all columns of the 'YORDERLINE' table plus a property called 'YOL'. This property is a member of a collection called 'YOL' and is associated with the 'YORDERSUBLIN' class. A specific script called 'YORDERLINE_CSPE' contains the code that handles the event linked to the sub-lines. A method called 'YLINEPRICE' computes the price of a line by cumulating the quantity multiplied by unit price on all the sub-lines.
• 'YORDER' is a persistent class including all the columns of the 'YORDER' table plus a property called 'YOD'. This property is a member of a collection called 'YOD' and is associated with the 'YORDERLINE' class. A specific script called 'YORDER_CSPE' contains the code that handles the event linked to the sub-lines.

The 'YORDER' persistent class also has the following characteristics:

• The main index used is 'YOH0'.
• All the CRUD methods are supported by the class.
• A method called 'YTOTAMOUNT' has been added in the method tab.

The Mapping tab includes the following join description grid:

• Reading, Creation, Modification, and Deletion check boxes are selected for both detail lines in the Mapping tab. Because the child classes are basic, the CRUD code for these lines will be generated at 'YORDER' level.

Description of the 'YORDER' representation

This is the representation used for the CRUD management of the 'YORDER' class. It has the following characteristics:

• Associated class: 'YORDER' with the 'YOH' instance code.
• Behaviors available: Creation, Modification, Deletion, Reporting Excel, Reporting Word, and Mail merge Word.
• No collection is declared, the collections used are inherited from the class.
• The associated script is called 'YORDER_RSPE'.
• No dedicated property is defined, only the properties of the class will be necessary.
• No dedicated method is defined, only the methods of the class will be necessary.

The sections and blocks are organized as follows:

The following properties are available in the representation:

The script associated with the representation is called 'YORDER_RSTD'.

The different scripts used

All scripts are starting with '$METHODS' or '$PROPERTIES' label. To know the exact context, the following variables need to be checked:

• 'ACTION' gives the event being caught. For example, "ADDLINE_AFTER" is executed when a line is added in a collection.
• 'CURPRO' gives the name of the property when the event is called by '$PROPERTIES' and hence associated to a rule (CONTROL, INIT, PROPAGATE).
• 'CURPTH' gives the level of the instance compared to the level where the code is attached. 'CURPTH' is used only for methods (always empty for rules). If 'CURPTH' is empty for a method, the code associated with a class is called for an instance of this class. If 'CURPTH' is not empty, it describes the path starting from this instance. For example:
  • If the code related to the sub-line is called in the script associated with the sub-line, then 'CURPTH' is empty.
  • If the code related to the sub-line is called in the script associated with the line, then 'CURPTH' equals "YOL".
  • If the code related to the sub-line is called in the script associated with the header, then 'CURPTH' equals "YOD.YOL".
  • If the code related to the sub-line is called in the script associated with the representation, then 'CURPTH' equals "YOH.YOD.YOL".

The scripts used on every class or representation have the following pattern:

$METHODS
 Case CURPTH
   When "" : # Methods called for the instance of the class associated with the script
     Case ACTION
         ... here are the methods
     Endcase
   When ... : # Methods called for child instances of the class associated with the script 
     Case ACTION
         ... here are the methods
     Endcase
Endcase
Return

$PROPERTIES
  Case CURPRO
    When ... : # The property is defined here
      Case ACTION
         # Here ACTION can have the values CONTROL, INIT, PROPAGATE...
      Endcase
  Endcase
Return

At sub-line level

This script associated with 'YORDSUBLIN' is used at the sub-line level to manage the events, control, and default values only. By default, this script defined as specific is called 'YORDERSUBLIN_CSPE'.

As nothing is required, there is no action to be done in this script.

If the properties in the sub-lines have to be filled, no code is necessary, the Mandatory check box will be activated in the class definition.

At line level

This script associated with 'YORDLINE' is called 'YORDLINE_CSPE'. The only code written is used when a line is modified to propagate to the sub-lines some of the modifications that have been done.

Two things happen:

$METHODS
 Case CURPTH
   When "YOL"
     Case ACTION
       When "ADDLINE_AFTER" : # A line has been added
         this.YORDNUM = this.APARENT.YORDNUM : # "this" is the sub-line, this.APARENT is the line
         this.YORDL   = this.APARENT.YORDL
         # To assign YORDSL to a new value, compute the maximum of the YORDSL property
         #  for all the YOL child instance of the line, and add 1 to the result
         this.YORDSL  = 1+max(this.APARENT.YOL(1..maxtab(this.APARENT.YOL)).YORDSL)
     Endcase
 Endcase
Return

$PROPERTIES
Case CURPTH
  When "" : # We are here on the properties of the instance associated with the current class (line)
    Case CURPRO
      When "YORDNUM" : # Order number at line level
        Case ACTION
          When "PROPAGATE" : # The value has been modified
               Local Integer I
               # Loop on all existing sub-lines of the line : "this" is the line modified
               For I=1 To maxtab(this.YOL)
                 If this.YOL(I)<>null : this.YOL(I).YORDNUM=this.YORDNUM : Endif
               Next I
        Endcase
      When "YORDL" : # Line number at line level
        Case ACTION
          When "PROPAGATE" : # The value has been modified
               Local Integer I
               # Loop on all existing sub-lines of the line : "this" is the line modified
               For I=1 To maxtab(this.YOL)
                 If this.YOL(I)<>null : this.YOL(I).YORDL=this.YORDL : Endif
               Next I
        Endcase
    Endcase
Endcase
Return

At header level

This script associated with YORDER is called YORDER_CSPE. There is more code to write because several cases need to be managed:

• Implement the method that computes the total price (global method of the YORDER class).
• Call this method just before the persistence is done in the database. This might happen in two cases: when the order is created or when it is modified.
• When a line is added, the line index properties are to be assigned. This is a bit complex because a sorting order on the lines need to be managed.
• When the order number is modified, the value to the lines is to be propagated.

On properties, the propagation code is the following:

$PROPERTIES
  Case CURPRO
    When "YORDNUM"
      Case ACTION
        When "PROPAGATE"
          # Loop on all the lines present to assign YORDNUM
          # The propagation rule present in YORDLINE_CSPE script will update the sub-line values
          Local Integer I
          For I=1 To maxtab(this.YOD)
            If this.YOD(I)<>null : this.YOD(I).YORDNUM=this.YORDNUM : Endif
          Next I
      Endcase
  Endcase
Return

$METHODS
 Case CURPTH
   When "" : # Method on the order class
     Case ACTION
       When "YTOTAMOUNT" : # Total amount computation : ARET_VALUE returns the result, I is a loop variable
           Local Integer I
             ARET_VALUE=0
             # Loop done on all the YOD values if the pointer is not null and if the line is not marked as deleted
             For I=1 To maxtab(this.YOD)
               If this.YOD(I)<>null
                 If find (this.YOD(I).ASTALIN,[V]CST_ADEL, [V]CST_ANEWDEL)=0
                   this.YOD(I).YPRICE=fmet this.YOD(I).LINEPRICE
                   ARET_VALUE+=this.YOD(I).YPRICE
                 Endif
               Endif
             Next I

       # When an insertion or an update method is called, call the previous computation method before database persistence
       When "AINSERT_CONTROL_BEFORE", "AUPDATE_CONTROL_BEFORE"
             this.YTOTAL= fmet this.YTOTAMOUNT

     Endcase

   When "YOD" : # method on the line
      Case ACTION
       When "LINEPRICE" : # Total line computation : ARET_VALUE returns the result, I is a loop variable
           Local Integer I
             ARET_VALUE=0
             # Loop done on all the YOL values if the pointer is not null and if the line is not marked as deleted
             For I=1 To maxtab(this.YOL)
               If this.YOL(I)<>null
                 If find (this.YOL(I).ASTALIN,[V]CST_ADEL, [V]CST_ANEWDEL)=0
                   ARET_VALUE+=this.YOL(I).YUNITPRICE*this.YOL(I).YQTY
                 Endif
               Endif
             Next I

         When "ADDLINE_AFTER" : # A line is added
            Gosub ADD_LINE_YOD : # We need to manage the numbering of the line added

      Endcase
Endcase
Return

• The 'YORDL' property is managed as the 'YORDSL' on the sub-line : an assignment is done with a number greater than the other values of the collection.
• The 'YSORT' property is managed by taking into consideration the display order of the lines. To avoid having to rewrite all the lines when a line is inserted, the 'YSORT' property is filled with multiples of 100. When a line is inserted, a dichotomy rule is used to assign it.
• The 'ASORT' property that is automatically added in the collections and managed by the supervisor handles the index in the table for 1 to the number of lines.

For example, we start with an empty order. Lines A, B, and C are added first. Then line D is inserted between A and B, line E is inserted between lines B and C, line F is inserted between A and D, line G is inserted before A, and line H is added at the end. The final result will be the following, as it appears on the page when all updates have been done:

As you can see, 'YORDSL' gives the input order, 'AORDER' handles the index for displaying the lines, and 'YSORT' allows you to sort the lines without having to reassign other number lines when a limited number of insertions have been done.

$ADD_LINE_YOD

# Increment value for dichotomy algorithm
  Local Integer MY_STEP
  MY_STEP = 100

# Manage key identifiers
  this.YORDNUM  = this.APARENT.YORDNUM
  this.YORDL    = 1 + max(this.APARENT.YOD(1..maxtab(this.APARENT.YOD)).YORDL)

# Manage the SORT property
  Local Integer I, BEF_LINE, AFT_LINE, OFFSET_LINE : # Positions in collection (they have a 0 value here)

# Where are the previous and next lines ? Loop on YOD values
# "this" is here the current line, we need to loop on the YOD in the header instance (APARENT)
  For I = 1 To maxtab(this.APARENT.YOD)
    If this.APARENT.YOD(I) <> null
      If this.APARENT.YOD(I).AORDER = this.AORDER - 1
        BEF_LINE=I
      Elsif this.APARENT.YOD(I).AORDER = this.AORDER + 1
        AFT_LINE=I
      Endif
    Endif
  Next I

Local Integer BEF_LINE_SORT, AFT_LINE_SORT: # Positions in the sort order

# If the new line is not added at the end of the collection
  If AFT_LINE <> 0
    # Assign the SORT properties (BEF_LINE_SORT is 0 if insertion at the beginning of the collection)
    AFT_LINE_SORT = this.APARENT.YOD(AFT_LINE).YSORT
    If BEF_LINE <> 0 : BEF_LINE_SORT = this.APARENT.YOD(BEF_LINE).YSORT : Endif
    # If there is enough space then we insert using a dichotomy method
    If BEF_LINE_SORT <= AFT_LINE_SORT - 2
      this.YSORT = int((BEF_LINE_SORT + AFT_LINE_SORT)/2)
    Else
      # If not enough space, we reassign the SORT property of all lines from the new inserted line
      For I=1 To maxtab(this.APARENT.YOD)
        If this.APARENT.YOD(I) <> null
          OFFSET_LINE=this.APARENT.YOD(I).AORDER-this.AORDER
          If OFFSET_LINE>=0
            # Assign the SORT property          
            this.APARENT.YOD(I).YSORT = BEF_LINE_SORT + MY_STEP + (OFFSET_LINE * MY_STEP)
          Endif
        Endif
      Next I
    Endif

  # If the new inserted line is at the last position in the array
  Else #If AFT_LINE = 0
    # If a previous line exist, we know the last value used, otherwise it is 0
    If BEF_LINE <> 0 : BEF_LINE_SORT = this.APARENT.YOD(BEF_LINE).YSORT : Endif
    # Assign the SORT property
    this.SORT = BEF_LINE_SORT + MY_STEP
  Endif

Return

At representation level

At representation level, the only code that has been added is the one that triggers a computation of the total every time a unit price is modified or a line is added. This code does not have to be inserted in the class code; otherwise, when running in service mode, the computation is repeated several times.

There is no guarantee that the total price will be displayed every time a modification is made on the price due to a bad web connection, especially with mobile devices, or data sent by group. Every time an exchange is made between the client and the server, the client returns the modifications made on prices since the last time, and the server sends back the computed total price accordingly.

The implemented code executes the following rules:

• When a modification is done on the lines instance (insertion and deletion), the price is recomputed.
• When a modification is done on the sub-lines instance (insertion and deletion), the price is recomputed.
• When an update is done on the price or quantity in a sub-line instance, the price is recomputed.

$METHODS
  Case CURPTH
    When "YOH.YOD" : # Here we are at the line level, "this" is the line, "this.APARENT" is the header
      Case ACTION
        When "PROPAGATE"
          this.APARENT.YTOTAL= fmet this.APARENT.YTOTAMOUNT
      Endcase
    When "YOH.YOD.YOL" : # Here we are at the sub-line level, "this" is the sub-line, "this.APARENT.APARENT" is the header
      Case ACTION
        When "PROPAGATE"
          this.APARENT.APARENT.YTOTAL= fmet this.APARENT.APARENT.YTOTAMOUNT
      Endcase
  Endcase
Return

$PROPERTIES
  Case CURPRO
    When "YOH.YOD.YPRICE"
      Case ACTION
        When "PROPAGATE"
          this.APARENT.YTOTAL= fmet this.APARENT.YTOTAMOUNT
      Endcase
    When "YOH.YOD.YOL.YQTY", "YOH.YOD.YOL.YUNITPRICE"
      Case ACTION
        When "PROPAGATE"
          this.APARENT.APARENT.YTOTAL= fmet this.APARENT.APARENT.YTOTAMOUNT
      Endcase
  Endcase
Return

Validation and deletion management

In a second step of this development, a validation operation has been added. Being an operation, it is considered as stateless. When the event is caught, 'this.YORDNUM' (the key) is filled but the instance is not. A complex operation needs to perform an 'AREAD' method first. In this case, the only operation to perform is to update the 'YSTATUS' value to 2 if it is not already done. If it is already the case, a warning is returned.

An important point is that for the moment, using error handling classes and methods is useless in an operation context because the instance in which the operation runs is volatile and will disappear with the corresponding 'AERROR' child class. The only solution is to return a single status, which is done through the 'ARET_VALUE' parameter.

The code of the operation has been defined in the 'YORDER_CSPE' script, just after the '$METHODS' label. If 'CURPTH' is empty, the code should be added after the lines managing the "AINSERT_CONTROL_BEFORE" and "AUPDATE_CONTROL_BEFORE" methods.

The code is as follows:

       When "YVALIDATE"
            Local File YORDER

            # Execute the update transaction
            Trbegin [YOH]
            Update [YOH] Where YORDNUM=[L]YORDNUM and YSTATUS=1 With YSTATUS=2
            Commit

            # If no record updated, it can be because the order does not exist or because already validated
            If adxuprec=0
              Read [YOH]YOH0=[L]YORDNUM
              # Send a warning status if order already validated, and error status if order doesn't exist
              If fstat=[V]CST_AOK
                ARET_VALUE=[V]CST_AWARNING
              Else
                ARET_VALUE=[V]CST_AERROR
              Endif
            Endif
     Endcase

The code is the following:

       # The order can only be deleted if YSTATUS is 1
       When "ADELETE_BEFORE"
            If this.YSTATUS<>1
              VAL_OK=fmet this.ASETERROR('','Order validated ',[V]CST_AERROR)
            Endif