cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CameronTCD
Helper II
Helper II

Cross reference two SharePoint lists to identify discrepancies.

I have two SharePoint Lists. 

"Mailing_list"

"Signee_list"

 

"Mailing_list" contains emails for everyone who has to sign the required document.

"Signee_list" contains emails for everyone who has signed the required document.

 

I'm looking to have a gallery/list that displays who's YET to sign the document, by cross referencing both lists.

 

It's been a while since I've used PowerApps extensively so a push in the right direction would be much appreciated.

Cheers.

4 REPLIES 4
FrostedFlake
Resolver I
Resolver I

Try to use an Excel sheet as junction table! Theres no foreign key in Sharepoint lists - I also use this workaround to join different tables.

 

ID_Mailing_listID_Signee_list
23112

 

 

May you do well!

 

 

RandyHayes
Super User III
Super User III

@CameronTCD 

Consider the following formula for your Gallery Items property similar to the following:

Filter(Mailing_List, email !(in Signee_List.email))

This will give you what you are looking for.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

 

Hi @RandyHayes 

 

I've tried a variation of solutions using ! but none return any information. 

These are the exact columns present...

 

Signature Record:

Document Title

Document Path

Signed By (this is the email)

 

RTS Mailing List:

EmailAddress 

 

 I've tried for example:

Filter(RTS Mailing List,!(EmailAddress = Signature_Record.Signed_By))

this states that the = is an invalid argument type.

 

With your solution:

Filter(RTS Mailing List,EmailAddress !(in

is where it falls apart. it won't accept in within the bracket.

 

Any ideas

RandyHayes
Super User III
Super User III

@CameronTCD 

Yes, my bad - in my haste of hand typing that, I put the paren in the wrong place.

Should have been:

Filter(Mailing_List, !(email in Signee_List.email))

See if that adjusts anything for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (94,077)