Visual Integrator import into AP Manual Check Entry - Need to suppress invoices that do not already exist in AP_OpenInvoice

Question

Running Sage 100 v2018 and have a VI import that brings in records into AP Manual Check Entry. The client wants to suppress any invoices that are not in AP_OpenInvoice and be able to review the Import Log to see those invoices that were skipped. This way the client can allow the invoices that are matched up to go through and only review the invoices that were not in the import file (manage by exception). Any help would be greatly appreciated.

David Speck · Accepted Answer

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.

Sage 100

Visual Integrator import into AP Manual Check Entry - Need to suppress invoices that do not already exist in AP_OpenInvoice

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING