The first step to creating your integration is to determine how your external application data fields need to map onto TDE logical tables. Start by analyzing which table you need to work with, and then which of the columns in that table can or should be updated by your application.

For this scenario, you would identify the FuelTransaction table, and then you would list just the columns that you are going to be writing to:

1. AccountNumber
2. Address
3. CardNumber
4. Country
5. DriverName
6. FuelType
7. FuelVolume (primary key)
8. PurchaseAmount
9. PurchaseTime (primary key)
10. TransactionType (primary key)
11. UnitId (primary key)

Note which table columns are flagged as required or are primary keys, and be sure that you provide incoming fields that populate them. Now compare this list with the output that you can export from your external application:

PARTNER_TRX_ID~~PROC_ACCT_NO_1~~PROC_ACCT_NAME~~MERCH_NAME~~MERCH_CITY~~MERCH_ST
~~MCC_ID~~TRAN_NO~~TRAN_DATE~~POST_DATE~~PRODUCT_ID~~PRODUCT_TYPE~~PRODUCT_QTY~~
PRICE_EACH~~ODOM~~DRIVER_ID~~VEHICLE_ID~~DISC_AMT~~PRODUCT_CODE~~TRAN_TIME~~VEH_DESC
~~DRV_FNAME~~DRV_LNAME~~MERCH_ADDR_LN1~~MERCH_ADDR_ZIP~~CARD_NO

86851728~~DQ086~~JGA CORP. BEACON~~PILOT #087~~JACKSONVILLE~~FL~~99177~~188~~
2/12/2012~~2/12/2012~~111125~~X~~199.8~~4~~000013099~~TA8011~~VEHICLE_A_1~~~~
D72~~01.48.19~~VEHICLE_A_1~~VERIZON CONNECT~~CUSTOMER~~3126 Fleet Avenue N~~32254-0000
~~XXXXXXXXXXX17865

1. CARD_NO
2. DISC_AMT
3. DRIVER_ID
4. DRV_FNAME
5. DRV_LNAME
6. MCC_ID
7. MERCH_ADDR_LN1
8. MERCH_ADDR_ZIP
9. MERCH_CITY
10. MERCH_NAME
11. MERCH_ST
12. ODOM
13. PARTNER_TRX_ID
14. POST_DATE
15. PRICE_EACH
16. PROC_ACCT_NAME
17. PROC_ACCT_NO
18. PRODUCT_CODE
19. PRODUCT_ID
20. PRODUCT_QTY
21. PRODUCT_TYPE
22. TRAN_DATE
23. TRAN_NO
24. TRAN_TIME
25. VEHICLE_ID
26. VEH_DESC

The two lists look mismatched because they are. Some fields, such as ODOM (odometer), have no place in the FuelTransaction table, so you can ignore them; others, such as MERCH_CITY, are needed, but you cannot use them by themselves. Fortunately, TDE templates offer many ways to transform your external data on the fly, so you can work with the data exactly as your external application exports it.

Once you know which table columns are going to be involved, you can plan out how to populate them from the import file fields that you generate from the external application. Describe how each of the TDE table columns you identified could map to one or more fields in your import file:

1. AccountNumber = PROC_ACCT_NO
2. Address = must combine MERCH_ADDR_LN1 and MERCH_CITY and MERCH_ST and MERCH_ADDR_ZIP
3. CardNumber = CARD_NO
4. Country = must always be "USA"
5. DriverName = must combine DRV_FNAME and DRV_LNAME
6. FuelType = PRODUCT_CODE
7. FuelVolume = PRODUCT_QTY
8. PurchaseAmount = must be calculated from PRODUCT_QTY and PRICE_EACH
9. PurchaseTime= must combine TRAN_DATE and TRAN_TIME
10. TransactionType = must always be "Sale"
11. UnitId = VEHICLE_ID

That is, you must map each table column to an incoming value, using various methods:

• Direct, field-to-column mapping
• Combining several fields into one column
• Calculating a column value based on several fields
• Hard-coding a known value to a specific column

You will accomplish these data transformations within the template syntax itself, which greatly streamlines both implementation and maintenance.

With the data planning done, you can now open your favorite text editor and begin drafting the contents of the template. The template itself follows the simple text format of an INI configuration file: a collection of name/value pairs, which are grouped into a few sections.

You begin by writing the [Template] section, which declares the target table, the template name, the template type ("intent"), and any optional choices, such as changing the delimiter or suppressing output headings:

[Template]
TemplateVersion = 1.0
TableID = FuelTransaction-1.0
TemplateName = CreateFuelWithPoints
Intent = Create
Delimiter = ~~                                  #  required if you do not use the default (comma)

Tips

