EXPORTMI

EXPORTMI is a generic API used to extract data from M3 and comes with data customization capabilities that provide flexibility in data selection. In general, this API has the capability to define SQL-like data selection and data search using the M3 Function Search (IES) features. The returned data is formatted as a single long field either containing a flat file format or a comma separated format.

Even with such flexibility, EXPORTMI has its limitations:

  • The retrieval of information is restricted to a single table in M3. Relating other tables is not possible. The API requires that the table where information is retrieved from contains the M3 company field (CONO).
  • Filters including company selections are not supported. M3 company is always set to the company used by the API session (normally the default company comes from the user unless a different company was set during the log in of the API session).
  • Filters including division are only supported for API sessions logged in on a global division (blank division).

Search transaction

The search transaction mainly uses the M3 Function Search (IES) features to filter data. This robust search functionality enhances the data selection and customization of EXPORTMI.

Follow these guidelines on how to use the transaction:
  • Fields to be included in the output (FLDS) can be selected. If left blank, all fields from the table are included.
  • The separator parameter (SEPC) is used to define the separator between the field values. If left blank, the result is a flat file format including trailing blanks. If a character is specified, that character is used as a separator and possible blanks are trimmed from the values. If the character is '*' or '#', the field values are also prefixed with the field name (the same applies to all lines in the result) followed by a space.
  • If the header parameter (HDRS) is set to 1, the first returned record contains the field names formatted in the same way as defined in the separator parameter.
  • Different searching methods are allowed as input by the search query (SQRY). See Using Search Functions.

Select transaction

The selection is defined in a SQL-like syntax and the same API transaction can be used for almost any table in M3.

The syntax for the filter must adhere to these guidelines:

  • The filter used to select the information is written in a SQL-like syntax.
    Note: This is not actual SQL, but it shares a similar syntax. Operations such as IN(), "order by" etc., are not supported.
  • You can define a specific index for the table, this will control the sorting of the returned list. If no index is specified, the 00-index will be used. If filtering is done, it is recommended from a performance perspective to use an index matching the filter fields.
  • Operators 'from', 'where', 'and', and 'or' must be written in lower case. They must also have a single blank space each side.
  • Operators '=', '<>' etc., must have a single blank space each side.
  • Special operators such as 'count(*)' can be used. The result is a number equal to the number of records that matches the filter selection.
    Note: count(*) returns the number of records for the current company. To count all records in the table you should use count(#).
  • Field names must be written in capital letters and contain the table prefix. That is, 'MMITNO' is correct, but 'ITNO' is not.
  • It is recommended, from a performance perspective, to always specify the individual fields. However, as an exception, you can use the general '*' to select all fields. That is, all fields that can fit in the transaction are included in the returned information.
  • Comparison values are specified with or without surrounding quotation marks '', regardless of the type of value, such as string, numeric etc.
  • The separator parameter (SEPC) is used to define the separator between the field values. If left blank, the result is a flat file format including trailing blanks. If a character is specified, that character is used as a separator and possible blanks are trimmed from the values. If the character is '*' or '#', the field values are also prefixed with the field name (the same applies to all lines in the result) followed by a space.
  • If the header parameter (HDRS) is set to 1, the first returned record contains the field names formatted in the same way as defined in the separator parameter.
  • Examples of the input string:
    Note: The blank space until position 16, which contains the separator parameter (SEPC). Position 17 then contains the Header parameter (HDRS).
    • With specified fields: Select ;1MMSTAT,MMITNO,MMITDS,MMRESP,MMDCCD,MMUNMS,MMITGR,MMITCL from MITMAS where MMSTAT = 10 or MMSTAT = 90 and MMITCL = 'Y001'.
    • Without specified fields: Select ;1* from CEMAIL where CBEMTP = 04 and CBRGDT <= 20160111.
    • With specified fields and specified index (note that the filter values matches the index): Select ;1MMSTAT,MMITNO,MMITDS,MMRESP,MMDCCD,MMUNMS,MMITGR,MMITCL from MITMAS80 where MMITTY = ‘D02’ and MMSTAT = ‘50’.
Related topics