filtering for singular value

[GeraldDahl]

I'm trying to filter one gallery based on a record that it is missing and I can't seem to find a solution.

I have three buttons in order to create:

collectionA with four records:

• namea, nameb, namec, named

collectionB with four records:

• namea, nameb, namec, named

collectionC with eight records by collecting all the records from collectionA and collectionB:

• namea, nameb, namec, named, namea, nameb, namec, named

Next I delete 'named' from collectionA and then I clear collectionC and then I collect collectionC again with seven records:

• namea, nameb, namec, namea, nameb, namec, named

By applying an ascending sort onto collectionC the items appear as:

• namea, namea, nameb, nameb, namec, namec, named

The crux of what I'm trying to do is to determine how to reveal the singularly unique 'named' value in collectionC.

Applying 'Distinct' as part of a filter for collectionC results in:

• namea, nameb, namec, named
  • While it is true that all of these values appears at least once in the collection, I am trying to identify the value that only appears once in the collection (ie, named).

Similarly, sorting collectionC in descending fashion or even creating a dropdown with collectionC as its source doesn't help because there might be a case where, for example, namec is the singularly unique value and this could be hard to spot if the list were hundreds of items long and where the actual name of the singularly unique value was namewx or something that appeared much farther down in the list. Also there could be multiple items in the list that only appear as singular values and I don't want to have to spot them all manually.

