EDI Exports - General Information

EDI Exports - General Information

This article gives you basic information about Data Export.

Creating a Data Export

Before we can start creating the export we need to analyse the issue we are trying to solve. At its simplest you can work through a few easy questions to get you started.

  1. When would you like to export the data?   To run a data export, we need a trigger event. Some examples of a trigger event are such as a failed delivery, when a driver leaves the depot, when freight isn't delivered and returned to the depot.

  2. How often would you like to export the data?  The data can be exported every few minutes, every hour, every second day, once a month or any other time frame you desire.

  3. What data would you like to send?  Consignment updates, proof of deliveries, extra charges, freight scans, photos, runsheets, equipment transactions or anything else Transvirtual tracks and records.

  4. In what format would you like the data?  Emailed, Printed, Remote FTP, SMS or Web Service API.

  5. Who needs the data you are exporting?  A customer, driver, agent, depot manager, accounts staff or manager. The choices are limitless.

A common example might be:

  1. Trigger: A driver selects a 'check address' delivery issue against a consignment in the mobile application

  2. Trigger Period: Check every 2 minutes

  3. Data Required: Consignment Information

  4. Export Framework: Generate an email

  5. Export to: customerservice@yourcompany.com.

This means that every 2 minutes the system will send data for consignments whose status has changed to 'check address' since it last exported. It will email the details to customer service for them to follow up.

We now have the information to be able to build this export

  1. Now that we know that the trigger event is a Consignment Status, we can go to the EDI > Data Export > Consignments Status Updates page.

  1. You will be presented with a page that shows all currently setup rules.

  1. Click on the New Rule button and enter a name for the rule and select the Export Framework or method of sending that will be used.

  • Rule Name: Check Address Email

  • Export Framework: Email

  1. Click on the Create button to create the rule.

  2. This will now create a rule and it will be presented in the list on the left in orange. (Orange means the rule is disabled)

  3. Once you select this rule, the right-hand side of the screen will be populated with the current configuration of this rule.

  1. Configure the Trigger Period as required.  Every 2 Minutes is the Default

  2. Configure the Export Framework to email to the appropriate email address with subject and body.

  3. Select the Export File Type and populate with the required fields

  4. Click the Update button to save these settings.

  5. Configure the Data Filter to "Status Name" "Equals" "Mobile - Check Address"

  1. Go back up to the Rule Details and turn on "Perform a Test Export" and click . This will send a test email with the rules you have setup

  2. Turn on "Enable Export" once ready and click Update to enable this rule.

Export Framework

There are currently 6 different export frameworks used in Transvirtual i.e., FTP, Email, Printer, Webservice, Account Package and SMS. Refer below to know how to configure each of them.

FTP Export Framework

The FTP export framework allows the transfer of data via an FTP or SFTP Connection. 

To configure the FTP connection, you may require filling the following information.  To do so, first select the ftp in the export framework field and then click on setting button to configure the information as describe below.

 
Most of these settings will be provided by the party hosting the FTP Service.

  • FTP Server URL:  The hostname of the FTP service (i.e. ftp.domain.com)

  • Username:  Username for the service

  • Password: Password for the service

  • Port:  Defaults for FTP is 21 & SFTP is 22.

  • FTP Folder:  Path to the folder the data will be exported to. (i.e., "POD/photo/in")

  • Active Mode:  By default, all data transfers are initiated with a PASV transfer, Tick this box to enable Active transfers.

  • SFTP:  Tick this box if you require a Secure FTP connection

  • Update other configuration that have the same 'Server FTP URL':   Tick this box if you would like to update other EDI Export rules that use the same FTP URL

Email Export Framework

The Email export framework allows the transfer of data via an Email. To configure the Email, you may require filling the following information.  To do so, first select the Email in the export framework field and then click on Setting button to configure the information as describe below.



When configure the Email setting, there are three main areas to fill in i.e., Details, Email Body and HTML Body.

Details

  • Send to Consignment Sender Email:  Yes/No

  • Send to Consignment Receiver Email:  Yes/No

  • Send to Consignment Creator: Yes/No

  • Send to Customer/Assigned Agent Contact:  Select the Customer/Agent contact type you would like to receive the email.

  • Email To:  Enter one or more email addresses to send the email to.

  • Subject:  Enter the Email Subject you require.

 The Customer Contact emails will be based on what has been setup in the relevant Customer Card under the Contact Details Tab as shown below.
    
To navigate contact detail tab of customer, on TMS web portal, go to Transport > Configuration and Setup > Card Files, click on Customer tab and then double click on applicable customer, the customer detail will display. Now click on Contact Details as shown above.


Once done, click on Validate button to check the preview and click on Update button to save the changes. 

Email Body

The email body is a text-based email that can include variable information about your EDI Rule.
There are 2 types of variable data that can be entered. This is handled using Razor Syntax, if you require a complicated email, don't hesitate to contact the support team to help you understand the syntax.
 

  1. Field Insertion

Right click in a location you need to enter a field. You will be presented with a menu to select fields. Find you field and select it and it will be presented like follows.

  1. @consignmentStatus.ConsignmentNumber

You will need to change the name of the field between the "@" and the "." to be "Model" to look similar to the following;

@Model.ConsignmentNumber

This will enable the output of the field in the location you require on the page.

  1. List insertion

Right click in a location you need to output a list. You will be presented with a menu to select lists and fields. Go into the List menu and select the list type you would like to insert. It will display similar to the following;

@foreach(var consignmentStatus in Model.ConsignmentStatusChange){

}

Now you need to insert the fields you wish to output within the listed loop, This is done within the "{}" brackets that was inserted above. Right click in there and select the fields required, so it looks similar to the following;
@foreach(var consignmentStatus in Model.ConsignmentStatusChange){
@consignmentStatus.ConsignmentNumber
@consignmentStatus.ConsignmentCurrentStatusDate
@consignmentStatus.ConsignmentCurrentStatus
}
Within the "{}" brackets if you would like to output text is must be done using a @Raw() command. An example follows;
@foreach(var consignmentStatus in Model.ConsignmentStatusChange){
@Raw("Consignment Number: ")@consignmentStatus.ConsignmentNumber
@Raw("Status Change Date: ")@consignmentStatus.ConsignmentCurrentStatusDate
@Raw("Status Value: ")@consignmentStatus.ConsignmentCurrentStatus
}
This would output
Consignment Number: CUSTA37157
Status Change Date: 1/09/2017 3:19 PM AEST
Status Value: Assigned
Click on the validate button to check and preview and click the update button to save your changes.

Email Body (HTML)

The email body HTML is the same as the text-based email body above, but you have the ability to present the email in a much nicer format with images etc. You can still insert the same variable information into a HTML Email. 


Printer Export Framework

The Print export framework allows the printing of data to a printer configured in the software. Once selected, the Printer Name and Print Count become available below the export framework as shown below.

 
  1. Printer Name: Use the drop-down button and select the applicable printer.
  2. Print Count: This field is generally used how many prints you want. In this field manually enter the count. 

SMS Export Framework

The SMS export framework allows the transfer of data via an SMS. To configure the SMS, you may require filling the following information.  To do so, first select the SMS in the export framework field and then click on Setting button to configure the information as describe below.

 

Field Description

  • Send to Consignment Sender Phone:  Yes/No

  • Send to Consignment Receive Phone:  Yes/No

  • Send to Customer Contact:  Select the contact type you require from the Customer Contact List

  • Mobile Number:  Enter a mobile number for the required contact

  • SMS Message:  SMS Message can be a simple text or can also include variable data.

There are 2 types of variable data that can be entered. This is handled using Razor Syntax, if you require a complicated SMS, don't hesitate to contact the support team to help you understand the syntax.

    1. Field Insertion

Right click in a location you need to enter a field. You will be presented with a menu to select fields. Find you field and select it and it will be presented like follows;

@consignmentStatus.ConsignmentNumber

You will need to change the name of the field between the "@" and the "." to be "Model" to look similar to the following;

@Model.ConsignmentNumber

This will enable the output of the field in the location you require on the page.

    1. List insertion

Right click in a location you need to output a list. You will be presented with a menu to select lists and fields. Go into the List menu and select the list type you would like to insert. It will display similar to the following.

@foreach(var consignmentStatus in Model.ConsignmentStatusChange){

}

Now you need to insert the fields you wish to output within the listed loop, This is done within the "{}" brackets that was inserted above. Right click in there and select the fields required, so it looks similar to the following;

@foreach(var consignmentStatus in Model.ConsignmentStatusChange){

@consignmentStatus.ConsignmentNumber

@consignmentStatus.ConsignmentCurrentStatusDate

@consignmentStatus.ConsignmentCurrentStatus

}

Within the "{}" brackets if you would like to output text is must be done using a @Raw() command. An example follows;

@foreach(var consignmentStatus in Model.ConsignmentStatusChange){

@Raw("Consignment Number: ")@consignmentStatus.ConsignmentNumber

@Raw("Status Change Date: ")@consignmentStatus.ConsignmentCurrentStatusDate

@Raw("Status Value: ")@consignmentStatus.ConsignmentCurrentStatus

}

This would output

  Consignment Number: CUSTA37157

  Status Change Date: 1/09/2017 3:19 PM AEST

  Status Value: Assigned

