Question

Creating a Generic Inquiry that uses a Data Provider Connection to external sql server.

  • 1 June 2023
  • 3 replies
  • 268 views
M
Rank
Userlevel 1

We are relatively new to Acumatica so I am trying to find out if it is possible to create a generic inquiry that uses a Data Provider to an external MS SQL database to pull data and create dashboards. That is the high level question.

DETAILS

We are running Acumatica version 22 R1. I have a Data Provider using the MS SQL Provider type that connects to an external MS SQL database that runs a specialty manufacturing application that we use. The Data Provider is working and I am using that data provider to power some import scenarios that bring new customers and invoices from the manufacturing application into Acumatica on a daily basis. This has been working great so I know I can connect and pull data using an import scenario.

OBJECTIVE

What I would like to do is create a dashboard in Acumatica that shows any accounts that have been closed in our manufacturing application that are still open in Acumatica. This would be used by the accounting department to see which accounts we need to potentially close out in the Acumatica system. Because of the delay in customers making payment on outstanding balances there is often a delay between an account being closed in the manufacturing system and the closure of the same account in Acumatica.

I would appreciate any insights that people have to share on this. If there is a better way to do this or some other approach I should be looking at please let me know. Thanks in advance for your consideration. 

3 replies

Sagar Greytrix
Rank
Userlevel 5
Badge +1

Hi @mtcook 

You can achieve your objective by following these steps in Acumatica:

  1. Set up a Data Provider to connect to your external MS SQL database.
  2. Create a Generic Inquiry to retrieve the required data from the external database.
  3. Configure the data source, define fields, apply filters and conditions in the Generic Inquiry.
  4. Design a dashboard using the Dashboard Designer and add widgets to display the information.
  5. Configure the data source for each widget to use the Generic Inquiry.
  6. Publish and share the dashboard with the accounting department.

By leveraging Generic Inquiries and Data Providers, you can connect to the external MS SQL database, retrieve the necessary data, and create a comprehensive dashboard in Acumatica to monitor and analyze accounts that need attention.

You have the option to set filters and parameters in the dashboard, as shown in the screenshot below.

Regards,

Sagar

Sagar Gadge Team Lead - Acumatica O: +91-22-67687800 E: sagar@greytrix.com
M
Rank
Userlevel 1

Thanks for the reply. I am glad to hear that generic inquiries can be used to access external data sources via the data provider. I have followed the directions and I have confirmed that my Data Provider is connected to my external database and when I refresh the schema I can see the tables and use them in my import scenarios. However when I created a generic inquiry and tried to add the tables from my Data Provider I am unable to see any of those tables. Is there something I have to do in particular to select the Data provider from within the Generic Inquiry? Is there a setting on the Data Provider that I need to configure that allows those tables to be visible to Generic Inquiries. Thanks again for your help. I think I am really close to getting this to work if I can add the tables from the Data Provider to the Generic Inquiry. Any additional help you could provide would be greatly appreciated.

xkylewrightx
Rank
Userlevel 5
Badge

@sagar07 
We are also wondering if you can elaborate on step #2 and #3: 

      Create a Generic Inquiry to retrieve the required data from the external database.

      Configure the data source, define fields, apply filters and conditions in the Generic Inquiry.

 

I don’t see where its possible to add the data provider to the results of a GI.

 

Kyle Wright CSW, Inc. | www.cswgraphics.com

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