Export Data

Owned by Stephen Juliano (Unlicensed)
Last updated: May 05, 2023 by Stephen Juliano
9 min read

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

  1. Open the Export Data screen

    1. INV>INV Advanced Tools>Export Data

  2. Create New Record

    1. Hit the New Record button (Green +)

       

  3. Create Template by Entering:

    1. Template Name

    2. Description

    3. File Name

      1. Hit Browse button

      2. Select location

      3. 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

  1. Open the Export Data screen

    1. INV>INV Advanced Tools>Export Data

  2. Cycle through the records to find it

    1. Use the Blue arrows at the top

     

  3. Look it up using the Template name

    1. Hit F7 to enter Query mode

    2. Enter the Template Name in the field

      1. Use % to search without exact name

        1. e.g. %ITEM% would search for any Template with ITEM in the name

    3. 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.

  1. Warehouse - We recommend using 1 warehouse at a time

  2. Vendor

  3. Super Cat - This may have a different label for you

  4. Category

  5. Class

  6. 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

  1. Click on the Field # field

  2. Hit tab or enter number - number autogenerates if you tab

  3. Select Field from Dropdown

    1.  

  4. Hit Tab through the row to the next Field #

  5. 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

  1. Open the Export Data screen

    1. INV>INV Advanced Tools>Export Data

  2. Set/Confirm File Name

    1. Hit Browse to set location and name

  3. Hit the “Export Inventory” button

    1.  

  4. Hit Yes to prompt “OK to export inventory data?”

    1.  

       

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

  1. Open the Export Data screen

    1. INV>INV Advanced Tools>Export Data

  2. Double-Click on Company name in top right-hand corner of the screen

    1.  

  3. Make sure the file is where File Name is set

    1. Hit Browse to select your file

    2.  

  4. Check “Update On Import” on the fields you want to update

    1.  

  5. Hit “Update Inventory” button

    1. Note: Not all fields will allow for import. Check with support if it isn’t available

    2.  

  6. If everything was done correctly, it will say it completed successfully

    1.  

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