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.

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.

That is some beautiful work of art, David...if you are ever in the Portland, OR / Vancouver, WA area let me know...drinks will be on me!

And, as an FYI, it did work...perfectly!

David is surly a Sage City MVP.

Totally agree with that!

As mentioned earlier...this is working perfectly.  As I was testing the import process I realized the user would be able to import the same file a second time and the transactions would all come through.  On the Job Log under the L.InvoiceNo. filed I do get a message stating "The invoice #### is already paid.  Do you want to continue?"  Could I add additional information to the InvoiceComment Calculation to also skip these transactions?

You could, it sounds like that message is coming from coBusiness'Lines'LastErrorMsg$, i would see what coBusiness'Lines'LastErrorNum$ contains and then include an additional TBL function to see if there is a match and return a different "comment", such as "Invoice Paid Already" and had an additional select criteria.  Alternatively, if you don't care what gets written to the InvoiceComment for descriptive purposes, the existing calculation could be used but also add to the evaluation and just return "SKIP" if either condition is true and modify the select criteria.  However, since you would be using the LastErrorNum, you would have to reset it for every line read.  You could do potentially do this with perform logic added to the Before Assign event of the line's InvoiceNo field or embed it in a temp field configured as a line temp field that resets on each record and place it above the InvoiceNo field.

Unfortunately I don't have unlocking keys allowing me to view the code in ProvideX that would allow me to see the LastErrorMsg.

You don't need unlocking keys and you don't need to view any code.  LastErrorMsg$ is a common property for any object you can access with using BOI.  So just like I referenced coBusiness and coBusiness'Lines in the calculation, you can do the same thing from perform logic, conditional logic, and a calculated field's calculation.  However, it is a property that is set by the business object when it encounters a warning or failure but is not reset, only overridden the next time a warning or failure occurs.  This is why I have been stating that you would have to reset it prior so you can appropriately trap it a specific condition when it actually occurs and isn't a left over from a previous occurrence.  If you want to view the output of the Lines' LastErrorMsg$ property, just add a string temp field after the L.InvoiceNo field and have it reset on each record, make it a calculated field, then place coBusiness'Lines'LastErrorNum$ in the calculation field.  You can repeat this for another temp field but use coBusiness'Lines'LastErrorMsg$ in the calculation field.  Once you know what to look for in either property when an invoice is already paid, then you can include it in the other calculation but you still have to make sure you are resetting whichever property you end up checking, to do this, just set up before logic on the Before Assign event for the line's InvoiceNo field and use the following in the perform logic field.

coBusiness'Lines'LastErrorNum$=""; coBusiness'Lines'LastErrorMsg$="" ! '

When the perform logic command contains a single quote, the command gets executed as a ProvideX statement, if it doesn't, it is expected to point to a file that contains ProvideX code which gets PERFORM'd.  So whenever I want to execute a statement, I always place the ProvideX character to indicate a comment, which is the exclamation mark followed by a single quote.  Now in the above code example, because I am referencing properties of an object with the single quote, the command would be executed anyway, but if I wanted to override a variable or something, like Temp004$, there wouldn't be a single quote involved there so I would have to use the exclamation mark followed by the single quote so I just make it a habit to always include in for code meant to be executed.

I ended up adding the coBusiness'Lines'LastErrorNum$ within a Temp field right after L.InvoiceAmt.  When I ran a test on the import file I noticed the Temp field is populated with: AP_ManualCheck_InvoiceBalance.X1.  How would I add this result into the existing L.InvoiceComment field that already has the "TBL(UCS(TBL(coBus..." information that is currently in place?  At this point I don't care what gets written to the InvoiceComment for descriptive purposes so I'd probably change the information to "SKIP"  

You could use the following to set the InvoiceComment to SKIP if the InvoiceType is "A" for new invoice or the last error number is equal to "AP_ManualCheck_InvoiceBalance.X1".

TBL(UCS(TBL(coBusiness'Lines'GetValue("InvoiceType$",temp.InvoiceType$)=1,temp.InvoiceType$,temp.InvoiceType$))=UCS("A") OR UCS(coBusiness'Lines'LastErrorNum$)=UCS("AP_ManualCheck_InvoiceBalance.X1"),{AP_ManualCheckDetail.InvoiceComment$},"SKIP")

If you want to return a different comment for each condition, then you could do it like this in one calculation.

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

You could also break it out into a third InvoiceComment field added to the import job.

I was not aware of which field was triggering the AP_ManualCheck_InvoiceBalance.X1 warning so if it wasn't on the InvoiceNo but instead the InvoiceAmt, then you need to make sure you move the perform logic to reset the LastErrorNum$ property to the Before Assign event of the detail's InvoiceAmt field.

Make sure to modify select criteria on the Select tab to match whatever you end up returning.