Maybe someone could shed some light as to why I wouldn't be able to simply enter a conditional expressing for InvoiceNo. where {AP_ManualCheckDetail.InvoiceNo$} = {AP_OpenInvoice.InvoiceNo$}?  

Maybe someone could shed some light as to why I wouldn't be able to simply enter a conditional expressing for InvoiceNo. where {AP_ManualCheckDetail.InvoiceNo$} = {AP_OpenInvoice.InvoiceNo$}?  

First, because AP invoices are not unique, and the same invoice # can be on multiple vendors.

Second, there is no link to the Open Invoice file when importing manual checks.

You could do something in a pre-write script for the check header table, doing the lookup to Open Invoices (using the full key fields for that table) and failing the record when not found... filtered to your specific VI job's program name if you want, but it is not something you can do directly in VI (that I know of... without Perform Logic, which is not something I do).

Thank you for the explanation, Kevin.

There is some perform logic to check for duplicate invoices, but I have no idea how to update it to check a different file.

https://www.sagecity.com/us/sage100_erp/f/sage-100-personalization-customization-and-productivity-tools/37605/4-3-perform-logic-for-a-p-invoice-duplicates/324886#324886

As Kevin said, there isn't a link at that point.

I was considering a UDS, but you may want to filter it so it only triggers during the VI import so it doesn't affect manual entries. If you embed everything in the VI job, you don't have any additional dependencies or have to worry about filtering a UDS.

If you use a UDS, then you should still be able to use the LastErrorNum instead of doing your own lookup in AP_OpenInvoice.  Might as well leverage existing functionality when possible because Sage 100 is validating the invoice number entered and setting the warning when it is a new invoice number.  If the warning is set, then you would use oScript.SetError to force it to fail.

Using the LastErrorNum shouldn't be needed... since InvoiceType indicates new invoices.

I wonder if this could be brought in as a FileAssign temp field (before the line is saved), and override the line payment amount when New.  Hmm... something to test perhaps.

I didn't look that close at the file layout, but you are right, should be able to use InvoiceType on the Select tab and have it set to not equal "A" and that will cause them to not be selected.  I did a simple test with Manual Check Entry through the UI and confirmed that the InvoiceType is set to "A" after entering a new invoice number and answering yes to the prompt asking if it is a new invoice.  The prompt is bypassed during the VI import but would imagine the InvoiceType would still be set in the same manner.

EDIT: Guess I spoke to soon, the InvoiceType field does not appear in the list of available fields.

Tried this option with different files and modifications but couldn't get it to compare.  Thank you for the suggestion though.

Are you importing into the line's invoice comment field?

Are you using any Select criteria against the lines?

My response regarding "Tried this option..." was in reference to the suggestion from Kevin regarding the perform logic.

In regards to importing into the line's invoice comment field, I am not importing anything.  As for a Select criteria, I do not have any at this time. 

I initially experimented with forcing/overriding the lines selection criteria using perform logic on the "On Execution" event but thought that would prove problematic if you were using line selection criteria. 

So then i started thinking about it another way.  Unfortunately, the invoice type isn't set until after the invoice number is assigned, which is after the condition on the field is evaluated so I couldn't put logic there.

Instead, I tried another approach with the InvoiceComment field.  This accounts for if you are importing into the InvoiceComment field and/or using line selection criteria.

Take the following fields for example.  Notice the InvoiceCOmment repeated twice.  The first occurrence is if you are importing it.

The second occurrence is using a calculation that retrieves the InvoiceType into a variable and compares that variable using the TBL function.  If the InvoiceType equals "A", then the TBL function returns "Invoice Not On File".

Here is the calculation.

TBL(UCS(TBL(coBusiness'Lines'GetValue("InvoiceType$",temp.InvoiceType$)=1,temp.InvoiceType$,temp.InvoiceType$))=UCS("A"),{AP_ManualCheckDetail.InvoiceComment$},"Invoice Not On File")

The InvoiceComment is then used in the line selection criteria.  

If it equals "Invoice Not Of File", it is not selected.

Here is the sample data I am importing.

Here is the result.