Scenario: Suppose that you have an external application that manages fuel transactions for your vehicles, and you need those third-party entries to generate matching entries in the Verizon Connect system. Your goal is to create a template that automates the flow of information from the third-party application to Verizon Connect: Whenever a new fuel purchase occurs, a well-formed equivalent record is added to your Verizon Connect data, correctly associated with its vehicle and driver, for the correct location.
This walk-through gives an overview of the tasks you would need to complete in order to create such an integration.
Only built-in template capabilities are covered here; far more sophisticated data processing is possible through template scripting.
This topic contains the following sections:
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:
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
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:
That is, you must map each table column to an incoming value, using various methods:
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)
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
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:
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
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:
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
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
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: