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

alec_judd

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

alec_judd

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))))

PrevContributor

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?

alec_judd

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.

PrevContributor

That did it.  Thanks for the quick response.

PrevContributor

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