Overview on importing data into Solomon IV

There is a standard feature that has been in Solomon for many years that many people are unfamiliar with.  This feature allows the ability to import external data into just about every Solomon data entry screen.

We have many clients that use this capability has part of the annual budget process, or processing repetitive data from an external system which is not directly connected to Solomon (i.e. cash register, food server systems etc.)

This article will provide a high level discussion of how the process is done.  Under the utility menu in Solomon are two menu choices: Control Macro Generator and Transaction Import. 

Step #1:

An import process always starts with a valid control macro being created.   This feature, if you think about it, is a great example of the flexibility of the Solomon system.  Any user, who owns the customization manager module, has the ability to completely modify a data entry screen (i.e. change the tab order, hide unnecessary fields, add in custom hidden fields etc.)  Consequently, it is likely that two different customers might have completely different screens for say, the Journal transaction screen.  Cleverly, the control macro generator solves this dilemma.  When you run this screen, it records the uniqueness of the data entry screen for each of these custom settings.

This is the view you get when you first select the Utility -à Generate Control Macro

Typically when I use this screen I first build an intelligent macro, then I come back a build a simple macro.   Using the F3 key on the screen no field, select the screen you want to do data entry to.  Select in the second field, Control File Type, either Intelligent or simple.  In the third field, control file name, create a name for the unique macro.  Typically I use the convention, screened_4letters + Int. clt or screened_4letters + Sim. Clt  For example, 0125int.ctl or 0125sim.ctl would be the intelligent or the simple macro for the budget screen 01.25.00

When you click the OK button, you can literally see Solomon opening up the target screen and reading the fields, tab order and recording the macro.  You can view and print the macro created by clicking on the view macro button.

Here are two examples of create macro files  (note :  I recommend you never use a word processor program to open these macro files, rather use a text editor program like notepad.exe)

Simple macro: 

Batch,cbatnbrb,cperpost,cbatchandling,cdrtot,cctrltot,cLotSerFxdVal,cLotSerNumVal

Document,crcptnbrh,crcptdate,crcpttype,cCreateVOAD,cdfltfrompo,cponbr,crcptctrlqty,crcptctrlamt

Detail,cpolineref,cpurchtype,cinvtid,csiteid,cqty,cunitdescr,cwhseloc,cunitcost,cextcost,cunitweight,cextweight,corigrcptnbr,corigrcptdate,cacct,csub,cvouchstage,cvenditemid,ctrandesc,cpooriginal

SalesOrder,csonbr,csolineref,cSOSchedRef,cqtyrcvd07

Duplicates,crefnbr,cvendid,cdoctype,cinvcnbr,cinvcdate,cterms,cdiscbal

LotSer,LS_cLotSerNbr,LS_cQty,LS_cMfgrLotSerNbr,cls_expiredate,LS_cShipContCode,LS_cLotSerRef,LS_cLineNbr

Intelligent macro:

'$include: "bsl.dh"

'04010 Control Macro

Sub ProcessImportLine( LevelNumber%, Retval% )

  select Case LevelNumber

    case TI_Start

      call AliasConstant( "Level0", "Batch" )

      call AliasConstant( "Level1", "Document" )

      call AliasConstant( "Level3", "Detail" )

      call AliasConstant( "Level4", "SalesOrder" )

      call AliasConstant( "Level5", "Duplicates" )

      call AliasConstant( "Level6", "LotSer" )

    case 0 ' 0 of 6

    'Level 0 if of Type N

      ' cbatnbrb is a key field for level 0

      ' Field mask is UUUUUU

      serr = SetObjectValue( "cbatnbrb", ImportField(1) )

      ' cperpost is a Period Field

      ' NOTE  -- This field is a required field

      serr = SetObjectValue( "cperpost", ImportField(2) )

'Currently Disabled cstatus

      ' cbatchandling is an Combo Box

      ' Values are   H;Hold,B;Release Later,R;Release Now,N;No Action

      serr = SetObjectValue( "cbatchandling", ImportField(3) )

      serr = SetObjectValue( "cdrtot", ImportField(4) )

'Currently Disabled cbatchqtytot

      serr = SetObjectValue( "cctrltot", ImportField(5) )

'Currently Disabled ccrtot

'Currently Disabled cpodtsqtytot

      ' Field mask is WWWWWWWWWWWW

      serr = SetObjectValue( "cLotSerFxdVal", ImportField(6) )

Here is more info on the simple control macro:

Simple data macro form files contain:

·       The data's destination screen in Solomon IV.

·       The type of transaction: change, insertion, or deletion.

·       Which information serves as key fields.

·       The actual data.

·       Comments.

Simple form data files have some limitations:

·       The comma (,) is the only supported field separator.

