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:

Table	Description	Column	Data type	Explanation
YORDERH [YOH]	Specific sales order	YORDNUM	A 10 (character string)	Sales order number
		YBPC	BPC (customer code)	Order customer
		YREP	REP (sales rep code)	Order sales rep
		YORDDAT	Date	Sales order date
		YTOTAL	DCB 9.2(Decimal)	Total amount of the order
		YCUR	CUR (currency code)	Order currency
		YSTATUS	Local menu (6520)	Order status
YORDERLINE [YOD]	Sales order detail	YORDNUM	YOD (sales order reference)	Order number
		YORDL	C 4(short integer)	Line order number (for join)
		YSORT	L 8(long integer)	Line order number (order in which the lines appear on the document)
		YITM	ITM (item reference)	Ordered item
		YPRICE	DCB 9.2 (decimal)	Line price
YORDERSUBLIN [YOL]	Sales order line sub-detail	YORDNUM	YOD (line order reference)	Order number
		YORDL	C 4 (short integer)	Order Line number
		YORDSL	C 4 (short integer)	Order sub-line number
		YUNIT	UOM (unit of measure)	Unit for quantity
		YQTY	QTY (quantity)	Sub-line quantity
		YUNITPRICE	DCB 9.2 (Decimal)	Unit price for sub-line

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

The indexes needed on the tables are as follows:

Table	Description	Index	Definition	Description
YORDERH [YOH]	Specific sales order	YOH0	YORDNUM (no duplicates)	Main index used to access the sales order by its reference
		YOH1	YBPC+YORDNUM	Index per customer
		YOH2	YREP+YORDNUM	Index per sales rep
		YOH1	YORDDAT+YORDNUM	Index per date
YORDERLINE [YOD]	Sales order detail	YOD0	YORDNUM+YORDL (no duplicates)	Main index used to access the sales order line
YORDERLINE [YOD]	Sales order detail	YOH1	YORDNUM+YSORT	Index used to display lines in the right order
YORDERSUBLIN [YOL]	Sales order sub-line	YOD0	YORDNUM+YORDL+YORDSL (no duplicates)	Main index used to access the sales order sub-line

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:

Reference	Class	Reference table	Origin table	Main index	Sorting index	Type	Join expression
YOD	YORDERLINE	YORDERLINE	YORDER	YOD0	YOD1	1,n	[F:YOH]YORDNUM
YOD.YOL	YORDERSUBLIN	YORDERSUBLIN	YORDERLINE	YOL0		1,n	[F:YOD]YORDNUM;[F:YOD]YORDL

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:

Section definition			Blocks definition
Code	Description	Order	Code	Description	Section	Order
YHEAD	Header	10	YMAIN	Main info	YHEAD	10
YHEAD	Header	10	YSTAT	Status	YHEAD	20
YLINES	Lines	20	YLINES	Lines	YLINES	30
YFOOT	Footer	30	YFOOT	Footer	YFOOT	40

The following properties are available in the representation:

Alias	Property	Collection	Description	Facets
YORDNUM	YOH.YORDNUM		Order Number	All
YBPC	YOH.YBPC		Customer	Query, Detail, Edit, Lookup
YREP	YOH.YREP		Sales rep	Query, Detail, Edit
YORDDAT	YOH.YORDDAT		Order Date	Query, Detail, Edit
YCUR	YOH.YCUR		Currency	Detail, Edit
YSTATUS	YOH.YSTATUS		Status	Detail, Edit (Not inputted)
YITM	YOH.YOD.YITM	YOH.YOD	Item Ref	Detail, Edit
YPRICE	YOH.YOD.YPRICE	YOH.YOD	Line Price	Detail, Edit
YUNIT	YOH.YOD.YOL.YUNIT	YOH.YOD.YOL	Unit	Detail, Edit
YQTY	YOH.YOD.YOL.YQTY	YOH.YOD.YOL	Quantitu	Detail, Edit
YUNITPRICE	YOH.YOD.YOL.YUNITPRICE	YOH.YOD.YOL	Unit Price	Detail, Edit
YTOTAL	YOH.YTOTAL		Order total	Detail, Edit (not inputted)

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:

$METHODSCase CURPTHWhen "" : # Methods called for the instance of the class associated with the scriptCase ACTION... here are the methodsEndcaseWhen ... : # Methods called for child instances of the class associated with the script Case ACTION... here are the methodsEndcaseEndcaseReturn$PROPERTIESCase CURPROWhen ... : # The property is defined hereCase ACTION# Here ACTION can have the values CONTROL, INIT, PROPAGATE...EndcaseEndcaseReturn

