Sage 100

I have a customer. They manage 20-30 investment funds. Each are separate standalone MAS90 entities. They want to pull in an adjusting entry automatically each week on Friday. This would have to happen for each of the 20-30 standalone MAS companies. What they'd like to do is: 1. Have this automatically import a separate entry to each of the 20-30 entries 2. Without the user having to do anything (assume VI and Windows scheduler). A couple things that I've not had to deal with before: 1. Using SQL as the source (I always use CSV because I perceive Sage's track record at supporting other file formats as poor). Is there a specific version SQL that VI is certified as being able to read? I've had really poor results when customers tried to use Lotus or Excel files as sources and picture my luck not being much different with SQL. 2. The customer wants to be queried for a data range of transactions to pull in. I'm uncertain this can be unattended. I also think there's a huge potential for mistaken double postings. QUESTIONS: 1. Is this easier to do with scripting? Or maybe I should ask can it be done into 20-30 separate companies unattended? 2. Anyone devise a similar VI type routine that might fire for automatically for this # of companies - and are there hidden ""gotcha's""? 3. What's the wisdom of using SQL as the VI source data file? I tend to avoid anything except CSV.

It depends on their SQL DB. I have a client that runs the query from the import. It updates a flag field in SQL called exported. I've never had an issue importing from SQL.

You could have a script that runs a command substituting in the company code and runs the VI job 20-30 times. Or, use BOI scripting.

VI has the advantage it is easier to support (if you don't know scripting or BOI). BOI has the advantage of flexibiliy. Most auto GL imports I uses scripting. They seam to run better than trying to call VI jobs in a batch file. The GL import into GL Entry is pretty bullet proof.

@RobertWood are you flagging the ""imported?"" field in the SQL db from within VI or does the client run it from their SQL system? The client is pretty vague on the exact details however it seems certain they'd want to: a. Import all 20-30 companies at onc b. Not have someone push the ""accept"" button or enter parameters 20-30 times c. Have it run automatically via some type of scheduler Without some type of double check I see them importing twice by mistake (of course the client swears they'll develop a procedure so that doesn't occur). I suggested to them that perhaps a button on the journal or transaction entry page -- but they are pretty insistent that this all happens without user intervention despite my being sure that there's going to be some measure of user error introduced.

We're using an update query as the source. The client is running the vi job from a .BAT file.

I've previously used a VI routine for something like this.

Customer is Landmark Capital in Avon - they're already shopping me