Using EpiNumericEditor control Integer against the InvcHead_FiscalYear field.
If I check on GreaterThanOrEqualTo, nothing is returned. But if I change to LessThanOrEqualTo, then the data shows. Equal also fails, GreaterThan seems to work.
Is the numEditor control type set to Int?
Yes, the NumericType is Integer
Did you manually add the control via a customization?
Or was it automatically added as a filter field on a tracker?
Well, it was an existing Dashboard control created by someone previous. But if I add a new control on the tracker via Customize Tracker View and set the values I do get the same results though.
Note: I just tried having 2 controls so it would work like a range… and if I try 2017 to 2019, it works fine… if I change it to 2018 to 2019, nothing shows! Maybe that would help point to the issue somehow?
Check the properties of the Tracker. The General tab shows all the fields that are visible, and if theye are a Prompt (used as input for filtering). If it is a Prompt type, there will be values in the Condition and ControlName columns.
An example dashboard of mine that filters on the InvcHead_InvoiceDate field.
Any of those in use?
And FWIW - to allow a range for filtering, I would have added a calculated filed to duplicate the value of the field I’m filtering on. Then show this second field on the tracker, but set its condition to LessThanOrEqual.
For example:
No need to customize the DB. Especially since, I’ve found it difficult to alter a DB after it has been customized.
Yes the properties general tab
Also, occasionally when I click the box, this error message comes up… cannot always duplicate it though.
Column ‘LikeDataFieldTableID’ does not belong to table QueryTable.
Going with one “built-in” filter (via the Tracker), and one added filter (via customization), I’d think you want the condition in the Tracker to be GreaterThanOrEqual, and in the properties of your added field, set for LessThanOrEqual.
But I’m not really sure what will happen if two controls reference the same BAQ field. Implying that they’d be AND’d together might be a bad assumption.
I did find a dashboard where I did what you’re trying to do - one built-in and one custom control for a range. Both are for the same BAQ column, POHeader_OrderDate
The Tracker one has a condioton of >=
And the custom control is set like:
Thanks,
I had tried that, thinking the range might work where the “Equals” didn’t, but had the same problem. Let me ask you this, is there some known issue where the Fiscal Year And Period don’t work for the InvcHead table when used in a Dashboard? The BAQ for it, when taken to SQL Server seems to run just fine. And the failure seems to be only for certain years (2018 and 2019) and depends on what starting
periods or even date ranges (when checking on the invoice date). I’ve tried creating a new dashboard and ran into the same problem so it’s not a corrupt dashboard. I even get the same Epicor errors in this new one that I see on the old one.
Any other suggestions at this point?
FYI - Only the InvcHead has valid FY and FP values.
While the InvcDtl records have fields for FiscalYear and FiscalPeriod, they are not vaild. To get the FY and FP for specific InvcDtls, you’d need to link it to the TranGLC table.
Also, did you try adding a calculated column to the BAQ (to duplicate the FY field), so you have another column to use as a filter in the Tracker properties? Like what I did in post #7
Just got back to this issue again today.
Thanks for the info on the InvcDtl records… in this case we’re comparing to the InvcHead table so that’s not it.
Today, I added a calculated field for the Invoice Date and tried two date controls one pointed to the original invoice date in the BAQ and one to the new calculated date and it still doesn’t work. It seems still to work over some date ranges
(before 2018 or something) and after 2019, but if I say, for example Jan 1 2019, to Dec 31, 2019 – nothing shows.
Are there any other suggestions on things to try?
With nothing entered in any of the dashboard’s “filters” (prompt controls on the Tracker pane), does all the expected data show?
Also, after re-reading this thread’s title, I see you’re trying to do a comparison between FY’s. Are you trying to use the parameter on the dashboard for any of the BAQ’s sub-queries (other than the top level query)?
Like if you have a sub-query to summarize some data, and you want the Dashboards parameter to affect a the table criteria of a sub-query (a non-Top Level sub-query). I don’t know if a dashboard’s input drills down beyond the first level of the BAQ.
Yes when the inputs are blank it works fine. There are no subqueries.
Are you using any aggregate functions (SUM(), COUNT(), etc…) in any calculated field?
edit
One more thing, when the results are returned (when using blank params on the dashboard), do any of the fields you’re filtering on have nulls?
Well I really liked that possibility,
but no… I checked and there are just 2 calculated fields, one for state and the new one for the invoice date that I am now trying to use as a range instead of using the original financial year.
You’re probably on the right track though, for some data issue that the BAQ doesn’t handle for some reason though, yet works when doing all data and even when running in SQL.
You all have me hooked. I think you have tried everything I would think of.
My only advice might be, how is the query structured? Is InvcHead the first table?
I sure am curious about this one.
No, it’s like this…
from Erp.InvcDtl as InvcDtl
inner join Erp.InvcHead as InvcHead on
It will turn out to be something simple in the end.
I’ll look more into as I have free time, thanks again.
Well, this is solved and as suspected, it was data related. Still don’t know why the Query worked but the Dashboard fails, but by skipping those records in the BAQ, the Dashboard works fine now.