Export Data
Overview
The Export Data screen allows you to easily export to excel and update your inventory using that file.
INV>INV Advanced Tools>Export Data (formerly under INV>INV Special Functions)
Table of Contents
Creating, Editing and Refining Your Template
This section covers creating, editing, and refining your template.
Create New Template
Unless a Template doesn’t already exists, it will load one when the screen opens. You can create a new one using the New Record button
Step-by-Step
Open the Export Data screen
INV>INV Advanced Tools>Export Data
Create New Record
Hit the New Record button (Green +)
Create Template by Entering:
Template Name
Description
File Name
Hit Browse button
Select location
Name file
Edit Existing Template
When you open the screen, a template will load. If that isn’t the template you need, there are two ways to find it.
Step-by-Step
Open the Export Data screen
INV>INV Advanced Tools>Export Data
Cycle through the records to find it
Use the Blue arrows at the top
Look it up using the Template name
Hit F7 to enter Query mode
Enter the Template Name in the field
Use % to search without exact name
e.g. %ITEM% would search for any Template with ITEM in the name
Hit F8 to execute Query
Define Export Criteria
This section sets which data to pull for the export. Setting values will narrow it down, but leaving the fields blank will search for all.
If you do not select a vendor, be sure to select vendor as one of the field names in the next section, otherwise the system will be unable to distinguish vendors for an item when the file is imported.
Warehouse - We recommend using 1 warehouse at a time
Vendor
Super Cat - This may have a different label for you
Category
Class
Manufacturer
If you include warehouse or vendor specific data in the export, such as Last PO Date, or Standard Cost, you MUST select a vendor or warehouse. The export file will contain a record for each warehouse and vendor for each item included in the export. The fields needs to be there for the import to work and to know what you are changing.
Define Fields to Import
This section allows you select what will be exported.
If you include warehouse or vendor specific data in the export, such as Last PO Date, or Standard Cost, you MUST select a vendor or warehouse. The export file will contain a record for each warehouse and vendor for each item included in the export. The fields needs to be there for the import to work and to know what you are changing.
Set fields
When setting fields, make sure to hit tab or enter through the fields as you go.
Step-by-Step
Click on the Field # field
Hit tab or enter number - number autogenerates if you tab
Select Field from Dropdown
Hit Tab through the row to the next Field #
Repeat as necessary
Reordering Fields on Template
If you want to reorder the fields in the template without deleting and re-entering, you can change the field numbers.
In the following example, we are making Vendor the first field in the export. Since Field #'s must be unique, we change the field # for Vendor to .5. Then click on the Renumber Fields button. Since .5 comes before 1, Vendor gets moved to the top.
Starting Example
Renumber Vendor to Field # .5
Click on Renumber Fields and Vendor is now the first field in the template
Export and Import
Once the template is complete, you can export the data to excel, make any changes, then import that data back in.
Exporting a file out of TRX
To export the template, click on the Export Template button. You choose the directory that the file will be saved in by entering the path and name in the "file name field" from section one. It is highly advisable to make a note of where you are exporting your file to so when you leave TRX you will know where to look.
Step-by-Step
Open the Export Data screen
INV>INV Advanced Tools>Export Data
Set/Confirm File Name
Hit Browse to set location and name
Hit the “Export Inventory” button
Hit Yes to prompt “OK to export inventory data?”
Importing your file back into TRX
Once you have exported you inventory file out of TRX and made all the required adjustments you will need to import it back in and update your inventory. Return to the Export/Update Inventory screen.
Step-by-Step
Open the Export Data screen
INV>INV Advanced Tools>Export Data
Double-Click on Company name in top right-hand corner of the screen
Make sure the file is where File Name is set
Hit Browse to select your file
Check “Update On Import” on the fields you want to update
Hit “Update Inventory” button
Note: Not all fields will allow for import. Check with support if it isn’t available
If everything was done correctly, it will say it completed successfully
Verify that the fields were updated
After running the import, be sure to spot check a few items to make sure your fields updated.
Field Breakdown
This section gives more detail per field on the screen.
Detail
Section 1: File information
In this section the important file information is stored and created
Template Name - Create a name for the template that you will using, if the name already exists that template will be loaded
Note: When entering into the screen if you press the F8 key you can then use the arrow keys on your keyboard to scroll through your saved templates.
Header Record - Select if you would like to export a header row for your file. It will create a row of column labels in the text file so you know what the column is.
Description - Give Brief description of what your template is for. In this example we will be exporting warehouse information and updating the ware house locations of various items
File Name - Give the file a name be sure to include the path of the file.
Note: the .TXT extension will be added automatically
Section 2: Template execution icons
In this section the screen action icons reside
Export Template - Templates can be used in any company that is on your database. They can also be exported themselves which allows them to be created easily for use other databases. This is very useful if you are having problems with an export and TRX Integration support personnel have requested your export so they can test it.
Generate SQL - There may be times when you need to view the SQL statement that is being used to generate the output. To view the SQL, click on Generate SQL button. A text file located in the same directory as your output file will be created with the SQL statement. This file will have the same name as your output file but with an extension of .sql
Export Inventory – this icon will send the text file that you created out of TRX so that you can edit it.
Update Inventory – this icon will use the file that you have active in the File Information to update your inventory
Section 3: Define Export Criteria
This section will allow you to limit the amount of items that you export into your file. Exporting the whole database into a spreadsheet can be quite unruly and most spreadsheets can only handle a limited amount of rows that your database could far exceed.
Warehouse – Which warehouse would you like to export from
Note: We recommend using 1 Warehouse at a time.
Vendor – Defines which vendor you would like to limit your selection too.
Category – If you have categories defined you can select by them
Class – If you have any class's defined you can select by them
Manufacturer – if you have any manufacturers defined you can select by them.
Active items only – check if you would like to limit to active items.
Note: If you include warehouse or vendor specific data in the export, such as Last PO Date, or Standard Cost, and you do not specify a vendor or warehouse, the export file will contain a record for each warehouse and vendor for each item included in the export.
Note: if you do not include a vendor in the upper template portion, be sure to select vendor as one of the field names in the lower section, otherwise the system will be unable to distinguish vendors for an item when the .txt file is imported back into TRX
Section 4: Define fields to export
This is the screen that you use to create the file fields that you are exporting and ultimately going to use to update or sort your database information.
Field Numbers - correspond to the column number. Leaving them blanks as you choose the next column heading will allow you to use the "Renumber Fields" icon to auto number them. You can also assign your own numbering.
TRX Field Name - is the name of the database field that you would like to export/update. There is a pull down menu that will list out your available options. Simply pull down and choose. The pull down menu has too many options to list here but you will note the layout contains a location, then a dash, then a description. That location represents the tab on the Inventory query screen, it is there so you can find the the field you are updating.
Length – This field is only used if you are exporting a fixed length field, you should accept the default length as it will correspond to the preset database field lengths. Changing them could cause unwanted truncation or export difficulties.
Default Value - field is used to supply a value should the data in the database be blank. For example, if you choose to include the Purchasing Unit of Measure and that field is blank, you could enter 'MISSING' in the default value field. The export file would then have the word MISSING in the Purchasing Unit of Measure column. You could sort by this field and easily find all the missing Purchase unit of measures because they would be in a row.
Format Mask – This field is used primarily for date fields. A default format mask is entered automatically when you choose a field that is a date.
Update on Import check box - allows you to define which fields will be updated upon re-importing of the data