Quickbase Discussions

 View Only
  • 1.  Formula checkbox from report filters

    Posted 14 days ago
    Thank you all for considering my post.  I am trying to create a checkbox formula that mirrors this set of report filters:

    image.png
    I am having a Dickens of a time to get the same results as the report displays. Create a new report where [Formula Checkbox] = true and delivers the same results as these filters.  I need to pass the Formula check box back to other table(s).  Many thanks.


  • 2.  RE: Formula checkbox from report filters

    Posted 14 days ago
    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
    ------------------------------



  • 3.  RE: Formula checkbox from report filters

    Posted 14 days ago
    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)

    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





  • 4.  RE: Formula checkbox from report filters

    Posted 14 days ago

    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
    ------------------------------



  • 5.  RE: Formula checkbox from report filters

    Posted 14 days ago
    Mark,
    Here is a sample of reconciled records that formula is not filtering out.  

    image.png

    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)





  • 6.  RE: Formula checkbox from report filters

    Posted 14 days ago
    Mark,

    All is good.  Thanks for your help!

    Brian