Create Custom DataPorter Templates in Sage 500 ERP

Creating your own DataPorter Custom templates in Sage 500

If you’re using DataPorter on a regular basis you probably already take advantage of making a DataPorter template. Templates make your life easier because they are formatted with only the columns you need for your import. This is especially helpful if you do the same import on a regular basis. If you find yourself either deleting columns each time you use DataPorter or you can’t seem to map a custom field that has been added to your Sage 500 task form then here are some tips to help you understand the fields in DataPorter.

Sage 500 Controls and Data

Sage 500 DataPorter uses rows 6 – 10 as part of its controls (shown highlighted in yellow below).  The columns within the yellow fields can be rearranged but they have to stay on their respective row. Learn more about rearranging below.  Rows 1 – 5 can be used to add instructions for the user (shown in grey below). Rows 11 and greater are used for your data entry.

Column B is used for the Import Status.  If an X is placed in column B in the data entry area, it will skip over the record. The system will automatically place an X on data that has been successfully imported.

TabCtrl:TabNo represents the tab from your form, such as the Header, Detail, or Totals tab in the image above. The first tab on the Sage 500 form (ex. Header) is always represented as “0” on the TabCtrl TabNo. The second tab on the Sage form (ex. Detail) is represented as “1” on the TabCtrl TabNo, and so on.  Tabs must be entered in ascending tab order from left to right.

Example: Column F = tabInvcEntry:0 is the Header tab of the form and includes 2 data fields, Bill to and Invoice Date.  Column H = tabInvcEntry:1 is the Detail tab of the form and includes the Item plus any additional fields to the right of column H until it reaches the end of the file or encounters a new tab number.

Note: Make sure that all required fields have a data column mapped to the template and are placed in a column within the correct tab.

SubTabCrtl:TabNo in Sage 500

Similar to the TabCtrl:TabNo, the SubTabsCtrl:TabNo are those sub-tabs found within a tab. They function the same way as TabCtrl and must be set up in a similar way.  Sub-tabs on the form below are the “project”, “account”, “freight”, “trade disc”, etc.

Control Type

MainKey : this is the Sage control for whether a record exists or not.  For example, a MainKey is a combination of the Invoice Type and the Invoice Number.  If the record already exists in Sage, it will not add the new record with the same MainKey(s). The MainKey is a validation point for each record to make sure it’s not a duplicate.

Control Caption

These are the default Control Captions as seen on the DataPorter Metadata (the other icon Alt + F8). These can be misleading at times! Remember when I mentioned you may have trouble mapping your custom fields? The Control Caption may be to blame. You must have access to customizer in order to use this trick I’m going to share with you.

1. 1. Click the customizer icon to open the Customization window.
   2. Click on the field you are trying to map to in your template.
      1. Notice there will be a black dotted frame around the field.
      2. Look for the Tab Index number for the field.
      3. Take note of the tab index number and close the window using the door icon (don’t save any save changes).
   3. Open the DataPorter Metadata window (Alt + F8). Scroll to find the tab index number in the Control Index.
   4. Find the Control Name and or Control Caption that corresponds to the tab on your template. This will be the column you use to enter the data for that field.Now you should have all the elements needed to create a DataPorter template. Remember that the order of the columns in Excel should be mimicked by the order you would naturally enter data – starting from the first tab at the top.  If you’re still stuck with the template, you may want to call us to help you troubleshoot it.