Sage 100

We have a prospect that needs to import daily journal entries from an external system into Sage 100. The issue is that the GL accounts numbers in the external system are different from the General Ledger so we need to translate the account numbers from the external system to the new system. The prospect has about 11,000 account numbers. Is there a way to use create a UDT for the translation and then reference that in VI? Any other ideas that would make this process simple? TIA.

Assume you mean without using an interim step

@WayneSchulz -That would be my preference. Since it is a daily event, I would hate to have them inport the info into Access and then export out a translated file if it can be avoided.

With that many potential transactions, you may not have a choice. How many lines on a daily basis do you have? Have VI do the translation might be dreadfully slow. My thought is to use Access. Put a menu on it to do the translation. One button which imports the data, translates it, then dumps it back out ina VI text file. You would add a UDF in the GL master, use a PTQ in Access to pull a fresh copy of the x-ref table every time. We do use intermediate Access steps and it is quite fast. You could even code the Access menu button to launch VI from a command line. Take it a step farther by adding verfication that the JE balances prior to uploading.

@JeffSchwenk in the sample they sent there are about 350 transactions. I can probably use Access if necessary, but I was hoping to eliminate that step. I will look at @MarkKotyla suggestion, but I believe that I have tried this before and VI would not allow that.

@JohnLeonard get with @AlnoorCassim. I know he's done similar stuff in the past using either a UDT or a UDF in the GL_Account file.

With Perform Logic I mean.

Have you checked to make sure the external system can't store the sage100 account in their equivalent of a UDF and feed it along with the transaction?

Thanks @RobertWood. @AlnoorCassim is that doable? @MikeFitzgerald my plan is to explore that as a first option, but I need to be able to offer alternatives if that is not possible (from previous discussions with them it seems that the external system is limited).

Are they currently using Sage 100? If so, what are they doing now? Just curious.

They are a prospect.

So if they're going to a new system, why wouldn't they just switch their chart of accounts?? They don't like what's in the external system? Can they be changed in that system?

@JohnLeonard it would work something like this: 1a) - Import into a UDT that is keyed by external system's Acct No and has MAS G/L Acct No as 2nd field. 1b) Perform Logic runs at Job start that reads through the source file, finds the external system's account in the UDT, gets the MAS Acct No next to it, then puts that in the source file. In the end, source file is rewritten for you with MAS GL Acct Numbers.

I know you said you didn't want to use an interim step but you can use Access with an automated routine to 1) read the source file, 2) convert the account numbers, 3) export a VI source file, 4) call a VI routine to import the JE. From a user perspective, it wouldn't take any more steps other than open Access. If you create a form that automatically pops up to confirm the import file and then executes the process, you would be potentially better off. Just a thought.

If the destination tables are the GL_JournalHeader and GL_JournalDetail you need to cross reference the GL Account Key not the GL account number.

The import job could use an ODBC souce from a simple query that reads the external system's stream and translates with a UDT as @AlnoorCassim described. No program logic needed.

First, thanks for all of the great ideas. - As a start i would like to try it using a UDF in the GL_Account table. I have added the UDF and then on the VI job (importing to GL_JournalHeader) I have a temp field that imports the external system account number and for the AccountKey I tried to do a file assign, but am getting ""The Account Number is required"" error. Somehow it is not making the connection to the temp field. What am i missing?

I understand your post to mean the udf you added to the GL_Account table contains the external system GL account #. Then you are importing data from a source file that is assigning the external system GL account # to a temp variable in the GL_Journal. Further, your import uses a file assign, attempting to retrieve the MAS GL account key by referencing the GL_Account table. If the above description is accurate, it s not going to work. You need to know the GL_account KEY to retrieve any field from GL_Account. You need to either 1. Specify (in your source file) the MAS GL account key associated with each MAS GL account number during the import to the GL_Journal tables. or 2. Use the Start Perform logic Alnoor suggested to pre-process the data prior to importing the source file. Adding a udf to the GL_Account file isn't going to help you as you have no method of retrieving the information without the account key. If you want to use method 1 above, add the corresponding MAS GL account key as a column on your source file that contains the external GL account number, and debit or credit amount. With those 3 or 4 fields you can import to GL_JournalHeader and GL_JournalDetail.

@DougHiggs - Thansk for the feedback on this. I will take a closer look at Alnoor's suggestion or the Access option.