Dataporter - Next Number (for ex, Customer number, Invoice number etc)

Suggested

Is it possible for DataPorter to automatically determine and assign the next available Customer Number, Invoice Number, or other sequential IDs without requiring the user to manually populate them in the import template?

For example, if I am importing five new customers and the last Customer Number in the system is 1000, could DataPorter automatically assign 1001, 1002, 1003, 1004, and 1005 during the import process, rather than requiring those values to be entered manually in the template?

  • 0
    Suggested

    I can think of some theoretical solutions, but in my mind they all require a varying level of code development against either the form or spreadsheet. The problem is that in all cases, the MainKey identifier in the spreadsheet has to contain a unique value at the time you fire the import, and with document types like an invoice, that identifier is used to link the data between the Main and Detail worksheets, so it has to be consistent. 

    Since the mapping function iterates the form controls and includes those added by Customizer (but does not include the toolbar (top) or navigation (bottom) buttons), you can feed a value to a custom control, or fire a command button from the spreadsheet. A solution might be to add one of these controls with supporting code to change the identity before or after the data is written to the database, or script it into Form New or Form Save events. You could also try changing the MainKey in the metadata to a custom control that would fire the stored procedure to get the next sequential value, then feed that to the main nav control. That might not work though since the interface could identify the data by the current value in the MainKey control/column when it attempts to write back to the spreadsheet with the import status. You could try to address that limitation within Excel by building a data connector to the database to obtain the next number, then populate the MainKey in the spreadsheet, or import the data with a specific identifier in your MainKey, then code a trigger or constraint to get the next ID and replace the identifying value(s). That ID is used by the front-end to organize the data, with the surrogate key generally used by the back-end. 

    You're working with limitations imposed by the original function of these tools. Data Porter is an extension of Data Migrator, so the original intent was to aid in the import of existing data migrated from another system where the ID of that data was previously assigned. Overall, you need to test some scenarios but you're probably looking at a custom solution that might vary in each form, since they are usually coded a bit differently to accommodate the logic specific to their data requirements. 

    If you have ongoing import needs, there is also Data Import Manager, which is based on an entire set of APIs and infrastructure that supports the creation of new entities and documents. The data import portion of that infrastructure is available in the database, represented by the staging tables (begin with Stg) with the import and business logic validation in various stored procedures (usually containing API in the name). You could use the data import interfaces included with the module to map, transform and import data, or you could build your own wrapper to handle the data transform and import functions. That's something else you might research if that matches your requirements.