How can I check if items in one list exist in other lists?

How can I check if items in one list exist in other lists that I have in a model as well?

Tagged:

Best Answer

  • Here is an example of how to do this:

    Example Lists:
    Employees (What I'm testing) (List A)
    Grades (List B)
    Region (List C)
    Products (List D)
    Size (List E)

    Steps:
    -Insert a new module and make it apply to the list that you're testing (List A, or for me Employees) by putting that into Pages
    -Take out Time
    -Insert line items for each of the lists that you're checking against and label them with the List name
    -Format each of the line items as Boolean
    -Pivot the module so your list (Employees) is on the rows and line items are in columns (or the other way around)
    -Insert this formula into the line items: IF ISNOTBLANK(FINDITEM(Grades, NAME(ITEM(Employees))))

    Replace my list names with yours:
    List B = IF ISNOTBLANK(FINDITEM(List B, NAME(ITEM(List A))))
    List C = IF ISNOTBLANK(FINDITEM(List C, NAME(ITEM(List A))))
    List D = IF ISNOTBLANK(FINDITEM(List D, NAME(ITEM(List A))))
    List E = IF ISNOTBLANK(FINDITEM(List E, NAME(ITEM(List A))))

    This is especially useful as a workaround if you have one list that is a subset of another list and you want to check if list items are located in both areas.  Similarly, if you didn't need to know which List the items were a part of, you could combine these formulas into one and insert it into a new Boolean Line Item ("Existing?") by using OR in between the original formulas.

    Existing? = ISNOTBLANK(FINDITEM(List B, NAME(ITEM(List A)))) OR ISNOTBLANK(FINDITEM(List C, NAME(ITEM(List A)))) OR ISNOTBLANK(FINDITEM(List D, NAME(ITEM(List A)))) OR ISNOTBLANK(FINDITEM(List E, NAME(ITEM(List A))))

Answers

  • I tried this and received a message that the code was invalid.  I am checking to see if the codes in WBS Global Studies list are in the Cost Objects list in the WBS Global Study Code property.  I used the code below:
    IF ISNOTBLANK(FINDITEM(Cost Objects, NAME(ITEM(WBS Global Studies)))) 

    I also tried specifying the property to check
    IF ISNOTBLANK(FINDITEM(Cost Objects.WBS Global Study Code, NAME(ITEM(WBS Global Studies))))
    but still received a message that the code was invalid.

    Can you tell me where I am going wrong?
  • Hi Ellen,

    It looks like I forgot to add the end of the formulas to my original post.  Can you try using the same formula you had, but just add a true/false ending?

    So it would look like this:
    IF ISNOTBLANK(FINDITEM(Cost Objects, NAME(ITEM(WBS Global Studies)))) THEN TRUE ELSE FALSE

    Let me know if that works out for you.
  • That did it.  Thanks for the quick response.
  • Alec, I believe the formula with no IF THEN ELSE like
    =ISNOTBLANK(FINDITEM(Cost Objects, NAME(ITEM(WBS Global Studies)))) also works fine for boolean formatted lineitems