SMS Ticket Import Tool

Parent Previous Next




SMS Ticket Import Tool



Different than the feature called ticket, other data import/export (under Utilities, Database Tools, or the autoimport/export feature, is the Ticketimport tool. It is available from the executables in the installed Turbo folder called ticketimporttool.exe.  The customer must have purchased the TicketImportTool option.



The TicketImportTool is away to import .csv formatted data, for tickets, from sources other than SMSTurbo.

Many other applications utilize this file format. The data can then be manipulated from the file via mapping


To use the tool the customer must have purchased the Ticketimporttool option.


Navigate to the SMSTurbo install folder and launch the executable ticketimporttool.exe.


After being prompted to make sure you wish to use the tool, the user will see the following screen:



Select OK to continue. You will then see:



Read the Read me first! information that comes up after launching the button at the top. This file has important

information on how to format the import file and using (SQL) expressions in that file.

See Below for a printout of that readme file.


1. Create the .csv file with the data you wish to use for the tickets you wish to create.

Note, Excel documents can be saved as a .csv in the File Extension portion of the save dialog window in Excel.  

This file should contain field header labels in the first row of the .csv field. This is will assist in the mapping process below.


2. Denote the .csv format options under "CSV Options"

Note: Please contact CIS for a sample file or template file which can be used for this purpose


3. Click the Ellipsis button to choose the file created in step #1 for the “Pick Import File"


4. Check the box for "Calculate Pricing on Import" if you wish to do that after importing these tickets. The ticket will be automatically updated with the existing price structure for that ticket's Customer/Order/Material/rate.


5. Under "Step 2" Click on the button for "Read File / Map Data" in the Step 2 section. Note - if an error shows “File does not exist”, go back to step #1 above and make sure you have created the .csv file and the path is correct.  The Data Mapping screen below will appear if the .csv file is loaded successfully.


6. The SMS ticket contains 3 sections or “Table Names” It contains a “Ticket”, a Ticket Detail section,

and a Ticket UDF section.  Scroll down and view these different sections I the Table Name column.  

The table field column represents the unique field name for holding the ticket data.

These names should match up to the .csv files header values created in step #1.


7. Use the “Map To” Drop down to match the selected value to the field name in the .csv file.  If there is no field that matches the .csv file, keep the drop down at “USE DEFAULT VALUE”.


8. If using the default values, enter the value in the Default Value Column.  Be aware the format of this value is very dependent on the format requests of the field in the SMSTurbo database.


9. The Express column can be used to created formulas to populate the field values. Please see the “Read Me First” Documents for more details.


10. Click the “Done Mapping” button to complete the process.  If any fields have been missed, the program will prompt the user to fix any mappings that have missing values.  These setting will now be stored in the SMS Database for latest use.


11. Click the “Import” button at the bottom of the Step 3 section.  You may see errors related to the import. Read the error message and find what value is “expected”.   click No to the message. Go back into the mapping field and find the field that is causing the issue. Find the field and its mapped values.  Verify the data in the CSV is the correct format for that field. Verify the default value is a valid format.  Run the import again until successful.


Select "Start Over" to reset all controls on the screen to their default value.


Select "Delete Mapping" to allow the ability to re-map the data with the "Read File / Map Data" button.


NOTE: You can designate the mapping table name by adding a command line argument of TABLE=<Some Table Name> if the table isn't there it will be created. Also you can have a DB= parameter on the command line i.e DB=smsdata


Contents of the Readme file which appear when selecting the "readme" button in the application.


Requirements and rules for the import of tickets:

1. File must be COMMA,TAB,SPACE or SEMICOLON separated values or Excel format

1a. If the File is in xlsx format (Office 2010 and up) you will need to download the Access Database Runtime from this location https://www.microsoft.com/en-us/download/details.aspx?id=13255

2. File must have header row

3. The file must have a column for the ticket number and the number must be a valid number greater 0

4. The import does not support the importing of signatures, images or detail images.

5. The TKDetail record fields NUMBER, LINENUMBER, TRUCKID, CUSTOMERID and ORDERNUMBER are set for you in code and cannot be mapped

6. All IDs that are used on a ticket (except TruckID) must exist in the database before import.


Extra information on Expressions:

You can do math or manipulate a value from the source file by using the token {CSV_FIELD} in the expression column. This will be replaced by the value of the actual file row and column before being sent to SQL for evaluation. This can be done by double clicking on the expression cell that you wish to edit. This will bring up the Expression Editor.


You can also use {columnname} in an expression. Column name would be the header name of the column that you want the value for. This will replace that token with the actual value of that column for the current row.


For example, if I have a value that I want to be uppercase when imported I would choose that field in the Map To drop down and in the Expression column I would enter: select UPPER('{CSV_FIELD}')


When evaluated this would UPPERCase the string value of that field


The expression field can be SQL only with the exception of the one token mentioned above.


The order of precedence for the fields is

When USE SET DEFAULT is selected

       1. Expression

       2. Default Value

When a Field is selected in Map to

       1. Expression

       2. Mapped Value


If in either case the expression fails the #2 step will be used.


Options:

You can have either of the following two command line arguments configured

1. DB=N where N is the Database number you wish to use, this follows SMS's rules for DB=N

2. TABLE=<NAME> where <NAME> is the name of the table to save the layout to in the SQL database. This name can contain be A-Z and 0-9.