Coding Examples

How to Sort an Array in Ascending and Descending Order

Sorta-is used to sort arrays of single-dimensioned variables.

Sorta, sorts the array in ascending order by default, to sort the array in descending we need to use the keyword Desc.

Syntax –

Where,

• Sorta – Number Array List
• Number – number is length of an array or up to which number we want to sort
• Array List – list of array variable separated by comma

In this example, we are sorting two arrays. The Sorta command is sorting array NUMBER in ascending order and STR in ascending order.

To sort these arrays according to STR array –

Sorta 4 STR NUMBER
or
Sorta 4 STR

To sort in descending order –
Sorta 4 STR NUMBER  Desc

Executing a SQL query using 4GL language

The following is an example of executing a SQL query using the Sage X3 4GL language. In this example, the user may need to get the aggregate or maximum function. The 4GL Language does not have the necessary syntax. But one option is to use SQL query in 4GL. Just go through the statements mentioned below:

Format:

Local char REQSTR(250)(10)

For [Table name] where document no

REQSTR(0) = “SQL query ”

For fields_0 from Databasetyp Sql REQSTR(0…10) as tablename

Databasetyp: it is a database type for this we have create function so it will identify that current database uses by X3 server

 We have applied this with example.

Local Char REQSTR(250)(10)
For [Z3T] Where 1=1
    For [ZQRY] Where DOCNO = [F:Z3T]DOCNO
        REQSTR(0) = “SELECT DOCDATE_0, QRNO_0 FROM ZETIQUERY WHERE ROWID      =”+num$([F:ZQRY]  ROWID)+””
        For (Date DOCDATE, Char QRNO) From func GETDBTYP  Sql REQSTR(0..10) As [ZYOP]
            Update [Z3T] Where DOCNO = [ZQRY]DOCNO With DOCDATE = [F:ZYOP]DOCDATE
            Update [Z3T] Where DOCNO = [ZQRY]DOCNO With QRNO = [F:ZYOP]QRNO
            Update [Z3T] Where DOCNO = [ZQRY]DOCNO With MARK = 2
        Next       
    Next
Next

How to Delete a Record in Sage X3 by using Table Index (Key)

There are multiple ways to delete a record in Sage X3. But if we do not delete the record using its index, the database must read the full table to find the row to be deleted. That means deleting the row would be fast but finding would be very slow. The ‘DeleteByKey’ instruction deletes a record at a given key after checking for edit conflicts. It compares the UpdTick value of the [F] record with the UpdTick value of the corresponding database record and it deletes the database record only if the two ticks are equal.

Syntax:

DeleteByKey [ABV]KEY = KEY_VALUE
DeleteByKey KEY = KEY_VALUE

• ABV is the abbreviation of a table. It must match a File declaration.
• KEY is the description of the key used to access to the table. It can have one of the following syntax:
• KEYVAR where KEYVAR is the code of a key declared in the dictionary or the name of a temporary index from an Order by Key clause.
• KEYVAR(INDEX) where INDEX is a numeric expression giving the number of key parts considered for the condition.
• KEY_VALUE is a list of expressions separated by semicolons.

All these elements are optional.

Refer to the below screenshot for the code snippet:

-
Sage X3 and Sage Software are trademarks of Sage Software, Inc. Microsoft SQL Server, Microsoft SQL Enterprise Manager and SQL Server Management Studio are registered trademarks of Microsoft Corporation. Oracle is is a registered trademark or Oracle Corporation. All other product names used here are trademarks or registered trademarks of their respective owners.

-