Reports are easily created using the SQL language and some EMDI commands embedded in comments.

Variables you can include in your SQL code:

(used in the where command)

:apo date from
:ews date until
Reference description 1st parameter from running accounts
Reference code 2nd parameter from running accounts
:param1 3rd parameter from running accounts
:param2 4th parameter from running accounts
:search search for forms that have search checked
:orderby is added instead of the order by command for sorting
:loydoc points document number
:loypro points item code
:loypay redemption item code
:orderdoc abbreviation of the document that appears in related
:orderdid document line id based on abbreviation of the document
–nodecimals– unlimited decimals
–combo– drop down list

Fields in SQL code

(used in the select command)

doc the sales line number “Aa” (create a button that opens sales on the corresponding document)
cus the customer line number “Aa” (create a button that opens the corresponding customer)
pro the product line number “Aa” (create a button that opens the corresponding item)
prd the product line number “Aa” (create a button that opens the documents of the corresponding item)
ksq its result is used in the update script (creating a button that opens an editor for data processing)
eml e-mail to send|Name|Value|e-invoice link|Voucher number (create button that sends e-mail)
sms mobile to send|Name|Value|E-invoice link|Voucher number (create button that sends sms)
img Photo
NEXT VALUE FOR “gen_counter” the line number

 

Update script

:ksq value executed in the code with quotes
:ksp value executed in the code without quotes

 

It is possible to run up to 2 scripts separated by a semicolon “;”.

In example 1, we can edit and save the fields of the selected customer. Importantly, a key field named “Aa” must always be included.

Example 1:

Example 2 consists of 2 scripts. First a new line is created, if it doesn’t already exist, in the table “kinhseis” and then we choose to show from the same table 2 fields, “Order stage” and “Notes”. We also use the key field as in this case “Aa” (“kinhseis”.”Grammh”).

Example 2:

If there is an update query as the first, then it is executed before the user’s selection. If it is set second, then it is executed after the user selection.

Open external application

We use the “editing field” to define a list of options in a field we want.

In “field options” we write, separated by commas, the options we want the list to have. It is not mandatory.

We use the “read field” to get the contents of a field of our choice. It is not mandatory.

Using the “link” field we can call an external application.

When the link calls a web address we use the variables:

#VL1 processing field
#VL2 reading field

For example:

In case we call a local program we don’t need to use variables because they are defined as 1st and 2nd parameters respectively.

For example:

 

EMDI commands

The first line can include some EMDI commands as a SQL comment and is automatically generated every time you make a change.

It contains all the settings we make for a report.

For example, if you want to create a graph, then you fill in where xx the field for the x-axis, yy the field for the y-axis and xxlabel the field for the title of the x-axis.

For line numbering we use the following variable after select:

 

Colors

To give color we can define a variable that takes some values (up to 9) separated by a comma, e.g.:

1,22,2,45,3,90,3,111,9

The color that the line gets respectively is:

yellow,red,green,yellow,red,green,yellow,red,green

 

Expanding list

It is possible to activate a list of options. Each option inserts a code into the –combo– variable, which reads it from a corresponding file with a psql extension.

In the following example we create 2 options, DELIVERY and RESERVED

When the user selects DELIVERY then the content of the sql1.psql file is inserted in place of the –combo– variable.

Example psql file

or any sql code we want to combine with our main code.

We save the psql files in the same folder as the reports.

 

Rights

We can define which users, based on permissions, can run a report. In the “Rights” field we write the title of rights belonging to the users who will have access to the report.
For many titles we separate with a comma.
Blank field for no restriction.

 

Additional functions in SQL (functions)

Get field from delimited text:

Result: test2

Convert text to number with decimals

Result: 152,33

Result: 152,33

Convert text to number without decimals

Result: <null>

Result: 152