VI Invoice Counter on export of AP_CheckDetail

SOLVED

Hi Everyone,

I am trying to export the AP_CheckDetail and CheckHeader information but I need to have a counter on the invoices.

There is a field NumberOfDetailLines but if the Check has 5 invoices this field shows 5 but I need to have each invoice listed as 1, 2, 3, 4, 5 (e.g Inoivce 001 = 1, Invoice 002 = 2, Invoice 003 = 3).

I have created a Temp field to Add 1 (Temp001+1) but if I select Reset Temp Variable each record has a 1 (resetting on the Detail line).  If I don't select the reset it continuously counts ALL records (not just the ones for the specific Vendor).

Any ideas?

Parents
  • +1
    verified answer

    I was able to accomplish most of what you wanted. I created Temp001, as Data Type Number Operation Assign and Accept.  Then I created Temp002, Data Type String, Operation Assign. Calculation (click on tools) in expression text concatenate the header fields for AP Division + Vendor + Check Entry Number - 'AP_CheckHeader02.APDivisionNo$+AP_CheckHeader02.VendorNo$+AP_CheckHeader02.CheckEntryNo$'.  Do a check to verify syntax and accept on tool dialog then and accept Temp002. 

    Go back to Temp001 and click the calculation Tool and enter 'TBL(POS(AP_CheckHeader02.APDivisionNo$+AP_CheckHeader02.VendorNo$+AP_CheckHeader02.CheckEntryNo$=Temp002$),1,Temp001+1)'  This will set Temp001 to 1 when the division + vendor + check entry number changes or it will increment the value by one.

    Note Temp001 must be before Temp002 and I had to set TEMP002 as an assign, which means it exports the column in order for this to work.  Setting it to calculation causes the calculation to occur prior to setting the count so it just counts all records.  If I set to Run Assign it exports the column name (if selected) and nul value for the column.

    Temp001 APDivisionNo VendorNo CheckEntryNo CheckDate CheckNo CheckAmt InvoiceNo PaymentAmt DiscountAmt UnappliedDiscountAmt Temp002
    1 1 AIRWAY 1 1050 1053190 500 0 0 01AIRWAY001
    2 1 AIRWAY 1 1050 Jun-13 550 0 0 01AIRWAY001
    1 1 ALLCLIM 1 15 1053190 7.5 0 0 01ALLCLIM001
    2 1 ALLCLIM 1 15 1053190 7.5 0 0 01ALLCLIM001
    1 1 ANDERS 1 307.17 112384 104 0 0 01ANDERS001
    2 1 ANDERS 1 307.17 118863 105.17 1.79 16.17 01ANDERS001
    3 1 ANDERS 1 307.17 1053190 98 0 0 01ANDERS001
  • 0 in reply to daburke

    Thanks so much!

    That worked great.  I put the Temp001 and Temp002 as the lat fields and used the Run Assign on Temp002 )thought it looked a iittle cleaner.

    Really appreciate the help.

    Jacquie

  • 0 in reply to Jhoyte

    Hi Jhoyte, happy to see that the community helped you figure out a solution!Tada

    If you have a minute, tell us about your great experience on Sage City, take this short 3 question survey

Reply Children
No Data