By using this website, you agree to our Terms of Use (click here)
In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Sincerely,
Tim Rodman
Just throwing out there to see if any ideas one has ... I need a GI which shows list of customers with their total sales and total payments with specific time period. If I choose to see all sales from Nov and also what payments I received from my customers during same time period, there could be orphan records or exception then how do we show 2 values from 2 diff DAC or is there a GI within a GI concept or how do we solve this?
The only way that I can think to do it would be to use subqueries.
And the only way I know of currently to do subqueries would be to create a SQL View and turn it into a DAC.
You might want to add your vote to this idea for subquiries in Generic Inquiries:
https://feedback.acumatica.com/ideas/ACU-I-1190
Also, Power BI (including the latest version of Excel) handles this kind of scenario quite nicely.
There is a trick I like to use in SQL to pull information together like this. The idea is to join on a list of numbers running from 1 to however many "types" of things you are trying to join on that you don't want interfering with each other. In your case, you are joining on 2 things, AR Invoice and AR Payment, so you need to be able to join on a 1 and a 2.
You'll need to guarantee a clean list of numbers for this to work. I made a combo attribute "MYPIVOT" and gave it values 1-10 with sort order 1-10 which was very quick and easy.
In your GI, you will want to start by adding tables: Customer, CSAttribute, ARInvoice, and ARPayment.
You first relation needs to be Customer to CSAttribute. CSAttributeDetail.AttributeID ='MYPIVOT' and CSAttributeDetail.SortOrder <= 2
If you would check, this gives you results like:
CustomerA, 1
CustomerA, 2
CustomerB, 1
CustomerB, 2
CustomerC, 1
CustomerC, 2
The idea now is to join ARInvoice to Customer and "1" and ARPayments to Customer and "2". I attached a sample GI of this. If you create the MYPIVOT attribute, you can see the results and how it behaves. If a Customer has only invoices and no payments, there will be "1" rows with values, and one "2" row with just NULLs. Once you get the hang of the concept, it can help get around the "No Sub-query" limitation in some cases.
Ahh, I am on 2019 R1. I took some screen shots to help you out.
@harsha-sarjapur
You should be able to get around it by just using the pencil (or typing) and setting the fields manually:
=[CSAttributeDetail.SortOrder]
The Validate button doesn't like it, but it will give you the results you want.
Holy cow, this is an awesome technique! I love it! Thanks for sharing @wyatt-erp!
I'm adding the phrase UNION ALL to this post since UNION ALL is how I would have done this in SQL. That way I can search Google for this phrase to find this post in the future:
"UNION ALL" site:augforums.com
I think this technique could also work for printing multiple copies of a report. And it's better than the "2 copies" technique mentioned below because you could do more than 2 copies.
@daniel-currie @timrodman using the DateInfo table discovered by @lauraj46 is just what this thing needed!
This is nuts. But thanks. I can use this and call it my own 🤡
This is truly awesome. I was stuck yesterday with this exact issue and this solution will hopefully solve it. In my case i actually want to aggregate the data from the 2 sources and I assume this could be done with a formula and a SUM applied to that, and appropriate group by on the common parent.
I came across this post while trying to create a GI to view a list of Customer's Contacts and Locations is one list. The method above gets me close, but too close to figure out where I went wrong.
Tables:
PX.Objects.AR.Customer Customer
PX.Objects.CR.Contact Contact
PX.Objects.CR.Location Location
PX.Objects.CS.CSAttributeDetail CSAttributeDetail
Table Relations:
Customer Inner CSAttributeDetail =2 Is Greater Than or Equal To CSAttributeDetail.SortOrder AND ='MYPIVOT' Equals CSAttributeDetail.AttributeID
Customer Left Contact Customer.BAccountID Equals Contact.BAccountID AND CSAttributeDetail.SortOrder Equals =1
Customer Left Location Customer.BAccountID Equals Location.BAccountID AND CSAttributeDetail.SortOrder Equals =2
I get the same Customer multiple times, some with a Contact, some with a location, some with neither. The results never give me all of the Contacts and Locations for a Customer.
I am not really following what the CSAttributeDetail is doing, so maybe it is there or these two senerios, mine and the one above do not equate. I have a Customer Table (One) to Contacts Table (Many) and to Locations Table (Many) that I would like one list of the Customers, it's Contacts with phone and email and it's Locations with location name and address. Each could be on a seperate line, like:
Customer
Customer Contact phone email
Customer Contact phone email
Customer Contact phone email
Customer Location name address
Customer Location name address
Customer Location name address
Customer Location name address
Thank you for any help you can give and for the help above...I did like the Customer Open Invoices/Sales Orders example @stewart-williams
It looks right.
You have 3 columns.
Column 1 will always be Customer.BAccountID (or similar)
Column 2 will be IIf([CSAttributeDetail.SortOrder] =1 , [Contact.Phone], Location.Name] )
Column 3 will be IIf([CSAttributeDetail.SortOrder] =1 , [Contact.Email], Location.Address] )
I may have the field names wrong there as I just did it from memory but what's important is that you need to do an iif to get the values into the right columns.
Also, if you want one row with just customer you will need 3 rows in the CSAttributeDetail list and one of those rows does not get used to join other tables.
The data you are trying to shape should look like this.
By putting the IIf into the formulas as I suggested is how you get your result.
If you have a customer with no location and no contact details you will always get 3 rows returned. In fact you will always get 3 rows returned because of the inner join Customer to CSAttribute. If you want to throw away some of the rows you will need to add a condition that if
((sort order is 1 or sort order 2 ) and (Contact.BAccountId is not empty/null OR Location.BAccountID is not empty/null))
OR
(SortOrder = 3).
Do you see what I mean?
Thank you. That looks like what I was missing...well that and the Location Address is actually in the PX.Objects.CR.Address table, so I added it.
What you are showing with the IIf formula is how I did the same type thing in my SQL queries in Access to build reports for my old system. I was just failing to see how to reproduce that in the Acumatica "wizard" GUI. I will give this a shot to see if I understand what your are saying.
I may have follow up questions, but I will try my best to figure it out before asking.
Thanks again!
Ah SQL. Actually I got that wrong
In SQL the where statement would be (Given just Customer on its own row would be attribute sort order 3):
Where
(
(SortOrder = 1 AND Contact.BAccountId is not null )
OR
(SortOrder = 2 AND Location.BAccountID is not null )
)
OR
SortOrder = 3