Hi @jneumann! To answer your first point there are a few different GI building tools to help with joins.
The first is the “Add Related Table” function on the GI entry form itself, you can select from tables which can join and will even pick the data links to join to, though keep in mind it will always create an Inner type join, though it can be quickly changed if needed.
The second is the DAC Schema Browser where once you’ve added a table to the GI you can click it and it will bring up the DAC Schema for this table where you can see key linking fields as well as foreign keys. Scrolling down on this screen will show you incoming and outgoing references of how to link these tables to related tables (it isn’t a definitive list though and being creative in how you look for tables can help).
Lastly there is the Merged DAC Schema screen, SM406000, which is similar to the DAC Schema except you basically plug in a table and then it will give you options to review incoming and outgoing reference tables.
The second point regarding adding a SQL view is done via customization. You can add DACs and SQL Views to the site and then reference them in the GI. I don’t really have a newer reference though this one is generally still the same:
https://www.acumatica.com/blog/technical-tuesday-report-from-sql-view/
There is also probably somewhere in the community with newer instructions but I couldn’t find anything after a quick search
Hello,
Bump for visibility, I need to solve a similar problem:
I am also on this road, and would like to share my findings as a humble learner of this system. I have found that the tools I have used in the past aren’t the best option going forward, because there is less friction when using the tools that Microsoft supports in their ecosystem with .NET, C#, Odata.
As for the SQL, I hear that the SQL can only be accessed “directly” on a local instance of Acumatica after it has been loaded with a snapshot of the production or sandbox testing instance. This is a fair design choice to not expose it via an API.
Will follow this thread for more discussion
Benjamin
Thanks for the quick response. I saw that Add Related Table function but have not used it yet. I will try creating something using that. I am familiar DAC Schema Browser but not Merged DAC Schema Browser. I will take a look at that. I was hoping to be able to see what code gets generated behind the scenes when I create a Generic Query showing the actual joins, Inner, Left, Right etc…. What gets generated as a result of my placement of parenthesis and use of AND or OR clause and Conditions I enter.
Regarding creating SQL views to use as a source for Generic Inquiries and Reports I was afraid that might be a customization. Just curious do you know anything about the number of customers that had that customization done. Like what percent of current customers are currently using it.
Thanks
Joe
@jcavanaugh thanks for your insight also
@jneumann I don’t know any numbers on how many customers have customizations with SQL views in them, but I suspect it is quite a bit since I’ve worked on quite a few cases where I either recommended it or saw it was already in use.
There are at least two ways you can check the SQL query generated from running a GI.
The first would be to run the GI and then check the trace by clicking top right corner Tools > Trace…
The second would be to navigate to Request Profiler screen and click to activate all the logging and then run the query and view the results in there
Thank you Benjamin I appreciate the info on customer adaptation of SQL views. I will try both methods you listed to see the SQL query generated.
Thanks again.
Joe Neumann