Collapse AllExpand All

1.2.2. Defining New EDI Tokens

You can also define your own tokens for more complex needs. Any database query that you can write with MetaSQL and the basic tokens above can be used to create custom tokens and set their values.

Note

For a complex, real-life example, see Chapter 6, Connecting with Incidents.

This capability is useful in several ways:

  • Some stock xTuple ERP reports require more parameters than just the basic five

  • You might want to share EDI Profiles between Customers, and so need to select different email addresses for different Invoices

  • You might want to share EDI Profiles between different Document Types for a single Customer but copies of these documents get sent to different people within your own company

Defining and using your own EDI Tokens requires about the same amount of knowledge as writing your own OpenRPT report—a little SQL, a little MetaSQL, and a little bit about the tables in the xTuple ERP database. The basic idea is that you write a database query that returns one record. You can use any of the basic tokens as MetaSQL parameters in that query. The names of the columns in that returned record become the names of the new tokens and the values in that record become the values of the tokens.

For example, let's say you want an EDI Profile that emails a copy of every Invoice to the Customer's billing Contact and to the Sales Rep for that Customer. So create an EDI Profile with an Invoice EDI Form that describes how to get the billing Contact and Sales Rep email addresses:

  1. Set up the EDI Profile just as described in Section 3.2, “Create a Simple EDI Profile”

  2. Change the "To" email address to </customeremail> (this is our first custom EDI token)

  3. Change the "Cc" email address to </salesrepemail> (our second custom token)

  4. Select the Invoice line in the list of EDI Forms and click EDIT

  5. Change the "Query" field to this:

    SELECT cntct_email AS customeremail,
           usr_email   AS salesrepemail
      FROM invchead
      JOIN custinfo ON (invchead_cust_id=cust_id)
      JOIN cntct    ON (cust_cntct_id=cntct_id)
      JOIN salesrep ON (cust_salesrep_id=salesrep_id)
      JOIN emp      ON (salesrep_emp_id=emp_id)
      JOIN usr      ON (emp_number=usr_username)
     WHERE (invchead_id=<? value('invchead_id') ?>);

    This query uses the basic token invchead_id as a MetaSQL parameter to find the particular Invoice. It assumes that the Customer has been set up with a Billing Contact and the Sales Rep has been set up as an Employee and xTuple ERP user. A more complicated query could use the correspondence contact if the billing contact has no email address and could work around the assumptions about the Sales Rep setup.

  6. Check the Transmission tab on the Customer window to make sure that the desired Customers have this EDI Profile attached for Invoices.