Sage 100

Doing a VI Import of A/R Cash Receipts for 4.30 (soon to be 4.40/4.50), and we'd like to be able to pull the Customer # based on the Invoice # that's part of the import. Is this feasible (from Open Invoice file I assume would be best)?

That certainly would be the smallest table to pull information from. Is this lookup part of the VI?

Yes, since we won't have the Customer # during the import, we'd like to grab it during the import.

Interesting. Keep us posted on how it turns out. Have never thought about doing this ""live"", only via a intermediate program to match the customer # to the invoice.

I'm thinking maybe via some perform logic, but don't know.

Certainly open to different suggestions/options.

MAS does allow you to duplicate invoice numbers, BTW

As I said, my inclination would be a simple Access DB to import cash receipts file, make the match and then export a file with the VI file with the customer #. It would also have an error log for no matches or invoice payment was in excess of open balance. Put all of this behind one menu button for the client.

@PhilMcIntosh, agreed. But for the small amount of times that's expected to occur and the payment gets applied to the wrong customer, they'd just have to manually fix it - unless we could somehow leverage the functionality of Ext Sol AR-1200 Cash Receipts Entry by Invoice # which was incorporated into 4.40. Otherwise, ideally it'd be great if that record simply ""failed"" and therefore nees to be handled manually via Cash Receipts Entry.

I did some perform logic to grab the invoice number based on the Cust PO in the input file, so I would think this would be similar.

So any further thoughts on going the Access route vs. VI Perform Logic? Just before I saw Beth's post, I was close to going with Access (i.e., Bring the Excel source file into Access each time, push a couple buttons to have it pull into the xls the Customer #'s from MAS 90 based on the Invoice #'s already present in the xls, and then they'd run the import - along with having something in place in case there's multiple instances of a given invoice # in the MAS file.) @BethBowers, is that something you could share? Feel free to PM me.

This does something different for CONSIGNM types - I don't remember the details re those. ! Find invoice number based on P/O number FIND_INVOICE: if not(cOpenInvoiceFH) then { cOpenInvoiceFH=coSession'opentable(""AR_OPENINVOICE"",""COMPANY"") } IF STP(IMP$[5])=""CONSIGNM"" then BAB_INVOICE$=MID(IMP$[3],22,8) ELSE BAB_INVOICE$=stp(IMP$[5]) ! default to PO Number in import file BAB_INVOICE_TYP$=""IN"" IF STP(IMP$[5])=""CONSIGNM"" then CHECK_INVOICE$=MID(IMP$[3],22,8) ELSE CHECK_INVOICE$=stp(IMP$[5]) SELECT *,rec=tmp$ FROM cOpenInvoiceFH begin ARDivisionNo$+CustomerNumber$+$$ end ARDivisionNo$+CustomerNo$+$FE$ where CHECK_INVOICE$=tmp.CustomerPONo$ BAB_INVOICE$=tmp.InvoiceNo$ BAB_INVOICE_TYP$=tmp.InvoiceType$ BREAK ! leave loop if finds first occurance NEXT RECORD RETURN CLOSE_FILE: Close(cOpenInvoicetmpFH,ERR=*next) cOpenInvoicetmpFH=0 RETURN