Data Exports

Data Exports

Creating a Data Export


Before we can start creating the export we need to analyze 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 export frameworks, see below on how to configure each of them.

FTP Export Framework

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


Configuring the FTP connection requires the following information to be filled out. 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. 

There are three main area's to fill in.


Details


  • Send to Consignment Sender Email:  Yes/No

  • Send to Consignment Receiver Email:  Yes/No

  • Send to Customer Contact:  Select the Customer 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.


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 the  button to check and preview.


Click the  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 underneath.

SMS Export Framework

The SMS export framework allows the transfer of data via an SMS.


  • 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 the  button to check and preview.

Click the  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 of the EDI Catagory. 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 name 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,

Click the  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 of 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 is 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 the  button to check and preview.

Click the  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

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. This article will go into further detail and show you how to split up Invoice export information. 
This requires the Finance Module
1. Navigate to (EDI > Data Export > Invoices). This section is used to export Invoice information from TransVirtual to an Accounts Package such as MYOB or Xero.
 
The rules listed here are the Invoice Export Rules you currently have setup. Those coloured white are active/enabled. Those coloured orange are not yet active, and won't send any export data. To enable any rules, click the Enable Export toggle you see appear-after clicking on the rule you wish to enable. Make sure you click 'Update' after enabling this toggle. 



2. You will need to setup (if you don't already have one) an Invoice EDI that is used to detail how Invoice information will send to your Accounts Package.


3. Once you have your Accounts package Invoice EDI setup, click on the Edit Export Rule Helpers button you see appear. This button allows you to create rules that will detail how Invoice information to Xero will be sent across; so you could split this up based on whether prices are levies, taxes, etc.  
 

4. You will then see this screen
 
Click New Rule. 

5. Choose a name for this rule
 
The name for this should be something related to how you will split up Invoice prices. So, in the above example, this rule will be for Fuel Levies.

6. Once the rule has been created, you will need to setup/configure the settings for this rule.
 
For the Price Value section you need to select what the prices for this rule will be classified as; so in this example, these prices will be Fuel Levies-so you need to select 'Levies' from the drop-down list. Note: The drop-down list for Price Value contains the different types of prices found in TransVirtual. When selecting one of these, you are saying that the prices for this rule is a levy or a tax, etc. in TransVirtual.
You will also need to select the appropriate Accounts Package from the list; we are using Xero in this example. Ensure that this rule is enabled using the toggle-slide to YES and click Update.

7. Once you have these details setup, you need to also select what Account code and/or Job Code this price will be attached to in Xero. Click the Link Account Code button or Link Job Code and select the appropriate one. 


8. This is now good to go. This rule will now split up Fuel Levies and attach them to the Account Code and/or Job Code you have specified in Xero. 
Not having these Invoice Export Rules means that the Invoice data will send across in one amount, rather than the information being split up.

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.

    • Related Articles

    • Re-send/Rediscover Exports

      Re-send vs Rediscover  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 ...

    • Data Imports

      Introduction 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 Remote FTP TransVirtual Hosted FTP Server TransVirtual Email ...

    • Data Filter

      Data Filters The Data filter is used extensively throughout the application. This article will provide an insight in to how it works and how you can use it in your business. Data filters help you filter data based off one or more rules within a ...

    • Data Management - Executive Summary

      Data Management - Executive Summary TransVirtual data management is continually reviewed to ensure that the information we collect via TransVirtual is as accurate and high quality as possible. Subsequent storage must maintain robust security and ...

    • In-Line Data Filter

      In-Line Data Filter Within Transvirtual when you have a table of consignments, invoices or Manifests etc you will find under the column headings a filter box. The below assists with some advanced features of these filters. Step by Step Guide Under ...