• You can query TDE for a complete list of templates.
• To develop a new template, find one that is close to your goal and copy it to serve as a starter.
• Create self-documenting template names with a naming convention that clarifies the intent and affected table, as well as the goal.
• Use the pound (#) character to start in-line comments; leave ample comments to explain your choices.
• For TableID, the best practice is to always include the table name in a comment after the GUID.

Next, you can tackle the calculations you need. The template engine supports an optional [Calculated] section, which lets you operate on the input data. Always put the name (variable) on the left, equal to the function (operator or built-in function) on the right.

To create the Address value, create a variable in [Calculated] and set it equal in [Output] to the concatenation of all of the address fields that you are importing:

[Calculated]
_address = Input.MERCH_ADDR_LN1 || " " || Input.MERCH_CITY || ", " || Input.MERCH_ST || "  " || Input.MERCH_ADDR_ZIP

[Output]
Address = _address  # Imports the calculated address into the Address column.

To create the "PurchaseAmount" value, create another variable in [Calculated] and set it equal to the quantity multiplied by the price. Then use that variable in the [Output]:

[Calculated]
_address = Input.MERCH_ADDR_LN1 || " " || Input.MERCH_CITY || ", " || Input.MERCH_ST || "  " || Input.MERCH_ADDR_ZIP
_purchaseAmount = Input.PRODUCT_QTY * Input.PRICE_EACH

[Output]
Address = _address
PurchaseAmount = _purchaseAmount

Tips

• Creating variables in the [Calculated] section (rather than doing the transformations on the [Output] line itself) improves the readability of your template.
• Use a simplified template and test input file to verify your calculations on their own.
• Deliberately include bad data in your test runs to ensure that you see the results that you expect.

Next, you can tackle the data conversions that you need. The template engine supports data conversions directly in the [Output] section, which lets you operate on the input data.

Quantifiable data: Since FuelVolume is the data type Volume, you see that the default units are liters. If you had data that was data type Volume but in gallons, you could convert the number simply by putting the unit of measure after the column name:

FuelVolume(gal)

FuelVolume(Volume,gal)

Timestamps: PurchaseTime requires you to concatenate the input date and time (which you now know how to do) to create the Timestamp, but it poses another problem: by default, dates are in UTC (Coordinated Universal Time), not the time zone in which the transaction was timestamped! Again, you can solve both problems in a single line in your template: by using data type formatting, you can import your time data into a native Verizon Connect UTC timestamp that is converted from the timezone specified (here, CST):

[Output]
...
FuelVolume(Volume,gal) = Input.PRODUCT_QTY
PurchaseTime(M/dd/yyyy HH.mm.ss,CST) = Input.TRAN_DATE || " " || Input.TRAN_TIME

Tips

• Always look up the Data types of the table columns that you're converting, to verify the defaults, valid units, and requirements for each.
• See DateTime conversion for timestamp conversion and formatting, and for the complete list of time zone codes.

When you integrate data from external systems, you will need a common identifier so that you have a way to locate the same record in both the external system and in Verizon Connect tables. To create this common identifier, you need to import your External keys by adding a custom identifier into parentheses on the Id type column:

unitId(VehicleIdMap)

An external key is a non-Verizon Connect value that you pair with a Telogis Id to uniquely identify an object in TDE (such as a job, marker, route, or unit). These external key values input as Text and are stored as custom columns in your table, associated with your native Telogis Ids.

[Output]
...
unitId(VehicleIdMap) = Input.VEHICLE_ID

Tips

• The custom identifier that you put in the parentheses becomes the new column name for your external keys.
• To create a Retrieve template to check this imported data, be sure to use the same custom identifier.

To finish your template, add in the hard-coded values that you need, as well as the one-to-one mappings of fields to columns:

[Output]
...
TransactionType= "Sale"
Country = "USA"
AccountNumber = Input.PROC_ACCT_NO_1
CardNumber= Input.CARD_NO
FuelType = Input.PRODUCT_CODE

Now your template is a text file that looks something like this:

[Template]
TemplateVersion = 1.0
TableID = FuelTransaction-1.0
TemplateName = CreateFuelWithPoints
Intent = Create
Delimiter = ~~

[Calculated]
_address = Input.MERCH_ADDR_LN1 || " " || Input.MERCH_CITY || ", " || Input.MERCH_ST || "  " || Input.MERCH_ADDR_ZIP
_purchaseAmount = Input.PRODUCT_QTY * Input.PRICE_EACH

[Output]
Address = _address
PurchaseAmount = _purchaseAmount
FuelVolume(Volume,gal) = Input.PRODUCT_QTY
PurchaseTime(M/dd/yyyy HH.mm.ss,CST) = Input.TRAN_DATE || " " || Input.TRAN_TIME
unitId(VehicleIdMap) = Input.VEHICLE_ID
TransactionType= "Sale"
Country = "USA"
AccountNumber = Input.PROC_ACCT_NO_1
CardNumber= Input.CARD_NO
FuelType = Input.PRODUCT_CODE

Tips

• For Create and Update templates, the order of [Output] fields does not matter.
• For Retrieve templates, the data is returned in the order of the [Output] section.
• For Retrieve templates, you can sort results by one or more columns by adding OrderBy = to the [Template] section.

TDE integration offers a web service that you can call directly (via REST) to POST data into the Verizon Connect Platform.

When you are ready to upload your template to Verizon Connect, follow this process:

1. Request a token, or else include the user/password on each query string.
2. Upload your template as the text body of a POST call.
3. Run your template with whatever parameters you need.
4. Update your template if you discover an error or need to make a refinement.

Tips

• See Using the Web Service for details about these REST calls.
• Review the HTTP status codes for help with any errors you may encounter.