How-to: Data export

Example 1

Task

We have sales orders for several books.

REQUIRE Time;

CLASS Book 'Book';
name 'Name' = DATA ISTRING[100] (Book) IN id;

CLASS Customer 'Customer';
name 'Name' = DATA ISTRING[50] (Customer) IN id;
address 'Address' = DATA ISTRING[50] (Customer) IN base;

CLASS Currency 'Currency';
name 'Name' = DATA ISTRING[50] (Currency) IN id;

CLASS Order 'Order';
date 'Date' = DATA DATE (Order);
number 'Number' = DATA STRING[10] (Order);

customer 'Customer' = DATA Customer (Order);
nameCustomer 'Customer' (Order o) = name(customer(o));

CLASS OrderDetail 'Order line';
order 'Order' = DATA Order (OrderDetail) NONULL DELETE;

book 'Book' = DATA Book (OrderDetail) NONULL;
nameBook 'Book' (OrderDetail d) = name(book(d));

quantity 'Quantity' = DATA INTEGER (OrderDetail);
price 'Price' = DATA NUMERIC[14,2] (OrderDetail);

currency 'Currency' = DATA Currency (OrderDetail);
nameCurrency 'Currency' (OrderDetail d) = name(currency(d));

FORM order 'Order'
    OBJECTS o = Order PANEL
    PROPERTIES(o) date, number, nameCustomer

    OBJECTS d = OrderDetail
    PROPERTIES(d) nameBook, quantity, nameCurrency, price, NEW, DELETE
    FILTERS order(d) == o

    EDIT Order OBJECT o
;

FORM orders 'Orders'
    OBJECTS o = Order
    PROPERTIES(o) READONLY date, number, nameCustomer
    PROPERTIES(o) NEWSESSION NEW, EDIT, DELETE
;

NAVIGATOR {
    NEW orders;
}

We need to create a button that exports the contents of the order in the XML format.

Solution

GROUP Info;
GROUP Customer : Info;

GROUP Specification;

GROUP price;

FORM Order
    PROPERTIES timeStamp = currentDateTime() ATTR

    OBJECTS order = Order
    PROPERTIES(order) IN Info date, number
    PROPERTIES IN Customer nameCustomer(order) EXTID 'name', =address(customer(order)) EXTID 'address'

    PROPERTIES IN Specification count = [GROUP SUM 1 BY order(OrderDetail d)](order) ATTR

    OBJECTS Detail = OrderDetail IN Specification
    PROPERTIES(Detail) nameBook, quantity,
                       nameCurrency IN price EXTID 'currency' ATTR, price IN price EXTID 'value'
    FILTERS order(Detail) = order
;

exportToXML 'Export to XML' (Order o) {
    EXPORT Order OBJECTS order = o XML;
    open(exportFile());
}

EXTEND FORM orders
    PROPERTIES(o) exportToXML TOOLBAR
;

To upload the data in the XML format, we need to create a form with the corresponding structure and then call the EXPORT operator. This operator will generate a file based on the form and then write the file to the exportFile property, which is then opened on the client side using the open action. The file will be opened in the software associated with its extension (i. e. ".xml").

The resulting XML will look like this:

<Order timeStamp="13.11.18 12:28:58">
   <Info>
      <date>13.11.18</date>
      <number>12</number>
      <Customer>
         <name>Customer2</name>
         <address>Address2</address>
      </Customer>
   </Info>
   <Specification count="2">
      <Detail>
         <nameBook>Book2</nameBook>
         <quantity>1</quantity>
         <price currency="USD">3.99</price>
      </Detail>
      <Detail>
         <nameBook>Book1</nameBook>
         <quantity>2</quantity>
         <price currency="EUR">4.99</price>
      </Detail>
   </Specification>
</Order>

The parent tag Order matches the name of the form. Names of all other tags are defined either by a property name on the form (e. g. date, number or count) or by the special parameter EXTID (e. g. name and address). Use of EXTID is preferable when the form contains several properties with the same name, but for different objects.

