Forum Discussion

HowardFu's avatar
HowardFu
Qrew Cadet
6 years ago

conditional lookup date field

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.

6 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    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's avatar
    HowardFu
    Qrew Cadet
    6 years ago
    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?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Did you test the formula and try to make it fail vs you original question? Can you make it fail.
  • HowardFu's avatar
    HowardFu
    Qrew Cadet
    6 years ago
    Ah it works now. I didn't know the order of the conditions in the formula mattered.

    Thank you!
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      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
  • HowardFu's avatar
    HowardFu
    Qrew Cadet
    6 years ago
    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?