Query has more results than dashboard

[TDray (Todd Dray)]

My query and dashboard show a different number of results. While it is normal for the dashboard to show more since the query is limited to 10000. My problem is the opposite and I wasn’t able to find anyone else who seems to have had this issue.

I created a query and once it it put into a dashboard, it gets less results.

I’m not filtering results on the query:

nor am I filtering the grid itself:

This is a brand new dashboard with nothing else. I started completely over in case I had done something and forgot to change it back, but this involved rewriting the query from the start, and then creating a new dashboard using that query.

If I open the query using this:

It still gives different results than it does in the dashboard. Any ideas or can someone point me in the direction to look?

[bordway (Bruce Ordway)]

Just curious what you query looks like - and if you include index fields?
I believe I’ve seen some strange behavior in dashboards when I didn’t include any key fields in the underlying BAQ.

[TDray (Todd Dray)]

It’s really messy and 7 layers deep. If it helps, the goal is to find old inventory that hasn’t been used by a parameter date and its value.

I’ll post pics, but once again messy and not labeled well as I was recreating one I had already done:

This subquery is used to grab the max transaction number from PartTran for a specific part. This is then fed to the next query to find the date of that last transaction.

This query limits part tran types to be fed into another subquery to help determine the value of the parts sitting on the shelf:

This subquery is also fed into the one to determine inventory value and totals the number of parts in various bins:

This brings those two queries together along with part to get the description and part type based on a UD field and part type as the back up. It also sums up the total value. The links are all on PartNum.

Then I’m combining our max transaction date with the part value:

Top level simply cuts off those whose max transaction date is after the cutoff date:

[jkane (John Kane)]

Are you multi-site or multi-company?

[bordway (Bruce Ordway)]

@TDray

messy and 7 layers deep.

Yeah a little hard to follow… wonder if you can post a copy here?
For now. off the top of my head…

• I didn’t see a company field in your queries/subs - I always include company in my queries
• and with PartTran I always include index fields too… except where I can’t because of grouping/aggregation
  e.g. from data dictionary
  

[TDray (Todd Dray)]

We are single site and single company

[TDray (Todd Dray)]

tedOldInv061622.baq (69.4 KB)

I uploaded a copy of the BAQ. Sorry, I first did pictures because I didn’t think it would be helpful if someone were not on the same version of Epicor. Should have done both.

I’ll try adding the company field even though all of our files have the same company.
I’ll try adding the index fields where possible, but the majority of the queries involve grouping / aggregation or combining queries that do.

If either of those are fixes, I will post here but otherwise, any additional thoughts or directions to go / look are welcome.

Thank you very much to those who have responded.

[TDray (Todd Dray)]

So I was only able to add the index fields to a single one of the queries (subquery 2) and could not carry that any further or use it anywhere. I added them to the Display Columns for that single subquery and didn’t use them anywhere else or even move them further along because of aggregation issues.

So, using any kind of reason or logic, shouldn’t fix it. But this is Epicor. Problem solved. Thank you so much for your help. Very much appreciated.