To create "intermediate" tags (e. g. Info, Customer or Specification), the correspondingly nested groups are created to which the necessary properties are then exported.

Use of the ATTR attribute allows you to put the value not in a separate tag, but in the attribute of a parent tag (e. g. timeStamp or count).

To put the currency attribute into the price tag, a group with the corresponding name is created, into which the nameCurrency and price properties are then added. Since the ATTR attribute is specified for currency, the currency is added to the price tag as attribute. And for the price, the predefined EXTID 'value' is set and therefore the value is written directly to the price tag. Without all these parameters, the result would be as follows:

<price><currency>USD</currency><price>4.99</price></price>

If you have a target XML template into which you need to export data, then use the following rules.

Any tag in the resulting file is generated by one of four elements:

1. Property (to be declared using PROPERTIES).
2. Object (to be declared using OBJECTS).
3. Property group (to be declared using GROUP outside the form).
4. Form (the upper tag)

• If the tag repeats several times in a row (e. g. Detail), declare an object with the matching name.

• If a tag contains nested tags, but is used only once (e. g. Specification), then declare a property group.

• If the tag contains attributes (e. g. count = "2"), then it must be generated either by a property group or by an object (depending on whether this tag is used once or more).

• If the tag contains a value, then such tag must be generated either by a property (e. g. nameBook or quantity) with the corresponding name or by an object/property group with the only "nested" property having EXTID 'value').

Example 2

Task

Similar to Example 1.

We need to create a button that exports the contents of the order in the JSON format.

Solution

exportToJSON 'Export to JSON' (Order o) {
    EXPORT Order OBJECTS order = o JSON;
    open(exportFile());
}

EXTEND FORM orders
    PROPERTIES(o) exportToJSON TOOLBAR
;

The resulting file will look like this:

{  
   "timeStamp":"13.11.18 15:11:45",
   "Info":{  
      "date":"13.11.18",
      "number":"12",
      "Customer":{  
         "address":"Address 2",
         "name":"Customer 2"
      }
   },
   "Specification":{  
      "count":2,
      "Detail":[  
         {  
            "quantity":1,
            "price":3.99,
            "nameBook":"Book 2"
         },
         {  
            "quantity":2,
            "price":4.99,
            "nameBook":"Book 1"
         }
      ]
   }
}

Note that the ATTR option is ignored when you export data in the JSON format.

Example 3

Task

Similar to Example 1.

We need to create a button that exports all the orders for a certain date in the CSV format.

Solution

exportToCSV (DATE date) {
    LOCAL file = FILE();
    EXPORT CSV HEADER FROM number = number(order(OrderDetail d)),
                    customer = nameCustomer(order(d)),
                    book = nameBook(d),
                    quantity(d),
                    price(d)
           WHERE date(order(d)) = date TO file;
    WRITE CLIENT DIALOG file() TO 'orders';
}

FORM exportParameters 'Parameters'
    OBJECTS d = DATE PANEL
    PROPERTIES(d) 'Date' = VALUE
;
exportToCSV 'Export to CSV' () {
    DIALOG exportParameters OBJECTS d INPUT DO
        exportToCSV(d);
}

EXTEND FORM orders
    PROPERTIES() exportToCSV DRAW o TOOLBAR
;

The first action gets a date as input and then generates a flat CSV file with a semicolon delimiter using the EXPORT operator. The file will look like this:

number;customer;book;quantity;price
14;Customer 1;Book 1;2;8.99
12;Customer 2;Book 2;1;3.99
12;Customer 2;Book 1;2;4.99

The column name is overridden for the first three parameters, while the other two use the names of the corresponding properties. The result is first written to the local property file. Then the user is prompted to save the file from that property to his workstation. This is done using WRITE operator.

The second action opens the form, asks the user to enter a date and then calls the first data export action.