conditional lookup date field

Question

I have a projects table with an items table as the child. The items table has an approval date field. I would like to set up a lookup field in the project log with the following conditions:

1. If none of the dates are filled in, it says "not started"
2. If only some of the dates are filled in, it says "in progress"
3. If all of the dates are filled in, it displays the latest date

If this is not possible, then I would like the lookup field to only display the latest date if all of the cells are filled in in the approval date field for that project.

quickbasecoachd · Answer

You will need to make a summary field on the relationship to count the # of records where the date is blank, and another summary field on the relationship to count the # of records with dates.

you will also nee a summary maximum of the date field.

Then on the parent record

IF(
[# items] =0, "No Items",
[# items] = [#Items without dates], "Not Started",
[#Items without dates]=0, [Max date],
[# items] > [#Items without dates], "In Progress", "error - check formula!")

howardfu · Answer

It looks like there's a conflict in the logic.[# items] could be &gt; [#items without dates] AND #items without dates] could = 0How do you resolve this?

quickbasecoachd · Answer

Did you test the  formula and try to make it fail vs you original question?  Can you make it fail.

howardfu · Answer

Ah it works now. I didn't know the order of the conditions in the formula mattered.Thank you!

howardfu · Answer

instead of "in progress", is there a way to have it show [# of items with dates] of [#&nbsp; of items with dates]?For example 1 of 3 or 1/3? to show that 1 of 3 items have dates?

Forum Discussion

conditional lookup date field

Related Content

Pipelines: Lookup newest record?

Check one field for multiple conditions

Conditional formula url

Snapshot Fields

Embedded Grid Edit Report Pre-filtered Conditional Dropdown