conditional lookup date field

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
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.

Photo of Howard Fu

Howard Fu

  • 294 Points 250 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,820 Points 50k badge 2x thumb
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!")



Photo of Howard Fu

Howard Fu

  • 294 Points 250 badge 2x thumb
It looks like there's a conflict in the logic.

[# items] could be > [#items without dates] AND #items without dates] could = 0

How do you resolve this?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,820 Points 50k badge 2x thumb
Did you test the formula and try to make it fail vs you original question? Can you make it fail.
Photo of Howard Fu

Howard Fu

  • 294 Points 250 badge 2x thumb
Ah it works now. I didn't know the order of the conditions in the formula mattered.

Thank you!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 63,166 Points 50k badge 2x thumb
Yes, exactly. When you have an F said processes play conditions in sequence until it finds the first one that is true and then it stops processing
Photo of Howard Fu

Howard Fu

  • 294 Points 250 badge 2x thumb
instead of "in progress", is there a way to have it show [# of items with dates] of [#  of items with dates]?

For example 1 of 3 or 1/3? to show that 1 of 3 items have dates?