Sage 100

Wondering if anyone knows of some simple perform logic or a calculation that would allow me to read and validate the AP Invoice Number prior to importing in through AP Invoice Data Entry. I need to be able to recognize during import if the invoice number already exists in the Open Invoice file, and if so, flag the new record to be imported as ""Adjustment"". I've seen the post on here long ago about how to skip duplicate AP invoices, but nothing for how to bring them in. Thanks in advance for any tips!

That's been discussed previously and some suggestions were provided. I'm locating it and will post shortly.

I believe VI will assume the record is an adjustment if it already exists in the database.

https://90minds-com.socialcast.com/messages/10804012 https://90minds-com.socialcast.com/messages/5664955 Please let us know how you make out, because I'll likely need to do this for a client of mine.

Oh wait, I guess I didn't read your entire post. Sorry. What version?

I didn't state that - sorry. It's 4.50. Thanks for locating those posts! Not sure what I was missing in my searching.

Well, one of those posts discusses how to prevent duplicates from coming in (I want them to come in but show as adjustment) and the other link doesn't go anywhere. Let me try to take out that field altogether instead of assigning the values with temp fields and see what happens.

Also check Bernie's comment that perhaps the duplicates numbers are automatically adjustments. Please update us as I think as Brett indicated this is something that we could run into from time to time.

The duplicates will come in as duplicates as Bernie stated.

Attached are screenshots of the VI job log and DFDM after seeing if the invoice job would assign the Adjustment flag on its own. Even though the log does recognize it as existing, it still can't assign ""Y"" correctly.

You are correct. It won't change the flag. Once you post them, they'll show up as adjustments. I'd suggest copying the company and posting in the test company.

Unless I'm missing something, they are not posted as adjustments unless you tell the VI job to do so. Attached is a sample of the Invoice Register that does not show the ""*ADJUST*"" as the client needs, and the DFDM hisotry record - please note the Invoice Type of ""I"" instead of ""A"".

True, but it still treats them like adjustments in the system. If you need it to show as an adjustment everywhere, Alnoor's perform logic that is in the post that Brett references could be made to work for that purpose.

Unless the almighty Alnoor is available today, does anyone else have an idea of how to create the .M4P file referenced in the previous Perform Logic resolution?

Send him an e-mail............

I probably wouldn't call him almighty when you do.

If your at 4.40 or above you could likely do this with a script also. It might even be easier for you to support if you can work with VBScript.

Couple of things here... 1) the M4P files Alnoor created are text files and will work just fine as the VI perform logic. The line between various scripting and this kind of coding overlaps considerably. 2) Alnoors perform logic AP_VISkipDuplicateInvoices.m4p and AP_VI_430_VISkipExistingInvoices.m4p both check the AP Invoice History table for existence of the AP Invoice and report failure if it exists. This is fine if that is what you need to do, but I do not think that will meet your needs here as you need to know if the invoice is in your Open Invoice table and if so, you simply want the Adjustment flag set accordingly. Try the attached - I think it will do exactly what you need here.

Thanks Randy. I implemented the changes outlined in your screenshots, but the invoices still came through with Adjustment=N. Also, a side note: the import took very long to run...I'm not sure if this is normal for perform logic looking through other transaction or history files, but thought I'd mention that it would take the client almost 15 min just to import about 60 invoices. And they have files of upwards of 300+ invoices at a time.

Not sure what could have gone wrong... Also, not sure why this logic would have caused any significant slowdown. It is not using business objects, but reading the AP OpenInvoice table directly and limiting the invoices it looks at to only those for the vendor in question. Easy enough to test though... with the perform logic removed, including the 'CALC' - how long does it take to run? As far as not working... Very odd. Ran just fine for me in 4.50 on ABC data. How about a screen shot of VI job - similar to the first two in the setup document?

I see what the problem is...I have the invoice number being calculated/assigned depending on how long it is in the source file (see attachment if you're curious). When I import directly from column 2 as is, your Perform Logic works great. But it can't seem to work after the invoice has been calculated by the temp fields. That's just my luck. But thanks for what you've put together Randy!

Quite a complicated AP invoice import you have here... It looks like quite a lot of conditional logic associated with InvoiceNo ... It is not clear to me whether or not the perform logic gets called for each variation of the InvoiceNo, but I will hazard a guess that it is. I will also guess that if the last InvoiceNo conditional is met AND and adjustment, that things might work for that particular invoice. My testing shows that this perform logic also works, at least in my simple import job, as AFTER assign as well ... Likely this might be called after your conditional logic and as such only called once. Could have an impact on processing speed as well. If you decide to give this a try, let me know how this works out... Thanks, Randy

You're a genius! Not only did changing the Perform Type to ""AFTER ASSIGN"" work for the Adjustment flag, but the speed went back to something much more reasonable...about a minute for 34 invoices instead of 7 min. I really appeciate your help!

Having used his services in the past, I can vouch that Randy knows his stuff!!!!!!!!!

@AmberStoneburg very glad this worked out for you. @JeffSchwenk when are you in my area again? My coffee cup runneth dry!

@RandyMarion I'll give you about a 30 minute advance warning to head on over to the Bob Evans the next time I drive through.