The workflow task in MachShip allows you to define field mappings between MachShip fields and data fields from a selected file type such as CSV or XML.
How to navigate to create a Workflow Task
To create a new workflow task, you need to access the Manage Workflow Tasks page. It is accessed via Admin > Other Options > Workflow Tasks.
Then select Workflow Task.
From this page, you can create a new workflow task by clicking on the Create Workflow Task button at the top of the screen.
Create a Workflow Task screen
In order to generate a workflow task, you will need to enter the following fields:
- Name
- Abbreviation
- Company
- Workflow Task Type
The Workflow Task Type needs to be Invoice Importer as we are creating a workflow task to import invoices into MachShip.
- Mapping Type
Since you are going to import a delimited file, please select Delimited File Mapper.
Note: Once you choose a Workflow Task Type and a Mapping Type, you will not be able to change them. If the selected type is incorrect, the only way to change it is to start the process over.
Once you have chosen the Delimited File Mapper as the Mapping Type, you will see a new section called Workflow Task Options as shown below.
For delimited file type, you can adjust several configuration variables:
- Rows To Skip
By default, it is set to 0, meaning there is no row to skip. When your file has some redundant information, for example, lines that contain the report title or author info, you can skip those lines and enter in this field the number of lines you want to skip. Note that blank lines are automatically skipped.
- Rows To Skip at End
By default, it is set to 0, so similar to Rows to skip, there is no row to skip at the end. When your file has some data at the end, for example, an extra total line, then it is time to enter in this field the number of lines you want to skip. Note that blank lines are automatically skipped.
- Contains Headers
When your file has headers, you can view each row in the file as a map instead of as an array of strings. Enabling this option helps you to access specific fields by their corresponding column name.
- Delimiter
It is an optional field and if left blank the expected delimiter is a comma (CSV file). If you have the file delimited by another field please just place the character in the box provided.
- Trim Fields
Enable this option if you want MachShip to remove redundant white space before and after the field value.
- Fail on Missing Field
When this option is on, the import process will fail if there are missing fields.
- Fail on Missing Header
If the mapping is based on the header and the file doesn't contain it, then the import will be failed if the option is turned on.
- Quote all fields
This option will place quotes around all the fields being imported into MachShip.
- Ignore Bad Data
The Ignore Bad Data option will ignore the line/field if there is bad data sent through.
- Ignore Quotes
The Ignore Quotes option will ignore and remove the quotes if they are within the delimited file.
Define Mapping:
The next step is to define all the property map options. Please click on Add Property Map Option button to add a new property/field mapping.
For each property mapping, you will need to fill in the following info.
- Location
Specify which part of the file to take the information from.
- Body: Take the information from within the file contents. It is the most common location.
- File Name: Take the information from the file name.
- Extension: Take the information from the file extension.
- File Name and Extension: Take the information from the combination of the file name and the file extension.
- Constant Value: Specify that the information is a constant value, the value is defined in the Map From column.
- Company: Take the company from the name of your current company.
- Base Company: Take the information from the name of your base company.
- User: Take the information from your user display name.
- Constant Location: Take the information from a constant location from the selected file.
- Mapping Target
Depending on your import type, there will be a list of properties in MachShip that you can map a location property to.
- Mapping From
You need to define how to read the value and map into the target. Typically for the delimited file import, it will be the header name when the file contains headers. When there are no headers, the best input is the index position of the property.
- Ignore Row if Empty
If you file will likely contain some lines or tag collection which do not relate to data you wish to import set this flag as being "Yes". MachShip will then ignore all the data related to this collection of tags or row in the file, allowing you to import the other data.
- Pattern and Replacement
Only use this option when you want to use Regular Expression to determine whether to replace the original field value with a different value. The regular expression functionality will also allow you to undertake direct replacement. For example - set the field you wish to replace in the box on the left and what you wish to replace it with, in the box on the right.
For example - the input below will replace every term of Startrack with the expected abbreviation in MachShip of STRACK for the carrier Startrack.
Mapping Targets
To successfully set up and import a CSV file as an invoice, you need to make sure the file has all the required fields. To create an invoice in MachShip, we will need the following info:
- Carrier account abbreviation
- Carrier account name
- Carrier consignment reference
- Carrier invoice id
- Charge weight
- Cubic weight
- Customer reference
- Customer reference 2
- Date
- From address
- From name
- From postcode
- From state
- From suburb
- From zone abbreviation
- Fuel percentage
- Fuel surcharge
- Is Fuel Exempt
- Invoice date
- Invoice total fuel
- Invoice total tax
- Is additional charge
- Manifest reference
- Other value
- Quantity
- Route and fuel price
- Route and Tax price
- Route price
- Sender reference
- Service abbreviation
- Service name
- Tax
- Tax percentage
- To address
- To name
- To postcode
- To state
- To suburb
- To zone abbreviation
- Total
- Volume
- Weight
The fields in bold are all required if you are importing as an invoice. However, the more information provided the easier it will be.
Examples of how to map and import Invoices from a CSV file
Example 1
See an example CSV file below.
Date,Week Ending,Invoice,Manifest,Account Number,Charge Customer,Consignment,Service,Sender,PickUp Area,Delivery Suburb,Delivery Postcode,State,Reference,Items,Measure(m3),Weight(Kgs),Charge,G.S.T,Total,FuelAdjustment,FuelAdjustmentRate,OtherFees
29/01/2019,31/01/2019,20190501,301341000000 ,123432,SCOTT TIGER FAMILY,ID1008751,[E],Voyage,SYD,BRENDALE,4500,QLD,10055317,1,0.12,160,79.92,7.99,87.91,3.52,4.61,
22/01/2019,31/01/2019,20190501,301301000000 ,123432,SCOTT TIGER FAMILY,ID1007031,[E],Blue Technologies,SYD,WAUCHOPE,2446,NSW,10054681,1,0.89,500,299.55,29.96,329.51,13.2,4.61,
30/01/2019,31/01/2019,20190501,321301000000 ,123432,SCOTT TIGER FAMILY,ID1009744,[E],Golden Road Productions,MEL,TONGARRA,2527,NSW,10054732,1,0.53,240,144.6,14.46,159.06,6.37,4.61,
31/01/2019,31/01/2019,20190501,321361000000 ,123432,SCOTT TIGER FAMILY,ID1005930,[E],Venusystems,MEL,REGENCY PARK,5010,SA,,1,0.29,120,51.5,5.15,56.65,2.27,4.61,
31/01/2019,31/01/2019,20190501,321361000000 ,123432,SCOTT TIGER FAMILY,ID1008746,[E],Pinnaclelectrics,MEL,REGENCY PARK,5010,SA,10055365,1,1.21,592,160.46,16.05,176.51,5.97,4.61,
31/01/2019,31/01/2019,20190501,321361000000 ,123432,SCOTT TIGER FAMILY,ID1002434,[E],Mermedia,MEL,ST GEORGES,5064,SA,10055394,1,0.14,60,51.5,5.15,56.65,2.27,4.61,
31/01/2019,31/01/2019,20190501,321301000000 ,123432,SCOTT TIGER FAMILY,ID1008866,[E],Fairiprises,MEL,ST PETERS,2044,NSW,10055366,1,0.29,160,77.83,7.78,85.61,3.43,4.61,
30/01/2019,31/01/2019,20190501,321321000000 ,123432,SCOTT TIGER FAMILY,ID1007671,[E],Marssys,MEL,BRIGHTON,7030,TAS,10055279,1,0.19,60,74.4,7.44,81.84,3.28,4.61,
30/01/2019,31/01/2019,20190501,321321000000 ,123432,SCOTT TIGER FAMILY,ID1000388,[E],Leopardworks,MEL,LEWISHAM,7173,TAS,10055314,1,0.05,20,74.4,7.44,81.84,3.28,4.61,
31/01/2019,31/01/2019,20190501,321321000000 ,123432,SCOTT TIGER FAMILY,ID1007218,[E],Quadcast,MEL,ST LEONARDS,7250,TAS,10055321,1,0.29,130,119.32,11.93,131.25,5.26,4.61,
The CSV file contains the following fields:
- Date
- Week Ending
- Invoice
- Manifest
- Account Number
- Charge Customer
- Consignment
- Service
- Sender
- PickUp Area
- Delivery Suburb
- Delivery Postcode
- State
- Reference
- Items
- Measure(m3)
- Weight(Kgs)
- Charge
- G.S.T
- Total
- FuelAdjustment
- FuelAdjustmentRate
- OtherFees
A valid setup would look like this:
There are a few notes with this mapping option:
- Date Format
When mapping a date field, you need to let MachShip know the date format. In this example, since the date is in Australian format, the correct value is dd/MM/yyyy, if it is an American date, then the value would be MM/dd/yyyy.
Note that this field can be left blank if the date format will vary. - Use one same field in multiple Mapping Targets
In this case, since the Total column from the CSV is only the sum of the Route Charge and the GST Tax but not include the Fuel Adjustment, this is why we have 2 records for the Total field, one is from the Total column, another is from the FuelAdjustment column. MachShip will understand the final Total will be the sum of these 2 fields.
- Use Regex to extract data from a field
The Service column from the CSV file has value as [E], we only want to assign E (without the square brackets) to the invoice field, that's why we have to use Regex in this situation.
The regex \[(.*?)\] is to find the matching pattern (string within brackets) and the result is the first string ($1) extracted from it.
Note: Should your file not have headers, you can use the index of the column to indicate to MachShip which column to obtain the data from.
Example 2
The second CSV file look similar to this one below.
3400099,2/11/2018,83860012,26/10/2018,3400000004238,MS011570984,,MELB ,3169,SYDN ,2229,Buck Security,CARINGBAH,2,0,20,35,0.14,0,16
3400099,2/11/2018,83860012,26/10/2018,3400000004228,MS015369329,,MELB ,3169,MELB ,3081,Equinetworks,HEIDELBERG WEST,8,0,80,80,0.21,0,20.55
3400099,2/11/2018,83860012,26/10/2018,3400000004216,MS015467150,,MELB ,3169,MELB ,3194,Hummingbirdustries,MENTONE,4,0,53,53,0.09,0,35.96
3400099,2/11/2018,83860012,26/10/2018,3400000004216,MS015567150,LONG LENGTH,MELB ,3169,MELB ,3194,Hummingbirdustries,MENTONE,0,0,0,0,0,20,0
3400099,2/11/2018,83860012,1/11/2018,FUEL LEVY 12.55,,,,,,,,,0,0,0,0,0,14.6,14.6
3400099,2/11/2018,83860012,1/11/2018,A.S.F.,,,,,,,,,0,0,0,0,0,4.95,4.95
This file doesn't have any header so we need to make sure we know the meaning of each value. You can check and confirm with the carrier that provides the file.
First, the Delimited Options have been set up as follow:
- Rows To Skip at End: This file has 2 total lines at the bottom and we don't want to import them as invoice lines. That's why Row to skip at the end is 2
- Contains Headers: The import file doesn't have any header line so this option is No
- Trim Fields: Notice that some field has extra space at the end (for example "MELB "), enable Trim Fields will return the string value without space at the begin and the end, in our example, we will get "MELB" instead of "MELB "
And below is how we define the mapping:
There are a few notes with this mapping option:
- Map From: As there is no header, you cannot reference the column by the header name, that's why you will use column indices, the first column will be 0 and so on
- Constant Location: We use constant location to find the Invoice total field because we know where it is located in the file, in this case, it is the second row from the bottom and the column index that has the value is 19
- Constant Value: The CSV file doesn't contain the tax amount, we know in this case the tax percentage is 10%, that's why we define the mapping for Tax percentage as shown below
Once all of the options have been populated, please click on the Create Workflow Task button and the workflow task will be created.
Comments
0 comments
Please sign in to leave a comment.