Formula checkbox from report filters
Thank you all for considering my post. I am trying to create a checkbox formula that mirrors this set of report filters: I am having a Dickens of a time to get the same results as the r...
Forum Discussion
It's lot of typing so i will do this in pseudo code, but I will endeavour to get the ANDs and ORs correct and the parentheses.
and
[Riversmart home]
and
(isnull([adit date]) or [audit date] < [date reaudit requested])
and
not [test account]
and
[site owner public user]
and
(not [rsr auto enroll] or [rsr autoenroll app date ] < [ date reaudit requested])
and
([remove from waitlist]=0 or not isnull([date reaudit requested]))
and
(not [rsh import] or not isnull([date audit requested]))
Please post back with a copy and paste of the code if it does not work and you can't fix it, ad I will help edit it.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
and
[Riversmart home]
and
(isnull([adit date]) or [audit date] < [date reaudit requested])
and
not [test account]
and
[site owner public user]
and
(not [rsr auto enroll] or [rsr autoenroll app date ] < [ date reaudit requested])
and
([remove from waitlist]=0 or not isnull([date reaudit requested]))
and
(not [rsh import] or not isnull([date audit requested]))
Please post back with a copy and paste of the code if it does not work and you can't fix it, ad I will help edit it.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Mark,
My difference in filtering the formula in a test report is 45 records more than the report with the filter sample provided. This result is a whole lot better than I was ever able to generate. I am going to do some data reconciliation and find the differences. Here's the code. If you see something, let me know.
If([RiverSmart Homes]
and
(IsNull([Audit Date]) or [Audit Date] < [Date re-audit requested])
and
not [Test account]
and
[Site Owner - Public User]
and
(not [RSR auto-enroll] or [RSR Auto-Enroll Application Date] < [Date re-audit requested])
and
([Remove from waitlist]=0 or not IsNull([Date re-audit requested]))
and
(not [RSH Import] or not IsNull([Date re-audit requested])),true,false)
and
(IsNull([Audit Date]) or [Audit Date] < [Date re-audit requested])
and
not [Test account]
and
[Site Owner - Public User]
and
(not [RSR auto-enroll] or [RSR Auto-Enroll Application Date] < [Date re-audit requested])
and
([Remove from waitlist]=0 or not IsNull([Date re-audit requested]))
and
(not [RSH Import] or not IsNull([Date re-audit requested])),true,false)
On a personal note, I have been an independent QB developer for about 17 years now. I can't tell you the number of times you have saved my bacon and provided guidance with your solutions to QB Community posts. Just want to say thank you.
Brian
Glad to hear I've been helpful over the years.
I can't see what wrong... when you reconcile we will see an example of a difference and go from there.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------- Mark,Here is a sample of reconciled records that formula is not filtering out.Appears that the formula is having a difficult time processing dates in these lines:If([RiverSmart Homes]
and
(IsNull([Audit Date]) or [Audit Date] < [Date re-audit requested])
and
not [Test account]
and
[Site Owner - Public User]
and
(not [RSR auto-enroll] or [RSR Auto-Enroll Application Date] < [Date re-audit requested])
and
([Remove from waitlist]=0 or not IsNull([Date re-audit requested]))
and
(not [RSH Import] or not IsNull([Date re-audit requested])),true,false)- Mark,All is good. Thanks for your help!Brian
