By using this website, you agree to our Terms of Use (click here)
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.
Very interesting concept of cross-joins, which version of Acumatica you did this, unable to load the XML file it throws an error. I'm trying to load this into 2018 R2, did you use same version or is it 2019 R2?
Ahh, I am on 2019 R1. I took some screen shots to help you out.
Thanks for attaching screenshots, figured out this won't work in 2018 R2 and it works grt in 2019 R1 and R2. The reason GI never exposed the child fields in the parent section and vice versa, looks like a major change from 2018 to 2019 which is a good thing within GI framework.
@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.
I guess need to stay away from validate button 🙂 ... that did the trick was able to view inquiry, also added the dates so it could be filtered for specific period, though it needs dates from ARInvoice and ARPayment to filter, couldn't use single date field to query.
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.
Online Members