In the following example code, we will skip these lines and mention where the code is written.

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:

Event	ACTION value	CURPTH	CURPRO	"this" value	Actions to be done
When a sub-line is added to a line	ADD_LINE_AFTER	""	"YOL"	YORDELINE instance	1. Propagate YORDNUM and YORDL from line to the sub-line
When a sub-line is added to a line	ADD_LINE_AFTER	""	"YOL"	YORDELINE instance	2. Compute YORDSL with a new value greater than the existing line values
When a key property (YORDNUM or YORDL) is modified in the line	"PROPAGATE"	""	"YORDNUM"	YORDELINE instance	1. Propagate YORDNUM on all the sub-lines
	"PROPAGATE"	""	"YORDL"	YORDELINE instance	2. Propagate YORDL on all the sub-lines

The first case gives the corresponding code (after the '$METHOD' label):

$METHODSCase CURPTHWhen "YOL"Case ACTIONWhen "ADDLINE_AFTER" : # A line has been addedthis.YORDNUM = this.APARENT.YORDNUM : # "this" is the sub-line, this.APARENT is the linethis.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 resultthis.YORDSL = 1+max(this.APARENT.YOL(1..maxtab(this.APARENT.YOL)).YORDSL)EndcaseEndcaseReturn

The second case gives the corresponding code (after the $PROPERTIES label):

$PROPERTIESCase CURPTHWhen "" : # We are here on the properties of the instance associated with the current class (line)Case CURPROWhen "YORDNUM" : # Order number at line levelCase ACTIONWhen "PROPAGATE" : # The value has been modifiedLocal Integer I# Loop on all existing sub-lines of the line : "this" is the line modifiedFor I=1 To maxtab(this.YOL)If this.YOL(I)<>null : this.YOL(I).YORDNUM=this.YORDNUM : EndifNext IEndcaseWhen "YORDL" : # Line number at line levelCase ACTIONWhen "PROPAGATE" : # The value has been modifiedLocal Integer I# Loop on all existing sub-lines of the line : "this" is the line modifiedFor I=1 To maxtab(this.YOL)If this.YOL(I)<>null : this.YOL(I).YORDL=this.YORDL : EndifNext IEndcaseEndcaseEndcaseReturn

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:

$PROPERTIESCase CURPROWhen "YORDNUM"Case ACTIONWhen "PROPAGATE"# Loop on all the lines present to assign YORDNUM# The propagation rule present in YORDLINE_CSPE script will update the sub-line valuesLocal Integer IFor I=1 To maxtab(this.YOD)If this.YOD(I)<>null : this.YOD(I).YORDNUM=this.YORDNUM : EndifNext IEndcaseEndcaseReturn

On methods, the code is the following:

$METHODSCase CURPTHWhen "" : # Method on the order classCase ACTIONWhen "YTOTAMOUNT" : # Total amount computation : ARET_VALUE returns the result, I is a loop variableLocal Integer IARET_VALUE=0# Loop done on all the YOD values if the pointer is not null and if the line is not marked as deletedFor I=1 To maxtab(this.YOD)If this.YOD(I)<>nullIf find (this.YOD(I).ASTALIN,[V]CST_ADEL, [V]CST_ANEWDEL)=0this.YOD(I).YPRICE=fmet this.YOD(I).LINEPRICEARET_VALUE+=this.YOD(I).YPRICEEndifEndifNext I# When an insertion or an update method is called, call the previous computation method before database persistenceWhen "AINSERT_CONTROL_BEFORE", "AUPDATE_CONTROL_BEFORE"this.YTOTAL= fmet this.YTOTAMOUNTEndcaseWhen "YOD" : # method on the lineCase ACTIONWhen "LINEPRICE" : # Total line computation : ARET_VALUE returns the result, I is a loop variableLocal Integer IARET_VALUE=0# Loop done on all the YOL values if the pointer is not null and if the line is not marked as deletedFor I=1 To maxtab(this.YOL)If this.YOL(I)<>nullIf find (this.YOL(I).ASTALIN,[V]CST_ADEL, [V]CST_ANEWDEL)=0ARET_VALUE+=this.YOL(I).YUNITPRICE*this.YOL(I).YQTYEndifEndifNext IWhen "ADDLINE_AFTER" : # A line is addedGosub ADD_LINE_YOD : # We need to manage the numbering of the line addedEndcaseEndcaseReturn

The last part of the code is now the 'ADD_LINE_YOD' sub-program. This code assigns two properties on the YOD collection:

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:

line inserted	AORDER property	YORDSL value	YSORT value
G	1	7	50
A	2	1	100
F	3	6	125
D	4	4	150
B	5	2	200
E	6	5	250
C	7	3	300
H	8	8	400

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.

The following algorithm manages this operation:

$ADD_LINE_YOD# Increment value for dichotomy algorithmLocal Integer MY_STEPMY_STEP = 100# Manage key identifiersthis.YORDNUM = this.APARENT.YORDNUMthis.YORDL = 1 + max(this.APARENT.YOD(1..maxtab(this.APARENT.YOD)).YORDL)# Manage the SORT propertyLocal 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) <> nullIf this.APARENT.YOD(I).AORDER = this.AORDER - 1BEF_LINE=IElsif this.APARENT.YOD(I).AORDER = this.AORDER + 1AFT_LINE=IEndifEndifNext ILocal Integer BEF_LINE_SORT, AFT_LINE_SORT: # Positions in the sort order# If the new line is not added at the end of the collectionIf 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).YSORTIf BEF_LINE <> 0 : BEF_LINE_SORT = this.APARENT.YOD(BEF_LINE).YSORT : Endif# If there is enough space then we insert using a dichotomy methodIf BEF_LINE_SORT <= AFT_LINE_SORT - 2this.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 lineFor I=1 To maxtab(this.APARENT.YOD)If this.APARENT.YOD(I) <> nullOFFSET_LINE=this.APARENT.YOD(I).AORDER-this.AORDERIf OFFSET_LINE>=0# Assign the SORT propertythis.APARENT.YOD(I).YSORT = BEF_LINE_SORT + MY_STEP + (OFFSET_LINE * MY_STEP)EndifEndifNext IEndif# If the new inserted line is at the last position in the arrayElse #If AFT_LINE = 0# If a previous line exist, we know the last value used, otherwise it is 0If BEF_LINE <> 0 : BEF_LINE_SORT = this.APARENT.YOD(BEF_LINE).YSORT : Endif# Assign the SORT propertythis.SORT = BEF_LINE_SORT + MY_STEPEndifReturn

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.

The computation of the price is done by calling the 'YTOAMOUNT' method on the order header instance, which provides the following code:

$METHODSCase CURPTHWhen "YOH.YOD" : # Here we are at the line level, "this" is the line, "this.APARENT" is the headerCase ACTIONWhen "PROPAGATE"this.APARENT.YTOTAL= fmet this.APARENT.YTOTAMOUNTEndcaseWhen "YOH.YOD.YOL" : # Here we are at the sub-line level, "this" is the sub-line, "this.APARENT.APARENT" is the headerCase ACTIONWhen "PROPAGATE"this.APARENT.APARENT.YTOTAL= fmet this.APARENT.APARENT.YTOTAMOUNTEndcaseEndcaseReturn$PROPERTIESCase CURPROWhen "YOH.YOD.YPRICE"Case ACTIONWhen "PROPAGATE"this.APARENT.YTOTAL= fmet this.APARENT.YTOTAMOUNTEndcaseWhen "YOH.YOD.YOL.YQTY", "YOH.YOD.YOL.YUNITPRICE"Case ACTIONWhen "PROPAGATE"this.APARENT.APARENT.YTOTAL= fmet this.APARENT.APARENT.YTOTAMOUNTEndcaseEndcaseReturn

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 transactionTrbegin [YOH]Update [YOH] Where YORDNUM=[L]YORDNUM and YSTATUS=1 With YSTATUS=2Commit# If no record updated, it can be because the order does not exist or because already validatedIf adxuprec=0Read [YOH]YOH0=[L]YORDNUM# Send a warning status if order already validated, and error status if order doesn't existIf fstat=[V]CST_AOKARET_VALUE=[V]CST_AWARNINGElseARET_VALUE=[V]CST_AERROREndifEndifEndcase

When a deletion is requested, the system also controls that the 'YSTATUS' is not equal to 2; otherwise, the deletion is rejected. This will be added in sequence to the previous lines. When 'CURPTH' is empty it is also done at the header level in the '$METHODS' section.

The code is the following:

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

Index Home