2 Replies Latest reply on Mar 1, 2013 1:11 AM by Xavier P

# Checking presence across multiple list

I have 3 lists, A,B and C - attached.

I need to know who is on list A only, A+B, A+C and B+C

I ran this calculation with booleans but it does not seem to be yielding the expected results:

IF [A2B] AND ISNULL([B List (Checklist.xlsx)].[B2C]) THEN "Also on B"

ELSEIF [A2C] AND ISNULL([B List (Checklist.xlsx)].[B2C]) THEN "Also on C"

ELSEIF ([B List (Checklist.xlsx)].[B2C]) THEN "On B+C"

ELSE 'A list only'

END

Anyone can see why?

Xavier

• ###### 1. Re: Checking presence across multiple list

Hi Xavier,

C list needs to be the primary source since it has the most records. Then, the linking fields need to be on the view as well (place email B and email A on the level of detail shelf).

Then, try a calculation similar to the following:

if isnull(ATTR([B List (Checklist.xlsx)].[email B])) and max([email C])=max([email C]) and isnull(ATTR([A list (Checklist.xlsx)].[email A])) then 'C only'

elseif  ATTR([B List (Checklist.xlsx)].[email B])=ATTR([B List (Checklist.xlsx)].[email B]) and ATTR([A list (Checklist.xlsx)].[email A])=ATTR([A list (Checklist.xlsx)].[email A]) and max([email C])=max([email C]) then 'All 3 Lists'

elseif isnull(ATTR([B List (Checklist.xlsx)].[email B])) and max([email C])=max([email C]) then 'A + C Only'

elseif max([email C])=max([email C]) and isnull(ATTR([A list (Checklist.xlsx)].[email A])) then 'B + C Only'

end

Hope this helps!

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: Checking presence across multiple list

Thanks Tracy! Appreciate you taking the time to review.  I have another question though, minister@britain.com is only on B list and as such not showing on my C list - also my master list.

I guess I need to create an extract with multiple tables in order to have all email addresses from each list - not a must-have but could be nice to.  If so, I also guess I need to change the calculation - will try that myself.  Note to me: was not aware of Max function.