AUGForums.com

An Acumatica User Group

  • Free
    • Start Here
    • Rolodex
    • Podcast
    • Excel Talk
    • Blog
    • Forums
  • Courses
  • Consulting
  • Login
  • Register
Acumatica Forums

By using this website, you agree to our Terms of Use (click here)

  • Overview
  • Member List
  • All-Stars
  • Stats
  • Recent Posts
  • Help
Forums
AUG Forums
Acumatica Generic I...
GI with 2 or more d...
 
Notifications
Clear all

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

Questions [Solved] GI with 2 or more different values

    Last Post
RSS

Harsha Sarjapur
Posts: 53
 Harsha Sarjapur
Topic starter
December 26, 2019 1:44 pm
(@infosourcing)
51-100 Posts
Joined: 2 years ago

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? 

Topic Tags
#GI
26 Replies
Tim Rodman
Posts: 2896
 Tim Rodman    ★★ All-Star ★★
December 27, 2019 12:45 am
(@timrodman)
Over 200 Posts
Joined: 7 years ago

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.

Reply
daniel liked
Richard Whight
 Richard Whight
(@richard-whight)
Joined: 11 months ago

6-20 Posts
Posts: 17
January 24, 2022 7:21 pm
Reply toTim RodmanTim Rodman

@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

 

Reply
Tim Rodman
 Tim Rodman
    ★★ All-Star ★★
(@timrodman)
Joined: 7 years ago

Over 200 Posts
Posts: 2896
February 18, 2022 9:35 am
Reply toRichard WhightRichard Whight
Tim Rodman

@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.

Reply
Richard Whight
 Richard Whight
(@richard-whight)
Joined: 11 months ago

6-20 Posts
Posts: 17
April 21, 2022 7:50 pm
Reply toTim RodmanTim Rodman
Richard Whight
Tim Rodman

@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* 🤡 

Reply
Tim Rodman liked
Wyatt.ERP
Posts: 128
 Wyatt.ERP
December 27, 2019 1:06 pm
(@wyatt-erp)
101-200 Posts
Joined: 2 years ago

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.

 

 

Right-click to Download

Reply
Harsha Sarjapur
 Harsha Sarjapur
(@infosourcing)
Joined: 2 years ago

51-100 Posts
Posts: 53
December 27, 2019 1:26 pm
Reply toWyatt.ERPWyatt.ERP

@wyatt-erp

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?

Reply
Wyatt.ERP
Posts: 128
 Wyatt.ERP
December 27, 2019 1:49 pm
(@wyatt-erp)
101-200 Posts
Joined: 2 years ago

Ahh, I am on 2019 R1.  I took some screen shots to help you out.

Reply
Harsha Sarjapur
 Harsha Sarjapur
(@infosourcing)
Joined: 2 years ago

51-100 Posts
Posts: 53
December 27, 2019 2:35 pm
Reply toWyatt.ERPWyatt.ERP

@wyatt-erp

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.

Reply
Wyatt.ERP
Posts: 128
 Wyatt.ERP
December 27, 2019 3:35 pm
(@wyatt-erp)
101-200 Posts
Joined: 2 years ago

@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.

 

Reply
Harsha Sarjapur
 Harsha Sarjapur
(@infosourcing)
Joined: 2 years ago

51-100 Posts
Posts: 53
December 27, 2019 4:08 pm
Reply toWyatt.ERPWyatt.ERP

@wyatt-erp

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. 

Reply
Tim Rodman
Posts: 2896
 Tim Rodman    ★★ All-Star ★★
January 14, 2020 8:45 pm
(@timrodman)
Over 200 Posts
Joined: 7 years ago

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.

https://twitter.com/TimRodman/status/1141421054532960256

Reply
Daniel Currie
 Daniel Currie
(@daniel-currie)
Joined: 9 months ago

2-5 Posts
Posts: 5
November 29, 2021 3:56 pm
Reply toTim RodmanTim Rodman

@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

Reply
Wyatt.ERP liked
Tim Rodman
 Tim Rodman
    ★★ All-Star ★★
(@timrodman)
Joined: 7 years ago

