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
MarkShnier__You
Qrew Legend
Qrew LegendIt'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
- MarkShnier__You
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