Check for Data & Time for all records

Question

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

LauraThacker · Accepted Answer

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

Sign up

Login to the community