Reports are made easily with SQL programming language and other EMDI commands that are implemented on the comments.
Variables that you can use in the code:
(in “where” command)
:apo starting date
:ews final date
Report description 1st parameter from accounts
Report code 2nd parameter from accounts
:param1 3rd parameter from accounts
:param2 4th parameter from accounts
:search search for forms that search is checked
:orderby it is added for sorting instead of “order by” command
–nodecimals– unlimited decimals
–combo– drop-down list
Fields in the code
(used in the select command)
doc sales line number “Aa” (creates a button that opens sales in the selected document)
cus client line number “Aa” (creates a button that opens the selected customer)
pro product line number “Aa” (creates a button that opens the selected product)
prd product line number “Aa” (creates a button that opens the documents of the selected product)
ksq its result is used in the update script (creates a button that opens an editor for data editing)
eml the e-mail to be send|Name|Value|E-invoice link|Shipping tracking number (creates a button that sends an e-mail)
sms the mobile to be send|Name|Value|E-invoice link|Shipping tracking number (creates a button that sends sms)
img photo
NEXT VALUE FOR “gen_counter” : line number
Update Script
Up to 2 scripts can be executed, delimited with “;”.
In the 1st example, we can edit and save selected customer’s fields. It is important to always include the key field “Aa”.
Example 1:
1 |
select * from "pelates" where "Kvdikos"=:ksq |
The 2nd example contains 2 scripts. First, a new line is created (if not already exists) on the table “kinhseis” and then we select to show 2 fields from the same table, “Order stage” and “Notes”. In this case, we also use the the key field “Aa” (“kinhseis”.”Grammh”).
Example 2:
1 2 |
UPDATE OR INSERT INTO "kinhseis" ("kinhseis"."Grammh","kinhseis"."aapar") values (:ksq,(select first 1 "grammes"."Aapar" from "grammes" where "grammes"."Aa"=:ksq)); select "kinhseis"."Uesh" "Order stage","kinhseis"."Grammh" "Aa","kinhseis"."custom1" "Notes" from "kinhseis" where "kinhseis"."Grammh"=:ksq |
Custom fields from the update script
The “Field for editing” is used to set a list of options in the field we want.
In “Field options” we write the options for the list, separated by comma (“,”). It’s not compulsory.
The “Reading field” is used to get the contents of the field we want. It’s not compulsory.
By using the “Link” field, we can execute an external link or application.
When a web link is called, we can use the following variables:
#VL1: edit field
#VL2: reading field
For example:
1 |
https://www.mysite.com/myscript.php?number=#VL1&id=#VL2 |
When a local programm is called, there is no use for variables because they are being sent as 1st and 2nd parameters.
For example:
1 |
C:\Program Files (x86)\company\mysoftware.exe |
EMDI commands
The first line can contain EMDI commands as comments in SQL and is automatically created whenever you make a change:
1 |
--search-searchtitle-asearch-searchtitle-graph x-xx-graph y-yy-graph labels-xxlabel-from-fromvalue-to-tovalue-step-stepvalue |
Where searchtitle, the title of the search field (2 fields).
If you want to create a graph, then where “xx” you input the field for axis x and “yy” the field for the axis y and “xxlabel” the title of the field that is on the axis x.
To create loops, fill the fromvalue, tovalue, stepvalue.
To count the lines we use the following variable after the select:
1 2 |
select NEXT VALUE FOR "gen_counter" AS "Row #", ... |
Colors
In order to paint lines with colors (yellow,red,green) we can define a variable that takes up to 9 values delimited with comma “,” :
1,22,2,45,3,90,3,111,9
Lines are colored accordingly as follows:
yellow,red,green,yellow,red,green,yellow,red,green
Drop-down list
A drop-down list could be created where its each option inserts a code into the variable –combo– from the corresponding psql file.
In the following example, we create 2 options, DELIVERY and RESERVED:
1 |
sql1.psql|DELIVERY,sql2.psql|RESERVED |
When the user selects DELIVERY then the content of sql1.psql file is inserted in place of –combo– variable.
psql file example:
1 |
where "color"=2 |
or whichever sql code piece we would like to implement in our main code.
The psql files are saved in the same folder as the reports (usually C:\Program Files (x86)\SBZ systems\EMDI\reports).
Additional functions
Get field from split text:
1 |
SPLIT_STRING('test1;test2;test3',';',2) |
Result: test2
Convert text to decimal numbers
1 |
STRING_TO_FLOAT('152,33') |
Result: 152,33
1 |
STRING_TO_FLOAT('152.33') |
Result: 152,33
Convert text to numbers without decimals
1 |
STRING_TO_INTEGER('152,33') |
Result: <null>
1 |
STRING_TO_FLOAT('152.33') |
Result: 152