Inconsistent results between Dashboard and Generic Inquiry

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

@lewisad - Many times when you’re using filters while calling out separate fields, if you’re not using the proper operators, the drilldown or results will display the data more than once.  What I mean is, using OR instead of AND, or not including brackets. Also, your filters are different.  One includes a parameter and one doesn’t.  This could display different results depending on the parameter. 

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.

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.

Thanks.