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 "...
Formulas and functions
reports and charts
  • Laura_Thacker's avatar
    Laura_Thacker
    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
------------------------------
Laura_Thacker's avatar
Laura_Thacker
Qrew Commander
6 years ago
You're welcome; happy to help.

------------------------------
Laura Thacker (IDS)
laura@intelligentdbs.com
(626) 771 0454
------------------------------
  • AndrewFry's avatar
    AndrewFry
    Qrew Assistant Captain
    6 years ago
    Laura,

    One other quick question related to the above, if you don't mind me picking your brain ....

    I have setup the various summary fields in the Jobs table, pulling in information from the child table, Assignments.

    I also setup the formula text field in the Jobs table to evaluate if the two summary fields are equal or not.

    I now need to have the formula text field and its results from the Job table available in the Work Orders table, Work Orders being the Parent and Jobs being the Child.

    I have my main formula in a formula text field in the Work Orders table but need to reference the value of the formula text field in the Jobs table.

    I can't seem to get the formula text field from the Jobs table to be a selectable item from within the formula text field of the Work Orders table ... if that makes sense ....


    ------------------------------
    Andrew Fry
    ------------------------------
    • Laura_Thacker's avatar
      Laura_Thacker
      Qrew Commander
      6 years ago
      For this, you can use the new Summary Combined Text field.

      It will summarize unique values of the field you choose from the children to the parent-record.  Since, in this case, you have 1 Job for a single Work Order; you will only get one results.

      However, unless you are going to use a formula field with the value ToText([Summary Combined Text Output]); you should build a separate formula-text field to convert the summary-combined-text output because for comparing or reporting; you might find it easier to read, filter, group/sort.  The summary-combined-text field is great; except that it can be more difficult to use in common ways such as reporting because it shows as a List(Text) style output. 

      Call me if you are stuck.

      ------------------------------
      Laura Thacker (IDS)
      laura@intelligentdbs.com
      (626) 771 0454
      ------------------------------
      • AndrewFry's avatar
        AndrewFry
        Qrew Assistant Captain
        6 years ago
        I think I understand.

        Just as clarification, I will have multiple assignments to a job, and multiple jobs to a work order.

        I do think that your recommendation will still work even though I will have multiple jobs.

        I think that for ease of implementation for the time being, I am going to use ToText([Summary Combined Text Output]).

        Once I get things under controll a bit more, I will make it more elegant by implementing what you recommended by converting the summary combined output.

        Thanks again Laura!! I appreciate your insight.

        ------------------------------
        Andrew Fry
        ------------------------------