Solved

Programmer/Analyst

  • 3 April 2024
  • 5 replies
  • 65 views
J
Rank

Hello we just went live with Acumatica Monday. I am trying to learn to use the Generic Inquiry interface.

Basically I have 2 questions.

 

First question - I am working with multiple tables and trying to create joins using the interface. Having some trouble getting my head around setting up proper joins. I am use to just creating a SQL view (Oracle) and then access that view through our ERP via a report interface. Is there a specific method for me to see the code behind a Generic Inquiry that I create.  I think that would really help me figure things out. If I understand correctly that would be the BQL code or some sort of combination of that and something else.

Second question  - As I mentioned I  am used to creating SQL views in our previous ERP (IFS). Reading through some post on line it appears we have that capability in Acumatica  where we can create a SQL view and then create a DAC (Data Access Class) to look at that SQL view and then access that DAC via a Generic Inquiry or a Report.  They mentioned the piece to create the SQL view is done outside of Acumatica by just going into the SQL database and creating the view.  Since we don’t have the physical data base here I am trying to picture the process to achieve that.  If anyone can explain the specifics of that it would be greatly appreciated.

Thanks

Joe Neumann

Peerless-AV

Aurora Illinois (Chicago burbs)

icon

Best answer by BenjaminCrisman 3 April 2024, 18:43

View original

5 replies

BenjaminCrisman
Rank
Userlevel 7
Badge +4

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

jcavanagh
Rank
Userlevel 1

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

取天下常以无事，及其有事，不足以取天下。
J
Rank

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

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@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

J
Rank

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

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved