Using VI to Import Cash Receipts

SOLVED

Can a VI job be setup to total a column on your source file? For example if importing cash receipts I have a column in my source file pointing to the Line Amount Posted and I want the job to sum up that column by customer and put the amount in the Header Posting amount column or does the total amount posted for each customer have to be in the import file?

Also when chaining the AR Cash Receipt Deposit to the AR Cash Receipt Header file how do you set the deposit number in the AR Cash Receipts Header job to be the same as the deposit number in the AR Cash Receipt Deposit job?

This is for Sage 100 Adv v2020. Thank you.

  • 0
    SUGGESTED

    You can calculate it.  First, for AR_CashReceiptDeposit, right after you assigns/run time assign for batch, bank code, deposit no and date put in a Temp001 numeric field that points to your line payment amount column that assigns on Standard Records and clear on each record.  Then for CashDepositAmt, do a calculated operation where the calculation is {AR_CashReceiptsDeposit.CashDepositAmt}+Temp001.  Next, for CashBalanceAmt make a calculated operation where the calc. is {AR_CashReceiptsDeposit.CashDepositAmt}.

    Similar for the Header posting, but create a Temp001 for the payment amount and again calculate H.PostingAmt using {AR_CashReceiptsHeader.PostingAmt}+Temp001 on Header Record only and again clear on each record.  This will build up the check amount received for each customer and check.

    Good Luck!

  • 0 in reply to VanMan

    It's two years later and I'm faced with this task again but this time I'm importing several invoices that have the It's two years later and faced with similar issue. The import job is for AR_CashReceipts header. Created Temp001 numeric field that points to line payment amount column that assigns on Standard Records and clear on each record.

    The H.Posting amount is calculation using {AR_CashReceiptsHeader.PostingAmt}+Temp001 on Header Record only and again clear on each record.  The lines import fine but the H.Posting fails. I think it's because I have several invoices that have the same dollar amount. If I change the amount posted to be different dollar amount per invoice everything imports fine.

  • +1 in reply to dhalpin
    verified answer

    I ran into this issue in the past too.  Add a new Temp field and map it to the Invoice# column.  You don't have to use the Temp field in anything else, that just tells the import job that the line is unique, even if the amount is the same.

  • 0 in reply to hyanaga
    SUGGESTED

    THANK YOU!! That worked perfectly!!

  • 0 in reply to hyanaga

    Thank you! I was having a very similar issue importing into PR_TimeTrack and adding a temp field to a unique field in my source file solved the problem.