·       The double quote (") is the only supported field delimiter.

·       The first field in a line of data must contain the key fields for the level.

·       The arrangement of fields in the data file must match that of the object names in the simple form control file.

·       Limited multiple language support. Words from a different language cannot substitute for the words Change, Delete, Insert, and Comment.

In other words, take a look at any target data entry screen. Note the order the fields are presented. Note the tab order, as you move from field to field.  The simple control macro duplicates this basic flow, with actual data entry validation for each field.

Intelligent form data files support all the features of simple form data files, plus several others for greater flexibility:

·       Fields can be arranged in any order.

·       Files have greater self-documenting capabilities.

·       Other characters can substitute for the default separator, delimiter, and comment characters.

·       Words from multiple languages can substitute for the words Change, Delete, Insert, and Comment.

In both the simple and the intelligent macros there is the concept of Levels.   In this example above, there are actually  six potential levels for data entry.  Batch, Document, Detail, Sales order, Duplicates, LotSer.  If you study a typical data entry screen we can see the respective area/ fields related to the batch, document info, detail lines etc.  It is the key action word which instructs the import process as to what type of data is being imported.

Step #2: Translate import data into the required structure.

I usually rely on the simple macro at this point.  I need to organize all the data fields so they line up with the proper action word (i.e. batch, document, detail etc.).   Depending on how the data can is arranged, this can be quite a chore.  Normally I arrange the data in an excel worksheet, then use the file save as feature to change the file type to csv (comma separated values).

As a plug for one of our developers, Kevin Hartman, we have great experience is automating this import file.  Kevin has written several vba program which take the un-structured input files, and re organizes the data in new output files which match the required layout.  Furthermore, Kevin typically modifies the Solomon menu structure so that these conversion programs that he has written are executed from with Solomon, through normal menu choices.

Here is an example of an import file for the shipper screen in order management module:

ORDER,0060,S0009834

"LINES,change",00001,5

"LINES,change",00002,10

Step #3: Transaction Import

The last step is to import the data.  This is done via the Transaction Import screen, which is accessed off the Utility menu.

When you open up this screen, this is what it looks like:

Here is an example of a completed screen:

The definition of the various columns is:

· Data file name:  location of the csv file for import (note: import data always a comma separated values text file)

· Data File Type: always ASCII

· Screen:  Target screen for data to be imported into Solomon (access with the F3 key)

· Control File Name:  Using the F3 key, search for the control macro created in step 1.   I usually use the intelligent version of the control macro, but the simple control macro should also work.

· Output Log file name;   this is a file which I typically create ahead of time to capture the log activity for messages created during transaction import.  Typically this is just a plain text file; created from the notepad.exe application.

· Run Minimized:  I recommend you uncheck this box.  In this unchecked condition, you can see the target import screen during the importation process.

After definite the fields for doing the transaction import, I usually click on the options button in the bottom right corner.   This form will then be displayed:

I typically change two default items on the above form.  First I set the import processing radio button to edit only.  Next I change the value for the “discontinue after X errors” to the number 2.

These two changes, in my opinion, allow me to verify that there are no errors when processing the entire import screen, and if there are any errors, discontinue after the first two errors.  As a side note, some of our consultants at Systematic Solutions believe that it is better to have a very high number for the number of errors(i.e. 100), and to process the entire for mistakes. 

Click ok to close out of this screen and back to the main transaction import screen. 

Start the import process in an edit mode by clicking on the Begin Processing button.

Next we need to determine if there were any errors in the edit only import batch.   This is done by clicking on the Edit errors button, which displays this screen:

The bottom section of this screen, displays the various detail lines for the data import file.  The top section of the screen displays processing messages which reading in data.   In the top section, I always scroll down to verify if there were any errors noted.   In the example above we can see the number of errors detected was 3, not zero.  

Because we have errors shown, I will in the top screen, scroll back to the top of the processing screen and search for the word ‘error’.  There are processing notes which are your clues as to what is wrong with the data import file.   Given these error message clues, I go through the re iterate process of cleaning up the errors and rerunning the import process until there are no errors reported.

When I have a clean import file with zero errors reported, then I am ready to do the final import. The parameters in the Edit Options screen now need to be changed to:

With these new settings, when we process the import file, the will be two passes.  The first pass will read all the data file and verify that no errors were found, then a second pass will occur which will update the imported data into Solomon in either a “add a new record mode” or “change an existing record” mode.

Because the actual transaction import process uses the standard data entry screens and all the program validation logic, you are getting the benefit of clean data import.   Coming through the back door to import data directly into the Solomon data files is always dangerous because of the chances of introducing errors.  Using this technique of transaction import maintains great data integrity for your accounting database.

If you would like assistance to automate any of your routine transaction import processes, give us a call or send us an email.