Multi-company Batch Processing

It is rather annoying that you have to run a special client to do batch processing and that you have to ensure that the client is always running, however even more annoying would be if you would have to run a client for every single company, which is what seems to be needed when you look at the standard system. Luckily however there is a no-modification-needed approach to avoid having to run multiple batch clients for multiple companies:
  • Create a new Virtual company e.g. BAT (Administration>>Setup>>Virtual company accounts)
  • On the Company accounts tab, add all companies to the Virtual company.
  • On the Table collections tab, pick the Batch table collection, which is available as a standard table collection.

That’s it. Easy, isn’t it?

Data import options

There are multiple options to import data. My favorite one when I still "owned" an installation and could take full responsibility was to import directly into the database using and updating the record-id stored in SYSTEMSEQUENCES. Now that I am consultant an can not take unreasonable risks I had to find a new way of efficiently importing data. The standard options are:
  • Excel:
    Populating the Excel template is great, you have predefined drop-down lists for enum values. You can use all the tricks available in excel to calculate fields in order to clean you data. However excel has two significant disadvantages: Importing from Excel is sloooooooow. And tables that are linked via the Record-Id (e.g. Address, Notes) can not be imported.
  • CSV:
    CSV import on the other hand is the exact opposite: importing e.g. thousands of customers takes only a few seconds, while the Excel import takes hours. The record-id field is available for auxiliary table such as Address and Notes, and the relationship will remain even though the record-ids might be re-mapped during import. But again CSV has significant disadvantages: Enum values are just 0,1,2,… you fist have to find the mapping of these values. Editing CSV tables is not easy as Excel does not save to quotation marks. Line breaks as they might appear in the address field can no be imported.

My idea was to combine the two methodologies and get a new variant with all the advantages – the remaining disadvantage is that it takes longer to set up:

  1. Create both an Excel and a CSV template (export both with a couple of records so that you have examples to work with)
  2. In the the Excel template create new worksheets for each table in which you will prepare the csv data file.
  3. Copy the sample CSV for the dat rows into the new worksheets. Use the menu Data>>Text to columns to convert the csv line (use Delimiter comma and text qualifier {none})
  4. Add another line and mark each column with on of the following data types: STRING, LIST, NUM, DATE
  5. The first five and the last columns contain system fields:
    • The first column always contains "RECORD"
    • The second column always contains the Table-Id. Copy the table Id you found in the exported sample record.
    • The third column contains the record-id which must be unique and can be simply calculated 1,2,3,4,… by using something like = C4+1 for cell C5.
    • The fourth and fifth columns are unused and contain 0
    • The last column contains the record version witch can be set to 1 for all records.
  6. All other fields should contain the following formula =IF(F$1="DATE",TEXT(‘SourceWS’!A7,"yyyy/mm/dd"),IF(F$1="NUM",’SourceWS’!A7,IF(F$1="LIST",VALUE(LEFT(‘SourceWS’!A7,3)),"""" & ‘SourceWS’!A7 & """"))), whereas of course the SourceWS is the original Excel template WS which you have populate and which provides you will all advantages described above. Once you have linked the first field you can copy the formula throughout the csv worksheet. Note that the source data should not contain any line break, but the line break should be replaced by some tag e.g. "-newline-".
  7. Now for creating the csv: copy past all the relevant data into a text editor. You now have tab separated value.
  8. Replace the tabs by commas. Note: Notepad is frighteningly inefficient for doing the simple task. Use a more sophisticated text editor such as Textpad and you will have replaced all tabs within seconds.
  9. Save the generated csv as dat file.
  10. Import
  11. Write a job to replace the -newline- tag:
    static void AddNewLine(Args _args)
    {
        //only modify the following constants
        #define.runCustTable(false)
        #define.runVendTable(false)
        #define.runInventTable(false)
        #define.runAddress(True)
        #define.pattern("-newline-")
        //only modify the constants above
        CustTable custTable;
        VendTable vendTable;
        Address address;
        InventTable inventTable;
        int64 oldCount;
        int i;
        ;
        ttsbegin;
        if (#runCustTable)
        {
            While select forupdate custtable where custtable.Address like "*" + #pattern + "*"
                                                || custtable.Street like "*" + #pattern + "*"
            {
                custTable.Address = strreplace(custTable.Address,#pattern,"\n");
                custTable.Street = strreplace(custTable.Street,#pattern,"\n");
                custTable.update();
                i++;
            }
            info(strfmt("%1 CustTable records updated",i));
            i=0;
        }
        if (#runVendTable)
        {
            While select forupdate vendTable where vendTable.Address like "*" + #pattern + "*"
                                                || vendTable.Street like "*" + #pattern + "*"
            {
                vendTable.Address = strreplace(vendTable.Address,#pattern,"\n");
                vendTable.Street = strreplace(vendTable.Street,#pattern,"\n");
                vendTable.update();
                i++;
            }
            info(strfmt("%1 VendTable records updated",i));
            i=0;
        }
        if (#runAddress)
        {
            select count(recid) from address;
            oldCount = address.RecId;
            delete_from address where !address.AddrRecId;
            select count(recid) from address;
            if (oldCount != address.RecId)
                info(strfmt("%1 Address records deleted", oldCount – address.RecId));
            While select forupdate address where address.Address like "*" + #pattern + "*"
                                                || address.Street like "*" + #pattern + "*"
            {
                address.Address = strreplace(address.Address,#pattern,"\n");
                address.Street = strreplace(address.Street,#pattern,"\n");
                address.update();
                i++;
            }
            info(strfmt("%1 Address records updated",i));
            i=0;
        }
        if (#runInventTable)
        {
            While select forupdate inventTable where inventTable.ItemName like "*" + #pattern + "*"
            {
                inventTable.ItemName = strreplace(inventTable.ItemName,#pattern,"\n");
                inventTable.update();
                i++;
            }
            info(strfmt("%1 InventTable records updated",i));
            i=0;
        }
        ttscommit;
        info("complete");
    }