How to add subaccount to the AP Aging Period Sensitive report in report designer?

Hi, Are you already filtering on Debit/Credit (aptran.drcr)?

You will want the debit on bills, to find the expense subaccounts. You will want the credit on Debit adjustments, to find the expense subaccounts.

Each bill can have multiple lines, so there will be some duplication on the bills with > 1 expense line.

Laura, I am not filtering on debit/credit,  I am using the LineNbr since most of the AP Bills/Documents have multiple lines.

I am using an expression that if there is a receipt nbr on the line, then pull the expense sub from the PO receipt line, otherwise pull the line’s sub. 

My expression: =iif([APTran.ReceiptNbr]=Null, [APTran.SubID_Segment1], [POReceiptLine.ExpenseSubID])

The first issue is each line on the document/bill is listed twice (I grouped by line nbr so i could validate the number of times it printed).

Secondly, I can get the PO Receipt line sub ID to pull but not the APTran subid segment 1.

Any thoughts? I am sure I have a grouping wrong, but being an AP Aging period sensitive, I am not sure what to change without affecting the aging itself.

And of course the next step is to group the report by the ‘department’ field.

Hi, I looked in the tables and I agree, line number is an excellent choice - line number is not repeated in APTran table within a document.

Can you provide a screen shot of the tables & joins? In my experience, extra records can be a symptom of incorrect join type, or a need to join on more than one field in the tables.

Thanks.

Hi - so I started with the AP Aging Period Sensitive and added 2 tables (I had 3 but tried removing one - POReceipt - hoping it would correct the duplicate line details, but no luck)

The table and relationships are below

Any thoughts? Is there any additional information that would help?

Hello, I don’t think I can help further, but perhaps showing the Grouping will help the next report expert.

LB