By using this website, you agree to our Terms of Use (click here)
Hi,
Using SaaS so no db access...
Is it possible to perform a left join on an aggregate statement within a GI?
For example, InventoryItem left join to INItemXref and present the Max Alternate ID associated with an Inventory ID if more than one exists?
If I had some kind of SQL access I would likely use a subquery to accomplish.
(I understand that the request is adding an element of randomness to the output, but the Alternate ID is actually considered a bit secondary in this case.)
Thanks!
Can you just do your normal join in the GI and then group by the inventory item fields you need and do a Max on the AlternateID on the results tab?
Thanks for responding...
Yes, I may end up testing that option.
There are some calculated values on the report that I do not want to be affected by grouping.
I have run into this situation before, so I am interested in learning another method if available.
Thanks!
GI is so limiting in Acumatica. Aggregates are performed at the end and nothing can be done with them.
I really miss direct SQL queries.
In these cases I resort to combining queries in Excel via Odata. GI is inadequate here.
I think they were talking about allowing use of GIs as tables or subqueries in the future if I remember correctly.
I have not tried this, but could you write a DB script to create a view with the select statement to gather the data you want? Then add it to a customization package and publish it. Then you could use the view to present to the data you are needing.
Thanks for all the responses...
Royce, yep having SQL access is the bomb...
Ellie, allowing a GI to be used as a source could work if it becomes a reality.
I have not gotten into customization packages yet. I think the big question may be whether a DB script would be allowed in a SaaS implementation? Shawn, if you do test this option, please post your results!
Thanks Again!
I would do the SQL View method discussed above and outlined here in this post:
https://www.acumatica.com/blog/technical-tuesday-report-from-sql-view/
As long as you build everything locally, you can package everything (including the SQL and the Data Access Class) into a Customization Project, then deploy into a SaaS environment.
One note though about this method, since Acumatica is multi-tenant, I hear rumors that this method might be eliminated in the future. The reason is that you can hardcode the CompanyID into the SQL View and "steal" data from another tenant. But I hope they don't eliminate SQL Views without giving us another tool to replace them. Or just give up on multi-tenancy which would be fine with me.
Acumatica needs to become more SQL friendly.
Thanks for the response Tim, and thank you again for hosting this site.
My hope is that Acumatica Corporate is contacting you for feedback on a frequent basis!
Your welcome Rob. I've been enjoying it. And enjoying learning new stuff from everyone here.
When it was at Tim Rodman dot com, Acumatica was keeping their distance and understandably so. Now that it's at AUGForums.com we've been having some discussions. Hopefully Acumatica will start promoting this site officially in the future.
Online Members
