Hi,
I’m having an issue where the results of my generic inquiry does not match the results on my dashboard. I’m using the same filters for both of them. In the below example, I get 137 records on the inquiry filter, 120 records on the dashboard widget and 137 records from the dashboard drilldown when clicking the widget that says 120.
Any thoughts on where I have gone wrong?
Best answer by ejmillar
View original
It looks like the widget is counting the unique order numbers. The screen shot below shows that ‘EWTM-00099’ is listed twice but the widget is counting it as one.
Thanks for your keen observation. Is there a way around that?
Figure out why the GI is displaying duplicate orders. Is there a reason that the duplicate orders are displayed? Can the duplicate orders be removed?
If the dups cannot be removed then create a new field which uniquely identifies the row and amend the widget to count that field.
Will investigate and report back. Thanks
+6
I can’t see what operators you have on your filters by your given screenshots, or what’s included in your parameters, but if you’re calling everything being modified in the last 7 days, OR everything with a “Supervisor”, it could potentially list them twice and each could deliver an additional result, or less results depending on the circumstances.
Hi
Here are the parameters and conditions I’m currently using. Notice some are disabled and still get the same results of duplicates and triplicates. I will create a fresh inquiry or copy from a known working one when all else fails. But would like this puzzle solved.
There are different groups of filters, like “7 days or less” between 7 to 30 days” and so on. But all would have the date range and an option to change the supervisor for the date range.
I would check the tables that you are using in the GI. Do you have a one to many relationship from FSServiceOrder to another table?
I’ve attached the GI. It was a copy from an already existing one that came with Acumatica.
Hello,
The grouping tab has a grouping but it’s not active. If you make it active then the duplicate records will be removed.
I would also review the tables and relationships. There are quite a few tables which are not used within the results tab.
Hi.
I had added that after you had initially mentioned the duplicate records. It did work. But I found it strange that I would have to resort to that. I then deactivated it to keep checking to find the source of the issue. I will copy this GI and begin removing unnecessary joins and tables and see if that corrects the problem. I’ll review the original GI and see if the issue was there initially. Otherwise, I’ll just start fresh.
Thanks again for all your help.
If you disable the relationships to FSBillHistory and ARInvoice like below then you should get unique records:
Could you recommend a good resource to learn about table relationships and database queries?
Here is what I recommend:
Learn SQL: Types of relations (sqlshack.com)
Acumatica S130 Data Retrieval and Analysis - Lesson 2.2 Getting Data from multiple DACs.
Here is what I recommend:
Learn SQL: Types of relations (sqlshack.com)
Acumatica S130 Data Retrieval and Analysis - Lesson 2.2 Getting Data from multiple DACs.
Thanks.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
