DataPorter and Metadata

2 minute read time.

DataPorter is based on moving data between spreadsheets and forms. It prepares a spreadsheet to receive data. Data is either copied or entered in the spreadsheet. It then takes the rows on the spreadsheet and imports them back to the form. DataPorter is invoked by pressing the “Alt-F7” key combination. This will launch Excel and create an Office Toolbar.

Sage 500 Metadata is stored in tdpControl table. The TaskID and FormName are gathered and defaulted when the task is launched. These are the keys to the table along with CtrlName which is the name of the control on the form. The "WksType" column is used to indicate whether the control is to be placed on the Main worksheet (WksType = 1) or the Detail worksheet (WksType = 2). Leaving the default value (WksType = 0) will place the control on the "Other" worksheet. This worksheet contains controls that are located on the form, but are not used to drive data into Sage 500.

The "CtrlTypeText" column may contain Null (blank), "MainKey" or "ButtonCommit" values. "MainKey" will be used to identify controls only on the Main worksheet that are keys to the form. These controls will also be placed on the Detail worksheet, if applicable, to identify the parent of the children. ButtonCommit will be used on the Detail worksheet to identify the OK Button to submit the detail line. For example for the Invoice Entry form (TaskID=84219765, FormName=frmInvcEntry) MainKey are txtInvcID (invoice number) and cboInvcType (either invoice or credit memo or debit memo) are on the Main tab (WksType=1), and ButtonCommit is on the detail tab (WksType=2). Below are only the three mentioned records for invoice form in tdpControl table:


In Sage 500 DataPorter Metadata is invoked by the pressing the “Alt-F8” key combination while a form has focus. This will launch the maintenance form (in this case when launched from Enter Invoices task) displayed in the following screen:

The SubTabName, Tabname, SubTabPageNo and TabPageNo columns are used to identify the Tab Control and Tab Page number that the control is located on. 

In order to find out the form name, tab name, task ID, and control name on the form you can launch Customizer on the form and by clicking (placing focus) on any field the system will automatically display control name, see below for the item ID field: