Discussions

Expand all | Collapse all

conditional lookup date field

  • 1.  conditional lookup date field

    Posted 01-14-2019 19:11
    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.



  • 2.  RE: conditional lookup date field

    Posted 01-14-2019 20:28
    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!")





  • 3.  RE: conditional lookup date field

    Posted 01-17-2019 01:20
    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?


  • 4.  RE: conditional lookup date field

    Posted 01-17-2019 02:51
    Did you test the formula and try to make it fail vs you original question? Can you make it fail.


  • 5.  RE: conditional lookup date field

    Posted 01-18-2019 00:09
    Ah it works now. I didn't know the order of the conditions in the formula mattered.

    Thank you!


  • 6.  RE: conditional lookup date field

    Posted 01-18-2019 00:32
    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


  • 7.  RE: conditional lookup date field

    Posted 01-18-2019 00:12
    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?