My idea is to create a calculated field that’s associated with a checkbox on the dashboard tracker screen and if that checkbox is checked, then it will display a field, and if it’s not checked then it won’t display it.
So is it possible to control other calculated fields with a calculated field? Is it possible to display or hide fields using calculated fields?
It’s pretty easy to control calculated fields with other calculated fields, however show and hide fields is harder. Is empty good enough for what you are doing? You can do a case where if the checkbox field is true, then show the information you want and if false just show a blank.
I really wanted to reduce the amount of empty fields on this one. I was thinking a decent work-around to this would be to have the query on a few different tabs, and then within the summary settings just have the appropriate field displayed (depending on which tab)…
I didn’t know it was possible to control calc field with other calc fields to be honest… Is there any documentation/references I can look to for these? I’m sure displaying and hiding fields is above my knowledge level in calculated fields but it’s worth a shot!
In a BAQ you can make calculated fields, and then after you have created those, you can make more calculated fields that references the previously created ones. Thus controlling one with another. In the dashboard you can strategically show or hide intermediate things so the end use doesn’t see them.
Are you talking about dashboards or regular Epicor windows with customizations built into them?
Dashboards - not customizations.
Alright if you give me some more detail I can probably help. I won’t be able to hide the field though. That would be a higher level of customization that I can do. Although I believe it is possible, I think it will be buggy. You will have to deploy the dashboard as an assembly to do it, because I don’t think you get enough control in the run time dashboards to do that.
Let’s just start with calculating the fields that you need.
I’m basically trying to create the aged receivables report but within a dashboard view. Don’t ask me why I need to create this…
So most of my struggles have been with the calculated fields for the dates (1-30, Over 60 days, Over 90 days, etc.)
I have basic calc fields that are “(case when (InvcHead.InvoiceDate >= DATEADD (day, -30, GETDATE())) then InvcHead.DocInvoiceAmt else ‘0.00’ end)”
and for the rest it’s just 60, 90, and 120, instead of the -30… These work properly, but I don’t know how to code it so that if it’s within an amount of days from today then display it. E.g. If it’s over 120 days, it should only show it in that field, not in over 90 and over 60.
My idea with this was to create a calculated field that basically said “If set as true display CalculatedField30days otherwise stay hidden” Then bind that to a checkbox on the dashboard.
That’s hilarious, I was just messing with the same thing that we have here. Here is my example of our aged receivables that I use for a BAQ gadget. Mine just looks at the due date plus the terms, and if that calculated date is in the past, it shows up on the list. Check it out and see if it helps you.
I don’t have time today to get you the example of the case that you need, but basically make a field for days late (todays date - due date) and then make a calculated field for each of the columns, 1-30, over 60 etc, and make a case that if the calculated days late is with the window of that column, if shows your value, else 0.00. Then it should move the value to the appropriate column.
I’ll have some time tomorrow to work up an example.
AgedRecievables.baq (20.1 KB)
Ha! That is hilarious…
I tried a few things for the “todays date - due date” - but couldn’t get anything to work properly. I figured it was a datediff statement but it looks like this doesn’t work in BAQ Calculated Fields (was getting errors no matter what).
Caleb
It’s just a context thing. It can work. Thinking about it in my car ride home, you probably don’t need that intermediate field. Just do a date add from the due date + however many days and compare that to today’s date ( < or > ) and calculate right in you case then else statements.
An example case statement for between 30 and 60 days past due would be like this. You can adjust the numbers in the statement for the desired ranges. I attached my BAQ modified to add 2 columns, one for less than 30 and one for between 30 and 60 days over. You can play with the labels to better explain what it’s looking for but this should get you the grid that looks like the report. (note the BAQ has some filters set in it that you won’t need because it’s specific to out data, so just take those out).
case
when dateadd(day, 60, InvcHead.DueDate) > Constants.Today then 0.00
when dateadd(day, 30, InvcHead.DueDate) < Constants.Today then 0.00
else InvcHead.DocInvoiceBal
end
AgedRecievablesReportStyle.baq (20.3 KB)
Edit: I replaced this with a BAQ that has the right criteria in the calculated fields
Thanks for all the help Brandon. I’ve only gotten a few mins to check the calculated fields out this morning, but I will definitely work on this later today. I’m sure I’ll need to make some edits, so if I have any more issues I’ll be sure to comment again 
I finished the query for this. Unfortunately I didn’t get to do the original post’s idea of hiding and displaying fields - but I was able to get all the correct information thanks to Brandon’s help.
For future references here are the calculated fields I used:
Current/Future:
(case
when dateadd(day, 0, InvcHead.DueDate) >= Constants.Today then InvcHead.DocInvoiceAmt
else 0.00
end) as [Calculated_CurrentDays],
1-30 Days:
(case
when dateadd(day, 1, InvcHead.DueDate) > Constants.Today then 0.00
when dateadd(day, 30, InvcHead.DueDate) <= Constants.Today then 0.00
else InvcHead.DocInvoiceAmt
end) as [Calculated_ThirtyDays],
Over 30 Days:
(case
when dateadd(day, 60, InvcHead.DueDate) < Constants.Today then 0.00
when dateadd(day, 31, InvcHead.DueDate) > Constants.Today then 0.00
else InvcHead.DocInvoiceAmt
end) as [Calculated_OverThirtyDays],
Over 60 Days:
(case
when dateadd(day, 90, InvcHead.DueDate) < Constants.Today then 0.00
when dateadd(day, 61, InvcHead.DueDate) > Constants.Today then 0.00
else InvcHead.DocInvoiceAmt
end) as [Calculated_SixtyDays],
Over 90 Days:
(case
when dateadd(day, 120, InvcHead.DueDate) < Constants.Today then 0.00
when dateadd(day, 91, InvcHead.DueDate) > Constants.Today then 0.00
else InvcHead.DocInvoiceAmt
end) as [Calculated_NinetyDays],
Over 120 Days:
(case
when dateadd(day, 1365, InvcHead.DueDate) < Constants.Today then 0.00
when dateadd(day, 120, InvcHead.DueDate) > Constants.Today then 0.00
else InvcHead.DocInvoiceAmt
end) as [Calculated_OneTwentyDays]
Here’s a snippet of what the query looks like:
So now that we have that working, what else is it that you are trying to do? I don’t quite understand what you meant by the hide/show fields. Can you mark up a screen shot or something?
I’m just going to go with something like this (I need to figure out how to get it so that only the correct data will show depending on the tab you’re in) :
My idea was to have a few checkboxes in the Parameters/Tracker view and it would be something like “Current/Future CheckBox” - you would check that and only the Current/Future calculated field with display in the summary view. And then same with the rest of the calculated fields.
You can put in a filter for each tab. For example, in the 1-30 tab, filter by rows that don’t equal 0.
I do something for a totally unrelated application that I think would work for you. You would create a query that would list all of the customers and add up the number of invoices in each over due category, and then you can have the query you just made subscribe to whatever customer is selected in the top grid to show the individual invoices. Depending on if you want to see things to that filtered of a level.
You can do check boxes, you would just create a boolean calculated field in your BAQ that keys off if there is 0 or not in the row.
One problem with check boxes in the filtering is that you only get true or false, (you can’t make it to both). Which is a pain.
My way around that is to make a calculated field with case to set a string field with trueboth, or falseboth. Then use an epiultracombo instead of check boxes in your tracker and set the criteria to starts with, and the options to true false or both. The () acts as a wildcard and it will pick up both. You can use whatever words you want. I stole that idea from @ckrusen ( Dashboard input of Yes/No/Either for checkbox Tracker inputs - ERP 10 - Epicor User Help Forum ), because it’s genius.
Oh wow… Duh! I forgot all about the filters under the grid properties. I see no reason why that wouldn’t work…
I happened to need this for something else so, since I was looking it up anyways here is the SQL that will give you a difference on the number of days when added to a calculated field.
ex.
DATEDIFF( day , LastCostAdj.Calculated_LastCostAdj , Constants.Today )
what they mean
DATEDIFF( Interval*day,month,year, hour*, First date , second date )
link to the SQL that will give you all the options.
Is this what you did? What error were you getting?
