Sage 100

I'm looking for a way to import an AP Invoice file into 25+ companies without having to write 25+ VI jobs. The company ID is one the record. I'd like to also avoid switching companies and run time assign prompting too. Not asking for much!

You should be able to use similar logic as discussed in your other post https://90minds-com.socialcast.com/messages/31193875?ref=stream.

Sorry. Did not explain the problem well. What I need is Select logic that imports only those records where the company on the record matches the company being imported into.

Assuming you don't use an add on like DSD's Multi-company AP, the way I've handled this in the past is to use filemaker to loop through the companies (you could use Access), and then call VI from the command line and pass the company code as an argument. Of course you'll need DSD's multicompany Dailty Transaction Register enhancement if you don't want to have to go into 25 companies to update the GL's.

I assume this is not SQL. A query and a BOI script?

Off the top of my head I'd try a vb script to extract the rows for a company into a new import.txt (creating a blank file if there are no results to prevent locking up the VI job), then start that import, renaming the import file when done. Repeat this for each company code, within the vb script (...loop through an array of company codes?).

I was thinking something similar to @KevinMoyes ... but with on execution perform logic that reads the source import file and writes those that match the current company code out to a temporary file, possibly a memory file for processing. Couple that with Kevin's idea of running through the list of company codes from a vb script or batch file....

I like the idea of a BOI script for importing that has a variable for the company code in use. If the record matches the system company code, then import it, else skip it. Now if only I knew if it could be done......

Jeff, I'd love to be able to reference the active company but that still means logging in to 25+ companies without even not knowing if they have data in the source file to begin with.

It is not me, but it is my understanding that if you are a good scripter you can call/connect to a database load data, call/connect to the next database, etc.

If, in your VI job, you setup a temporary variable that picks up the company code in your data file (see attached) , and compares it to the current company, you can use it to set, or NOT set a critical field like the vendor division (for example). Without the division, the record will fail, and not be imported. I think the treatment is a little different in non-framework modules like Payroll. The variable used for the current company is comp$ instead of <CompanyCode>, and you also have to set the field (PR Department for example) to an invalid value. I use ZZ. Then, in the Select tab, exclude Department ZZ from being selected. To avoid multiple logins, there are various articles out there that allow you to run a VI job from a script or batch file. The command may be different for current versions, but it should be something similar to : pvxwin32.exe ..\launcher\sota.ini ..\soa\startup.m4p -arg DIRECT UIOFF USER PASSWORD ABC VIWIXX AUTO Where VIWIXX is the VI Job number, and ABC is the Company ID The simplest way would be to repeat the VI command in the batch file 25 times. while substituting the next Company ID on each new iteration off the command. An alternative might be an external file that lists the current companies. This file is then referenced in a loop from the main script to repeat the VI command.

Is it safe to assume a fairly even distribution of data records across the 25 companies? If so, the downside of forcing critical fields to fail when not for the proper company is the majority of data records will then fail. I have used this method of firing off a VI job in a batch file to loop through a set of VI jobs, but it could easily be adapted to a list of company codes instead and it works well. I still prefer the on execute perform logic method to create a temporary file with just the current company import records in it. You would end up with a batch file to execute the VI job once for each company code automatically. You could run the batch file manually or set up windows scheduler to fire it off.

Regardless of the method used, each record has to be included or rejected for each company at some point.

I think I'll create the batch file with a line for each company using the one source file, realizing that the majority of the lines will fail in each import. Thank you all for presenting options.