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.
@timrodman Yeah, beware on MySQL versions that the view has performance issues. It appears that MySQL can put the contents into a temp table i.e. to disk and then retrieves the data from it.
https://dev.to/jamiemcmanus/beware-the-performance-dangers-of-mysql-views-352e
@richard-whight Thanks for the info. I haven't used MySQL myself for Acumatica since I'm still skeptical about it, but I hear others do. I just wonder how well it can actually scale.
@timrodman Yeah, there's a reason why MS SQL Server costs more. Perhaps it's the Master Data Services or the Data Quality Services or the Integration Services or the Reporting Services or the Analysis Services or perhaps it's the SQL Engine itself that can deal with Petabytes of data.
I'm not biased *hides MS Certification* 🤡
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.
@timrodman and @wyatt-erp - brilliant. works a treat and thanks for adding "UNION All" to the blog, that is exactly what i searched. Adding a few extras: UNION ALL ACUMATICA, UNION ALL MYOB ADVANCED
@daniel-currie and @wyatt-erp this is indeed a brilliant technique. Note that recently I've found myself using the PX.SM.DateInfo table instead of creating an Attribute. Then I do a "between" filter for the number of records that I need. PX.SM.DateInfo starts at January 01, 1970 so, if I need 4 records, then I do a "between 19700101 and 19700104" filter.
That way I don't have to create an attribute which makes the Generic Inquiry easier to move between environments that may or may not have the necessary Attribute.
Thanks to @lauraj46 for teaching me this technique on this post:
https://www.augforums.com/forums/acumatica-generic-inquiries/joining-to-dateinfo-table
@daniel-currie @timrodman using the DateInfo table discovered by @lauraj46 is just what this thing needed!
Thank you very much for this solution, it is exactly what we were after! If anyone is looking for a GI to get all customer open invoices / sales orders, here is a GI I built to do it, using the above method:
The Result
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.
@roycelithgo Definitely works! And in case anyone runs into this, make sure you use the function ISNULL([value],0) when adding them.
@wyatt-erp I got it working! It did get a little tricky when joining additional tables but I sorted it out.
I've now got a GI that calculates P&L Balances for the selected Date parameter.
This technique will be used in the future I am certain of it. I reckon this post needs to win some sort of an award! 🤣
@roycelithgo It doesn't scale to large data on MySQL very well where you have quite a number of joins. I've had timeouts. Should be sweet as on MS SQL though.
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
I got the IIf statements working...thanks again.
I am still having issues with multiple results. I have one customer that has 1 contact and 5 locations. I get the Customer name 5 times Sort Order 1 with no Contact or Location info, then 5 Sort Order 1 with the Contact info and finally 3 each of the Location with Sort Order 2.
I'm not sure what is causing the repeating, so I don't know what to do to remove them.
Any clues?
Please disregard the post asking about repeating results. I had Bill Filipiak with Net@Work take a look with me and he pointed out that I was using the wrong Address table. Once that was corrected, I got the results correctly without using any "Conditions."
Thank you again for your help to get me there.
Online Members