Click on the Validate button to check and preview and click on the Update button to save your changes.

Export File Types

The export file types within Transvirtual allows you to export your data in a customized output through a range of file types.

There are currently 2 base export file types to perform your data export, follow each of the links to see an article on how to configure each of them.

  • XML/JSON/TEXT/Other

  • CSV/Excel

The 3rd option allows you to combine the 2-output type above into a single output

  • CSV and TEXT

Basic CSV/Excel

This guide will show you how to setup a file export using the CSV/Excel file type export. The fields required for setup of this file type depend on the EDI Category. Some are POD Only related.



  • Delimiter Char: This is the character that separates the fields within the row. This is commonly a comma.

  • CSV File Type:  Select Standard for CSV, Excel 2003 or Excel 2007.

  • Include Header Row:  Tick the box to include a header row.

  • Send PDF Report:  Send a PDF of the output file.

  • Send Consignment Image:  Tick the box to send an image of the consignment.

  • Send Signature Image: Tick the box to send a signature image.

  • Export POD Image Format:   Normal (PNG), Black & White, Group 4 Tiff, PDF.

  • File Name Format (POD Image):   POD image file name that is exported. [number] to display consignment number in file name [ddMMyyyy] to display day, month and year of file export [ddMMyyyyhhmmss] to display day, month, year, hour, min and sec of file export.

  • File name format:  XML (or other) file name that is exported. [ddMMyyyy] to display day, month and year of file export [ddMMyyyyhhmmss] to display day, month, year, hour, min and sec of file export.

  • File Extension Name:  The extension of the file type. Defaults to ".csv" can be changed to anything required.

  • Data to Export:  By default, a few common field names will be inserted in this field. The order of these is replicated in the output file. Left click to add a field to the output. You can re-order the fields by dragging the field that requires moving.

Once done the changes, click on the Update button to save your changes.

Advanced Formats (XML/JSON/TEXT/Other)

This guide will show you how to setup a file export using the XML/Json/Text/Other file type export. The fields required for setup of this file type depend on the EDI Category. Some are POD Only related.



  • Send PDF report: Yes/No. This is a PDF File of the below File Structure

  • Send consignment image:   Yes/No

  • Send signature image:  Yes/No

  • Export POD Image Format:  Normal (PNG), Black & White, Group 4 Tiff, PDF

  • File name format (POD Image):   POD image file name that is exported. [number] to display consignment number in file name [ddMMyyyy] to display day, month and year of file export [ddMMyyyyhhmmss] to display day, month, year, hour, min and sec of file export

  • File name format:  XML (or other) file name that is exported. [ddMMyyyy] to display day, month and year of file export [ddMMyyyyhhmmss] to display day, month, year, hour, min and sec of file export

  • File extension name:  The extension of the file type. Defaults to ".xml" can be changed to anything required

  • Strip out whitespace:  Yes/No

  • Strip out new line:  Yes/No

  • File Structure:  By default, an XML output is created to get you started, this can be modified as suits.

  1. Field Insertion

Right click in a location you need to enter a field. You will be presented with a menu to select fields. Find you field and select it and it will be presented like follows;

@consignmentStatus.ConsignmentNumber

You will need to change the name of the field between the "@" and the "." to be "Model" to look similar to the following;

@Model.ConsignmentNumber

This will enable the output of the field in the location you require on the page.

  1. List insertion

Right click in a location you need to output a list. You will be presented with a menu to select lists and fields. Go into the List menu and select the list type you would like to insert. It will display similar to the following;

@foreach(var pod in Model.PodData){

}

Now you need to insert the fields you wish to output within the listed loop, this is done within the "{}" brackets that was inserted above. Right click in there and select the fields required, so it looks similar to the following;

@foreach(var pod in Model.PodData){

@Raw("<Delivery>")

@Raw("<DeliveryTime>")@Model.ReceivedTime@Raw("</DeliveryTime>")

@Raw("<ReceivedBy>")@Model.ReceivedBy@Raw("</ReceivedBy>")

@Raw("<ConsignmentNumber>")@Model.ConsignmentNumber@Raw("</ConsignmentNumber>")

@Raw("</Delivery>")

}

Within the "{}" brackets if you would like to output text must be done using a @Raw() command. An example follows;

<root>

<DeliveryList>

@foreach(var pod in Model.PodData){

@Raw("<Delivery>")

@Raw("<DeliveryTime>")@Model.ReceivedTime@Raw("</DeliveryTime>")

@Raw("<ReceivedBy>")@Model.ReceivedBy@Raw("</ReceivedBy>")

@Raw("<ConsignmentNumber>")@Model.ConsignmentNumber@Raw("</ConsignmentNumber>")

@Raw("</Delivery>")

}

</DeliveryList>

</root>

This would output

<root>

<DeliveryList>

<Delivery>

<DeliveryTime>30/05/2016 11:53 AM AEST</DeliveryTime>

<ReceivedBy>David</ReceivedBy>

<ConsignmentNumber>DEM90241</ConsignmentNumber>

</Delivery>

</DeliveryList>

</root>

Click on the Validate button to check and preview and click on the Update button to save your changes.

Combined Basic and Advanced Files

CSV and Text when used enables you to send both CSV/Excel and XML/Json/Text/Other files when configured.

  1. Configure CSV/Excel as above

  2. Configure XML/Json/Text/Other as above

  3. Select the Combined Basic and Advanced Files and click Update

Invoice Export Rules

1. TransVirtual allows users the ability to break up Invoice EDI's so that when this information exports to MYOB or Xero, the different prices that make up an Invoice can be sorted into different General Ledger Codes and Jobs of the connected Accounts Package. MYOB and Xero can have sections that show whether a price is an expense or a type of income-for example. Rather than send the information across as one, you may wish to split the Invoice information up; to more accurately reflect how an Invoice price has been compiled.
2. Refer Invoices Article for further detail and show you how to split up Invoice export information, to see it click here. 

Extra Information 

The above setup can be used for Xero or MYOB. One of the most important factors in this setup is that the Account or Job Code you are linking to is correct. Where you want to send this information should be the Account or Job codes you have linked this export to. This is where the information will send to in your Accounts Package. 
If you need help using Xero, or MYOB there are plenty of online articles, videos etc.

EDI Export - Advanced Helper Functions


This section will show you the available helper functions to display data in meaningful ways on your report.




Replace Lookup

Takes the input string and replaces Value1 with Value2 for each occurrence.
Code: {ReplaceLookup(input, [Value1|Value2])}


Returns: String with replacement complete




Total Number of Pallets on a Consignment


Code: {ConsignmentTotalPallets(idConsignment)}


Returns: Number




Total Number of non-pallet Items on a Consignment


Code: {ConsignmentTotalNonPallets(idConsignment)}


Returns: Number




Trim or Pad a String to a Length to the Right


Code: {TrimPad(string, length)}


Returns: "TEST "






Trim or Pad a String to a Length to the Left

Code: {TrimPadLeft(string, length, padValue)}


Returns: " TEST"




Max Width of a String in Characters


Code: {Max(str, length)}


Returns: " TESTIN"






Hour Range


Provides a time range an hour before and an hour have the tame passed in


Code: {HourRange(object dateTime)}


Returns: " 13:45-15:45"






Split String on a Character and Return a Specific Element


This function enables you to split a string based on a character and return a specific element of that back


Code: {Split(String, Split Value, ReturnNumber)}


Example: {Split("this|is|a|test", string "|", 1)}


Returns: " this"




Dates


@Model.Date


That would output a string in the default culture date time string. For greater control, you could do the following:


@Model.DateDateTime which converts the string to a datetime object which then allows all .net functions such as @Model.DateDateTime.ToString(“dd/MM/yyyy”)


@Model.DateDateTimeUTC – same as above, but in place of the client time zone will be UTC time


@Model.EventLocationDateTime – same as above, but the timezone of the event. Only used if client has multi offices with different timezones.




Same for decimal, example @Model.Cubic would be a string, if we need to call .net functions, can convert to a decimal via:


@Model.CubicDecimal then allows things like @Model.CubicDecimal.ToString(“n3”) which would be converted to 3 decimal places with standard 1000 , format


    • Related Articles

    • Customer Service Ticket EDI Export

      In Transvirtual there is the ability to create Customer Service tickets for a consignment. The purpose of these tickets is to raise any issues to the attention of your team so that they can be resolved. Transvirtual can export any created ticket ...

    • Photos and Images EDI Import

      In Transvirtual there is the ability to import photos and images and attach them to relevant consignments. This is done by creating an EDI (Electronic Data Interchange) Import rules in your account that can accept information via Email, FTP or Manual ...

    • General Import Information

      Introduction to Imports The import framework within TransVirtual allows you to import your data in a number of ways. This framework will expand as technology develops. There are 6 import frameworks as below. Remote FTP TransVirtual Hosted FTP Server ...

    • Integration - General Information

      Cross Client Data/Integration Requests Transvirtual works with a range of interconnected clients and prides ourselves in the robustness and easy roll out of our integrations. Sometimes we are approached with requests to: Confirm the presence/absence ...

    • Re-send/Rediscover Exports

      You can perform the following tasks on exports: Re-send: When an export rule has been triggered and completed, but you want to send it again. Rediscover: When an export rule hasn't been triggered, but changes to the data or export rule means it will, ...