I think that what I need help with is some way to compare my collectionA (after I have deleted 'named' from it (see italized portion above) with collectionB which still has 'named' in it. None of my efforts to filter collectionB towards that end have worked however. I think I will need to use forall and the <> operator but all I get is red squiggly lines. I imagine that if I can render the output of collectionB down to 'named' which is the singularly present item in collectionB that is not in collectionA, then I can go ahead and confidently delete 'named' from collectionC.

All of this has to do with trying to incorporate Google Contacts into a Power Apps project I am working on. If you would like more details about that I can share why this challenge is important to resolve in that context upon request.

Any help would be appreciated, please and thank you very much.

[PowerAddict · Accepted solution]

I have tried to replicate the same example as yours, let me know if it isn't. 

I have defined the following collections:

1. Collection A (I modified the name of this collection to make it easier to differentiate between the collections):

ClearCollect(CollectionA, {ColA: "namea"}, {ColA: "nameb"} , {ColA: "namec"})

2. Collection B:

ClearCollect(CollectionB, "namea", "nameb", "namec" , "named")

3. Collection C:

ClearCollect(CollectionC, "namea", "namea", "nameb", "nameb", "namec", "namec" , "named")

4. Collection D (in which I will capture the item that is in B but not in A): 

Clear(CollectionD); ForAll(CollectionB, If(IsBlank(LookUp(CollectionA, ColA = Value)),Collect(CollectionD, Value))) 

I then have another button to remove the needed item(s) from Collection C. The OnSelect property of that button is:

RemoveIf(CollectionC, Value in CollectionD)

Collection C then gets updated and named gets removed from it. 

Here is a working demo: 

Let me know if this helps. 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
https://thepoweraddict.com

[PowerAddict]

Glad I could help! Good luck with the rest of your app! Feel free to reach out in case of any questions!

Thanks,
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

[GeraldDahl]

Thank you Hardit for your wonderful reply.

I replicated everything you did and I got it to work with CollectionA through CollectionD as you described.

The next thing I did was to try to apply the concepts you showed me to the actual context I am using.

Here is a picture followed by code used for controls:

 GALLERIES

galGoogleContacts (the green gallery in the picture - bottom left)

• titleGCnames text is ThisItem.'gd$name'.'gd$givenName'.'$t'&" "&ThisItem.'gd$name'.'gd$familyName'.'$t'
• subtitleGCupdated text is ThisItem.updated.'$t'
• bodyGCid text is ThisItem.id.'$t'

galCDScontacts (the light coral gallery in the picture - top right)

• titleCDScontactsnames text is ThisItem.'First Name'&" "&ThisItem.'Last Name'
• subtitleCDScontactsupdated text is ThisItem.'Modified On'
• bodyCDScontactsid text is ThisItem.'Job Title'

galCollectionAmber (below the yellow and above the blue gallery)

• titleCollectionAmber text is CollectionAfirstname
• subtitleCollectionAmber text is CollectionAtime
• bodyCollectionAmber text is CollectionAid

galCollectionBlue (the blue gallery)

• titleCollectionBlue text is CollectionBfirstname
• subtitleCollectionBlue text is CollectionBtime
• bodyCollectionBlue text is CollectionBid

galCollectionCantelopeOrange (the Orange gallery)

• titleCollectionCantelopeOrange text is CollectionCfirstname
• subtitleCollectionCantelopeOrange text is CollectionCtime
• bodyCollectionCantelopeOrange text is CollectionCid

galCollectionDahliaPurple (the Purple gallery)

• titleCollectionDahliaPurple text is CollectionDfirstname
• subtitleCollectionDahliaPurple text is CollectionDtime
• bodyCollectionDahliaPurple text is CollectionDid

BUTTONS

btnMakeAmberGC

ClearCollect(CollectionA,ForAll(GoogleContacts.ListContactsV2().value,Patch(CollectionA,Defaults(CollectionA),{CollectionAfirstname:'gd$name'.'gd$givenName'.'$t',CollectionAlastname:'gd$name'.'gd$familyName'.'$t',CollectionAtime:updated.'$t',CollectionAid:id.'$t'})))

btnMakeBlueCDS

ClearCollect(CollectionB,ForAll(Contacts,Patch(CollectionB,Defaults(CollectionB),{CollectionBfirstname:'First Name',CollectionBlastname:'Last Name',CollectionBtime:'Modified On',CollectionBid:'Job Title'})))

btnMakeCanOrMixAmBl

ForAll(GoogleContacts.ListContactsV2().value,Patch(CollectionC,Defaults(CollectionC),{CollectionCfirstname:'gd$name'.'gd$givenName'.'$t',CollectionClastname:'gd$name'.'gd$familyName'.'$t',CollectionCtime:updated.'$t',CollectionCid:id.'$t'}));ForAll(Contacts,Patch(CollectionC,Defaults(CollectionC),{CollectionCfirstname:'First Name',CollectionClastname:'Last Name',CollectionCtime:'Modified On',CollectionCid:'Job Title'}))

btnMakeDahliaPurple

Clear(CollectionD); ForAll(CollectionB, If(IsBlank(LookUp(CollectionA, ColA = Value)),Collect(CollectionD, Value)))

I tried more of my own code for this button (see below) but I just got more red squiggles.

• Clear(CollectionD); ForAll(CollectionB,If(IsBlank((LookUp(CollectionA, CollectionA.CollectionAfirstname=CollectionB.CollectionBfirstname)),Collect(CollectionD,{CollectionDfirstname:CollectionBfirstname}))

Hardit, if you or anyone else can please help me to correct the code for the btnMakeDahliaPurple button I would very much appreciate it.

Thanks again for the assistance you offered to date and for any other suggestions you might have.

GERALD DAHL

[GeraldDahl]

Not sure if my follow up question got posted in the right spot but I’m hoping you might see it as part of or near this message - it contains my considered response to your solution as well as an image and a question about how to adjust the text on one of the buttons.

[PowerAddict]

Yes, I saw the question. Give me some time and I will get back to you.

Thanks,
Hardit Bhatia
https://thepoweraddict.com

[PowerAddict]

Hi @GeraldDahl

Sincere apologies for the super delayed response. Just want to make sure that you are still facing the issue (errors with the formula I suggested?).

Let me know and I can get started on this.

Thanks,
Hardit Bhatia
https://thepoweraddict.com

[GeraldDahl]

Hello Hardit,

Thank you for re-visiting this topic.

I've continued to try to resolve things on my own but I must admit that I'm still having the problem as described in my post from Monday with the picture having coloured galleries along with descriptions of controls.

Thank you for any further feedback you might be able to assist with and please feel free to ask for further clarifications if required.

With appreciation,

GERALD DAHL