Sage 100

I've been fighting with a VI import into MAS200 4.50.3 SQL AP Invoice Header (upgrade from 3.71) that is creating blank batches. Short story - job reads from a SQL database that has thousands of AP invoice records/batch numbers in it and is added to every day but not purged of imported records but once every 2 weeks or so until invoices are approved by mgmt. Client wants to import in only the latest batch number of header/line records, specific to the batch numbers specified at run-time. I have on the Select tab where Batch No > Run Time Prompt, just as there was in 3.71, but the job imports blank batches until it reaches a batch number that matches my critieria and then import the invoice records along with the Batch No. Hope that makes sense...is there Perform Logic I could use for Batch No, perhaps? Or maybe someone else has encountered this that knows of a trick to fool the job?

This is my take - others may have another point of view. Creating Perform Logic (PL) is not necessarily straightforward depending on what you're trying to do. It was in the duplicate A/P Invoice checking case but for this I would vote against it because it's more involved and requires a fair amount of debugging. To answer your question you could probably develop PL to compare BatchNo on the After Read event then figure out how skip to the next line in source file if it doesn't match (assuming header is NOT separate from line). But it's not straightforward. There are all kinds of squirrelly things it does with batches and you end up needing several pieces of PL for several events. I know this from this 1 import I did involving importing into Shipping Data Entry batches. The AP Invoice Dupe checking was a single event with no squirrels. Sorry to put a damper on your hope for a quick and dirty solution. Here are some ideas: -------------------------------------------------------- 0. Create a UDF on AP Invoice Header as a BatchNo shadow field. What is currently called the BatchNo in the source table you can reference that column for the UDF on the Data tab of your import. Then filter your import selection on the UDF via a Run-Assign operation or maybe the Select tab with a with a Run Time Prompt. Basically when they run the import they would enter the BatchNo where see the UDF prompt at the . For the BatchNo field in Data tab just Replace it - no Run-Time stuff. Then test it all see if this gets you anywhere. The point here is you are using something more ""normal"" for filtering selection instead of the squirrelly BatchNo. If it almost works, what about the thought of turning off Batch Entry altogether and use the UDF alone. 1. Use the BOI to filter your table. Your BOI script can use an InputBox to ask for the BatchNo then connect up to the SQL, read through this table they rarely purge, and export only records belonging to the BatchNo to either another table or a flat file. Then last step is to launch the V/I job in AutoRun mode. 2. Use the BOI instead of V/I entirely: Same as above but last step bypasses V/I. You write A/P invoices directly through the business object. 3. Use SQL tools to create a whittle down version of another table or flat file: I once created a VB Script that ran off MAS My Tasks menu. It asked for a UserID through an InputBox, then connected to SQL. It used the UserID to pull specific picked sales orders from a SQL WMS system and then exported that to a CSV file (but it could've been another table). Last step was launch the V/I job in AutoRun mode. If this is not your cup of tea, see if client's IT will take it up since it's all SQL stuff. 4. Use Access to to create a whittled down version of the table to a flat file. A number of people prefer Access as their tool of choice. -------------------------------------------------------- One more thing. In your particular case, you have the option of getting your pvx development team to quote the PL needed and compare it with price of the external process.

Does VI job have ""Skip Over Existing Records"" selected? Is the BatchNo above or below the InvoiceNo on Data tab? Instead of using the Select tab, can use the run-time prompt as a conditional for Batchno?

I am assuming you are using an odbc data source. Can you modify your SQL query in VI to only pull records not imported before or better limit the query to only records that need to be imported. Along with that you could add perform logic to write back to your sql table that the records has been imported upon job completion.

Mark - yes, I have the ""Skip"" selected, and BatchNo stuffed into the fields below InvoiceNo. The problem with using the Run-Time as part of the BatchNo assignment is that I don't want to assign it myself, I want to pull/select any batch ID's from the source database that are greater than the batch I designate. But thank you - I went around that way too before I realized it would not work. And Alnoor, I haven't had enough coffee to read your whole post yet, but I'll get to it soon :P

@AmberStoneburg Sorry, I didn't mean to assign the BatchNo with run-time. I meant create 2 temp fields, Temp01 for the Run-time batch number and Temp02 to read the batchno from source file. Then, define Batchno field as Calculated (Temp02), but also establish a condition of Temp01=>Temp02. Hopefully, this will skip the batchno if condition is not met (instead of creating a blank batch). Don't know if it'll work, but is easy enough to test.

I would just use filemaker or Access to read the file, check for dups against the existing AP Invoices , clean it up and then 'push' it into VI unattended

Thank you everyone - I've done some more testing with everyone's suggestions this morning, and am still not having much luck...where one side might be fixed, another breaks. I have supplied the client with the suggestions of either having development write some PL for the job or have the SQL administrators work with me to limit the records that can be read (i.e. purging imported records more often or flagging previously-imported records). Not sure why this ended up being so difficult, but can't really spend that much more time on it.