Solved

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

  • 11 August 2022
  • 6 replies
  • 121 views
L
Rank
Userlevel 1

I am in the process of trying to add the expense subaccount from the AP bill - using the APTran table - onto the AP Aging Period Sensitive report. 

Has anyone been successful in doing this?

I have added the tables APTran  and POReceiptLine - which hold all the information I need. The APTran has the AP bill line detail information and the POReceiptLine has the account number and subaccount if the bill is linked to a PO.

I have most of the information pulling on BUT it is pulling each line on the AP bill twice. I realize that something is wrong with my relationships and/or my grouping since the RefNbr is a link between multiple tables (APRegister, APInvoice, APTran,..). I am at a loss on how to correct this since it doesn’t seem like I can remove any of these tables or modify the relationships.

Any help would be appreciated! Thank you.

icon

Best answer by lburrow62 16 August 2022, 22:03

View original
Lynn

6 replies

Laura02
Rank
Badge +18

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 ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
L
Rank
Userlevel 1

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.

 

Lynn
Laura02
Rank
Badge +18

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.

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
L
Rank
Userlevel 1

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?

Lynn
Laura02
Rank
Badge +18

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

LB

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
L
Rank
Userlevel 1

Thank you for your help, I really appreciate it.

I believe I was able to figure it out based on  your comments. I reviewed the grouping and sorting and then moved my detail (line) information to the footer and ‘voila’ the lines did not duplicate. 

I am sure my relationships and grouping are not as efficient as they should be, but I am getting the information needed.

 

Lynn

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved