By using this website, you agree to our Terms of Use (click here)
Hi,
I have a generic inquiry where I need to filter on these conditions:
((ShipDate Between Today and Today+1) OR
(Day(ShipDate)=Friday AND
ShipDate Between Today and Today+3))
There is no way to create formulas on the conditions tab but there has to be some way to accomplish this seemingly simple scenario...? I feel like I must be missing something obvious.
Any ideas?
My solution would be to make a saved filter tab on the results screen that is shared and set to be the default.
Tell your boss I said you deserve a raise! 😉
That works by adding a hidden column using = Day(Today()) and filtering on that in the conditions. Still seems like they should allow formulas in conditions as you can in SQL.
@famousmortimer Thank you! The "Famous" in your name will surely work in my favor when I pass this along to my boss 😀 And FWIW, I agree that not being able to use formulas in conditions is a noticeable limitation of GIs especially because they are available in Report Designer, which has such a similar feature list. Glad you got your conditions working!
What about this idea?
Â
Do a cross join to a setup table that has a value that you know will never change:
Â
Put your formula in the Value 1 field on the CONDITIONS tab
=IIf(DayOfWeek([SOShipment.ShipDate])=6 and [SOShipment.ShipDate]>=Today() and [SOShipment.ShipDate]<=DateAdd(Today(),'d',5),0,1)
Â
As you can see in the formula, since the GLSetup.HoldEntry value is fixed and we know what it is, just design the IIf formula to return the fixed value that you are looking for. That will cause it to match records that you care about.
