Forum Discussion

AndrewFry's avatar
AndrewFry
Qrew Assistant Captain
6 years ago
Solved

Check for Data & Time for all records

I have a field formula - text.
I am trying to write a formula to check and see if all Assignments have a Date Scheduled and a calendar time.

If they do, then I need the Text in the field to be a "YES" otherwise a "NO"

The number of assignments can vary, but regardless of the number of assignments, I need to ensure that all assignments have a scheduled date and a calendar time.

I have to incorporate this part of the formula into a bigger formula that I am working out, but I am stuck on this portion of it because of the fact that there could be 1 assignment or there could be many assignments and I am not too well versed yet in determining how to go about catching all assignments.


Based upon the above, the results in the field would be "NO", because there is no Calendar Time for Test, Jeff, or Date Scheduled for Test, Sarah.

Both Date Scheduled and Calendar Time need to be filled in for every assignment in order to populate "YES".

Any pointers, thoughts, ideas, etc that any of you can provide is greatly appreciated!!

------------------------------
Andrew
------------------------------
Formulas and functions
reports and charts
  • LauraThacker's avatar
    LauraThacker
    6 years ago
    In order to determine whether all child records have the required data populated; you need to build two summary fields created in the relationship between this table and the parent-table.  You then need a formula-text field to evaluate the two values in the parent-record.

    1.  Summarize the count of child records
    2.  Summarize the count of child records where the [Date Scheduled] is not empty and [Calendar Time] is not empty
    3.  Build a formula-text field [All Assignments Complete?] to determine if these two summary-count values are equal and generate a Yes or No output if they do not match.
    If([# of Child Records] = [# of Complete Child Records] , "Yes" , "No" )

    4.  Add a Lookup field in the relationship of this new formula-text field [All Assignments Complete?] so that you can see this output value in each Assignment record
    5.  You can then simply display this formula-field in your reports in your Assignments table.  As the Assignments are completed properly; the lookup-value will change accordingly.

    You can also take this a step further by adding a formula-text field in the Assignments table; which will give you greater control over report filtering and sorting/grouping.  Create a formula-text field called [Assignment Complete?]

    Case(true,
[All Assignments Complete?] = "Yes", "All Assignments Complete",
not IsNull([Date Scheduled]) and not IsNull([Calendar Time]),"Assignment Complete",
IsNull([Date Scheduled]) and IsNull([Calendar Time]), "Assignment Incomplete",
IsNull([Date Scheduled]),"Missing Date Scheduled",
IsNull([Calendar Time]),"Missing Calendar Time"
)


    ------------------------------
    Laura Thacker (IDS)
    laura@intelligentdbs.com
    (626) 771 0454
    ------------------------------
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    6 years ago
    In order to determine whether all child records have the required data populated; you need to build two summary fields created in the relationship between this table and the parent-table.  You then need a formula-text field to evaluate the two values in the parent-record.

    1.  Summarize the count of child records
    2.  Summarize the count of child records where the [Date Scheduled] is not empty and [Calendar Time] is not empty
    3.  Build a formula-text field [All Assignments Complete?] to determine if these two summary-count values are equal and generate a Yes or No output if they do not match.
    If([# of Child Records] = [# of Complete Child Records] , "Yes" , "No" )

    4.  Add a Lookup field in the relationship of this new formula-text field [All Assignments Complete?] so that you can see this output value in each Assignment record
    5.  You can then simply display this formula-field in your reports in your Assignments table.  As the Assignments are completed properly; the lookup-value will change accordingly.

    You can also take this a step further by adding a formula-text field in the Assignments table; which will give you greater control over report filtering and sorting/grouping.  Create a formula-text field called [Assignment Complete?]

    Case(true,
[All Assignments Complete?] = "Yes", "All Assignments Complete",
not IsNull([Date Scheduled]) and not IsNull([Calendar Time]),"Assignment Complete",
IsNull([Date Scheduled]) and IsNull([Calendar Time]), "Assignment Incomplete",
IsNull([Date Scheduled]),"Missing Date Scheduled",
IsNull([Calendar Time]),"Missing Calendar Time"
)


    ------------------------------
    Laura Thacker (IDS)
    laura@intelligentdbs.com
    (626) 771 0454
    ------------------------------
    • AndrewFry's avatar
      AndrewFry
      Qrew Assistant Captain
      6 years ago
      Laura, that is a fantastic way of doing it. I hadn't even considered creating a summary field and using that to do my comparison.

      And then taking it a step further by providing a more indepth way of showing which part is missing, that is great!!

      Thanks for the pointers and assistance. I really appreciate it.

      I have only been using QB for a few months now and am still trying to capture all of the possibilities that QB offers.


      ------------------------------
      Andrew Fry
      ------------------------------
      • LauraThacker's avatar
        LauraThacker
        Qrew Captain
        6 years ago
        You're welcome; happy to help.

        ------------------------------
        Laura Thacker (IDS)
        laura@intelligentdbs.com
        (626) 771 0454
        ------------------------------