Sage 500 2023 - Product Update 1

Well, you are actually working in View/Edit Vouchers, not Enter Vouchers, and Data Porter generally does not allow you to import data that is going to edit an existing entry. The only way I've done something like this in the past is by coding a custom solution.

Also, you have added a custom control to the form, which is different from a UDF you can enable in Sage 500, so you would also need to update the Data Porter form metadata.

If you need to update the voucher data after-the-fact, you will probably need to find a different method.

Hello,

Thank you very much for your quick response, you're absolutely right. What we need is to modify the Data Porter for Enter Vouchers, so that it includes a custom field from a customized table.

When you mentioned metadata, were you referring to updating the tdpControl table? I tried adding the field there, but unfortunately, it didn’t work as expected. I was following this reference DataPorter and Metadata but I may be missing a step.

Could you point me to where I can find more detailed guidance or documentation on how to configure Data Porter to recognize a custom field from a custom table?

Thanks again for your help!

Best regards,
Maru

Data Porter (DP) is primarily a data entry procedure.  It does not update existing records nor accept data behind the buttons.  If you want to test Data Porter, please try processing one transaction with all the data you intend to enter.   If the data you added is included in the Excel upon export, then the data can be imported; otherwise, it needs to be processed separately in the front end.

Procedure:

1. Open Sage 500 and create a batch
2. Enter all the data on the screen as your sample transaction.
3. Click the DP Excel icon to display the excel sheet.
4. Click the "Add-ins" button in Excel, then select the "Data Porter: Map Controls" icon (graph) to display all the columns.
5. Click the "Data Porter: Export Data" icon (camera) to populate the excel sheet using the sample data you entered.

Note: If the data you enter is importable, it will be included in the Excel sheet as a sample transaction. Otherwise, the data is not importable.

You probably need to examine your approach to using Data Porter as opposed to Data Import, flat file import, or a custom import. The way to overcome some of the limitations in Data Porter can be accomplished using a creative combination of Customizer along with Data Porter. Depending on the amount of time you want to spend on the project, you could automate just about everything on the form, including the population of modal forms that are normally only accessible behind a button. It might just be easier to use one of the import APIs for that though, or just use DI.

As for the Data Porter metadata, it contains some standard and logical definitions used by the different interfaces to indicate where to place the fields on the Excel spreadsheet, as well as how the form controls map to those fields. The control collection is dependent on the form you are using and the placement and base logic for how those controls are mapped to the Excel spreadsheet is contained in the metadata.

The metadata definition starts with CtrlTabIndex column, and is displayed for reference only. This shows the tab order of the controls on the form. The WksType indicates where the control is to be placed in the spreadsheet. If it is on the Main worksheet, then WksType = 1, if it is on Detail then WksType = 2, while WksType = 0 places it in Other. The Other worksheet consists of form controls that are generally not used to pump data into the form or pull data out of it (import/export).

The CtrlTypeText column is null, or represents the MainKey, or ButtonCommit value. The MainKey is used for key values on the Main worksheet and is usually duplicated on the Detail worksheet to identify the parent ID for the detail. ButtonCommit on the Detail worksheet identifies the OK Button to save the line detail to the grid.

The SubTabName, Tabname, SubTabPageNo and TabPageNo columns identify the Tab Control and Tab Page number on which the control is located. If the data flow requires switching between tab pages, every first control on a tab page needs these columns to contain reference values.

Thank you so much for the detailed explanation and recommendations. I really appreciate the insight into how Data Porter works and the potential alternatives.

I’ll review this with our stakeholders and developers to determine the best approach for our needs. Thanks again for your help!