Add field to Report Designer AR Aging Detail by Customer

SUGGESTED

We run the AR Aging Detail by Customer (RD version) and lately we have had some payments that have come in that don't get applied to an invoice right away. So, they get entered as customer cash receipts and we indicate which project they relate to by entering details into the Description field during AR Enter Cash Receipts.  I can't for the life of me figure out how to get the Description field to display only for the customer cash receipts. I probably need a formula but I'm not very good with those. Was thinking just perhaps that someone had already written such a formula or had an Aging Report that would show me the description field on a Cs transaction?  Thank you in advance.

  • 0

    I don't have any of those right now to test it, but I think you can add to the report conditions "OR Cash Receipt Type (AR Transactions) = Customer Cash Receipt".  I wouldn't change the design conditions yet, but just test this condition when you print the report to see. 

  • 0 in reply to Rhonda V

    The report shows Customer Cash Receipts. What it doesn't show is the Description field.  When I add the Description field, I get it for ALL rows.  I want to show Description field only on Customer Cash Receipts. 

  • 0
    SUGGESTED

    Here is the formula to print the "Description" field for Customer Cash Receipts:
    IF(Tran Type (Design Formula) = "Cs cs rc", Description (AR Transaction))
    Be careful to reference the "Tran Type" from the Design Formulas already existing in the report design.
    After adding the newly created formula to the design, from the menu bar in Report Designer, click View > Print Controls.  
    Then from the menu bar, with the newly added field selected, Click Design  > Print Controls.
    From the pop-up window, select "SRT - Status or Act Seq" to assure it prints only on the selected transaction type.
    The color of your newly-added formula field should be the same as the other data fields on the design, except for the one field to the immediate right of the Date field.

    Art Minds
    Independent Senior Consultant
    Pasadena CA 
    Offering remote support and RD and Crystal Report design services

  • 0 in reply to Art Minds

    Thank you Art. That's much closer, but it is still displaying many extra rows.  I only want it to display for rows where there is a current cs/cr, it appears (I think) to be displaying for all rows that ever existed?  I have the "unpaid only" checkbox checked when I run the report, but it doesn't seem to respond to that?

  • 0 in reply to Siri H.

    You might try making that field a conditional print area.  To do that you need a design formula that says Description (AR Transaction) = "cs cs rc".  Then while you highlight that field you use Format - Conditional Print Areas and condition it based on that formula being true for that field (and choose to supress blank lines if it's false).  

  • 0 in reply to Rhonda V

    Thanks Rhonda, I think I mis-spoke. I want a regular aging report, where it shows only unpaid items WITH CS/CS details. If a line item is a CS/CR, I want to include the description field. I still only want to display unpaid (unallocated in CS/CR case).

    I've replaced the Contract field with the following formula:

    IF((Tran Type) = "Cs cs rc", Description, Contract). 

    This works great, except I am getting something like 400+ pages so it's not restricting what it shows me to only unpaid / unallocated items.  What am I missing?  If I highlight the entire row and restrict to just CS/CR as you suggested, I would think I'd ONLY get the CS/CR items and not the current unpaid aging items as well. (also the description is never cs cs rc, it's the tran type, but I imagine that's what you meant.) 

  • 0 in reply to Siri H.

    I wrote a formula called LOOKUP DESCRIPTIUON and I put the field called LOOKUP DESCRIPTION next to the type. 

    The formula goes like this: 

    IF(Oustanding Amount (AR Status) <>0, LOOKUP (Description (AR Transaction), Customer (AR Transaction), Transaction Date (AR Transaction), Run (AR Transaction), Sequence (AR Transaction)))

  • 0 in reply to Rhonda V

    Thanks, I guess our reports are slightly different to begin with.  What you did didn't work for me, but I took the Outstanding Amount part and added it to my existing formula. The results get me down to 239 pages which is an improvement but given that my normal aging is 7 pages, this isn't acceptable. See screen shots and thanks for trying to help! 

  • 0 in reply to Siri H.

    I should have added that I started from the default report design provided with the software that is referenced from the Report Menu with the name you described: "AR Aging Detail by Customer".  If you are staring with a modified version of that report, you may get different results. Are you certain that you referenced the correct field in the formula I provided ("Tran Type" from a Report Design formula, not from the "Tran Type" in the transaction file). The base report design I referenced is sorted uniquely and you must be careful to correctly specify the "Print once per" for the newly added field as described.

  • 0 in reply to Siri H.

    A report that is generating that many pages obviously has a field set to Print Once per Transaction or similar, or the "Summarize by" settings has been changed.

    The screen capture you are showing looks exactly like the default report design I was working with. I would be happy to send you the modified report design that prints exactly as you described you want it, and try it with your data. I tested it agains the Timberline Construction sample data after I entered a Customer Cash Receipt and it prints perfectly. See the screen capture below.  Is this what you're expecting?

  • 0 in reply to Siri H.

    On mine, the Date/Date Condition is printing once per "SRT - Status or Act Seq".  Your Date Condition is a different color than Tran Type so I think it could be the culprit.