Over 200 Posts
Posts: 2896
November 29, 2021 4:10 pm
Reply toDaniel CurrieDaniel Currie
Tim Rodman

@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

Reply
lauraj46 and Wyatt.ERP liked
Wyatt.ERP
Posts: 128
 Wyatt.ERP
November 29, 2021 4:26 pm
(@wyatt-erp)
101-200 Posts
Joined: 2 years ago

@daniel-currie @timrodman using the DateInfo table discovered by @lauraj46 is just what this thing needed!

Reply
lauraj46 and Tim Rodman liked
Stewart Williams
Posts: 6
 Stewart Williams
April 21, 2022 7:31 pm
(@stewart-williams)
6-20 Posts
Joined: 2 months ago

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:

 

image
image
image
image
image
image

The Result

image
Reply
Richard Whight
Posts: 17
 Richard Whight
April 21, 2022 7:56 pm
(@richard-whight)
6-20 Posts
Joined: 11 months ago

This is nuts. But thanks. I can use this and call it my own 🤡 

Reply
Tim Rodman liked
Royce Lithgo
Posts: 499
 Royce Lithgo    ★★ All-Star ★★
May 25, 2022 12:26 am
(@roycelithgo)
Over 200 Posts
Joined: 2 years ago

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. 

Reply
Wyatt.ERP liked
Wyatt.ERP
 Wyatt.ERP
(@wyatt-erp)
Joined: 2 years ago

101-200 Posts
Posts: 128
May 25, 2022 9:12 am
Reply toRoyce LithgoRoyce Lithgo

@roycelithgo Definitely works!  And in case anyone runs into this, make sure you use the function ISNULL([value],0) when adding them.

Reply
Royce Lithgo
 Royce Lithgo
    ★★ All-Star ★★
(@roycelithgo)
Joined: 2 years ago

Over 200 Posts
Posts: 499
May 25, 2022 10:46 pm
Reply toWyatt.ERPWyatt.ERP
Royce Lithgo

@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! 🤣 

Reply
Richard Whight
 Richard Whight
(@richard-whight)
Joined: 11 months ago

6-20 Posts
Posts: 17
May 25, 2022 11:57 pm
Reply toRoyce LithgoRoyce Lithgo
Wyatt.ERP
Royce Lithgo

@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.

Reply
Stephen Gorsch
Posts: 6
 Stephen Gorsch    ★★ All-Star ★★
June 22, 2022 3:30 pm
(@sgorsch)
6-20 Posts
Joined: 1 month ago

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

Reply
Richard Whight
Posts: 17
 Richard Whight
June 22, 2022 4:37 pm
(@richard-whight)
6-20 Posts
Joined: 11 months ago

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.

image

 

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? 

 

Reply
Stephen Gorsch
Posts: 6
 Stephen Gorsch    ★★ All-Star ★★
June 22, 2022 5:37 pm
(@sgorsch)
6-20 Posts
Joined: 1 month ago

@richard-whight

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! 

Reply
Richard Whight
Posts: 17
 Richard Whight
June 22, 2022 6:38 pm
(@richard-whight)
6-20 Posts
Joined: 11 months ago

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

 

Reply
Stephen Gorsch
 Stephen Gorsch
    ★★ All-Star ★★
(@sgorsch)
Joined: 1 month ago

6-20 Posts
Posts: 6
June 24, 2022 9:02 am
Reply toRichard WhightRichard Whight

@richard-whight

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? 

Reply
Stephen Gorsch
 Stephen Gorsch
    ★★ All-Star ★★
(@sgorsch)
Joined: 1 month ago

6-20 Posts
Posts: 6
June 24, 2022 12:11 pm
Reply toRichard WhightRichard Whight

@richard-whight 

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.

Reply
  All forum topics
  Previous Topic
Next Topic  
Topic Tags:  #GI (1),
  Forum Statistics
11 Forums
2,118 Topics
9,449 Posts
6 Online
1,654 Members

Latest Post: Restricting access to sub accounts by segment value - group type A Our newest member: Cory Griffiths Recent Posts Unread Posts

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Online Members

Acumatica Forums

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2022 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.

‹›×

    ‹›×