cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seraph787
Level: Powered On

Remove Gallery items where field matches any value of a field in another table

I have a certification gallery (which lists certifications that have expiration dates) and another gallery with tasks which are what I am doing to update expired certifications.

 

When I enter a task in the task gallery, I reference the certification ID from the certification gallery.

 

I'm trying to filter out my certification gallery so that it won't show items which have been referenced in the task gallery.  Below is what I've tried.

 

Filter(cpaCerts, Not(ID in cpaTracking.certID))
  • cpaCerts is my SharePoint list of certifications along with the expiration dates for those certifications.
  • cpaTracking is my SharePoint list of tracking actions I'm taking.  For each row, I enter the certID that matches the certID of cpaCerts that the tracking action is for.

I get an error on hovering over ID that says: "Cannot automatically convert this Number value to a Record."

 

I've tried making it like a record {Value: ID} and I've tried breaking cpaTracking.certID down into a Number value but I'm not sure what I'm doing wrong.  If it helps, the certID field in my Sharepoint cpaTracking list is a lookup field but when I put a period at the end of cpaTracking.certID, it doesn't suggest any sub array options.

 

To further elaborate:

cpaCerts table

ID   Expiration

1     1/21/2018

2     4/1/2019

3     5/30/2020

 

cpaTracking table

trackID    certID    trackingAction

52            1            Sent notification letter

53            3            Made phone call

 

I want my gallery to show

Gallery1 (Items = cpaCerts)

ID   Expiration

2     4/1/2019

 

Thanks in advance for your insights and help.

-Michael

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Remove Gallery items where field matches any value of a field in another table

Hi @seraph787,

Which type of the certID column within your cpaTracking list? Is it a Lookup type column?

 

I assume that the certID column is a Lookup type column within your cpaTracking list, I have made a test on my side on my side, please take a try with the following workaround:12.JPG

 

11.JPG

Set the OnVisible property of the first screen of my app to following formula:

ForAll('20181002_cpaTracking'.certID,Collect(certIDCollection,certID.Value))

On your side, you should type:

ForAll(
cpaTracking.certID,
Collect(certIDCollection,certID.Value)
)

Set the Items property of the Gallery control to following formula:

Filter('20181002_cpaCerts',Not(ID in certIDCollection))

On your side, you should type the following formula:

Filter(cpaCerts,Not(ID in certIDCollection))

In addition, the Delegation warning is not an error, it just means that there are something could not delegated within the formula that you provided. Currently, the in operator could not be delegated within the SP list data source.

More details about the Delegation warning within PowerApps, please check the following article:

Delegation

 

In order to get rid of the Delegation warning issue within your app, please take a try with the following workaround:

Set the OnVisible property of the first screen to following formula:

ClearCollect(cpaCertsCollection,cpaCerts);
ForAll(
cpaTracking.certID,
Collect(certIDCollection,certID.Value)
)

Set the Items property of the Gallery control to following formula:

Filter(cpaCertsCollection,Not(ID in certIDCollection))

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
seraph787
Level: Powered On

Re: Remove Gallery items where field matches any value of a field in another table

Nevermind, I went the route of eliminating all SharePoint lookups (they were causing quite a few issues).

Doing so caused my code to work.  On a similar SharePoint note: does anyone know if there is a setting or something that I can tweak to get rid of the below error warnings?

Delegation warning. The “LookUp” part of this formula might not work correctly on large data sets.  The data source might not be able to process the formula and might return an incomplete data set.  Your application might not return correct results or behave correctly if the data set is incomplete.

So far there have been no such problems as forewarned but I am sure it will happen as my data set grows, so if someone knows a remedy or some advice it would be greatly appreciated.  Note, I've only started getting this Delegation Warning now since importing my data sets from Excel spreadsheet tables that I was previously connecting to using the Onedrive excel connector so I'm thinking SharePoint might do something different than Excel in this regard?

 

Super User
Super User

Re: Remove Gallery items where field matches any value of a field in another table

Hi @seraph787,

You will always get a delegation warning if you use a Choice or Lookup column in Sharepoint.  Kudos to you on getting rid of these types of columns.  I had the same experience as you did.  I personally think they were meant for Sharepoint and not Powerapps. I haven't found any downsides to changing the type in Sharepoint to Single line of text or Number types and have not lost data because of it.  I suggest going back into Sharepoint and changing the Lookup column to single line of text. You may want to back up your list just in case. 

Community Support Team
Community Support Team

Re: Remove Gallery items where field matches any value of a field in another table

Hi @seraph787,

Which type of the certID column within your cpaTracking list? Is it a Lookup type column?

 

I assume that the certID column is a Lookup type column within your cpaTracking list, I have made a test on my side on my side, please take a try with the following workaround:12.JPG

 

11.JPG

Set the OnVisible property of the first screen of my app to following formula:

ForAll('20181002_cpaTracking'.certID,Collect(certIDCollection,certID.Value))

On your side, you should type:

ForAll(
cpaTracking.certID,
Collect(certIDCollection,certID.Value)
)

Set the Items property of the Gallery control to following formula:

Filter('20181002_cpaCerts',Not(ID in certIDCollection))

On your side, you should type the following formula:

Filter(cpaCerts,Not(ID in certIDCollection))

In addition, the Delegation warning is not an error, it just means that there are something could not delegated within the formula that you provided. Currently, the in operator could not be delegated within the SP list data source.

More details about the Delegation warning within PowerApps, please check the following article:

Delegation

 

In order to get rid of the Delegation warning issue within your app, please take a try with the following workaround:

Set the OnVisible property of the first screen to following formula:

ClearCollect(cpaCertsCollection,cpaCerts);
ForAll(
cpaTracking.certID,
Collect(certIDCollection,certID.Value)
)

Set the Items property of the Gallery control to following formula:

Filter(cpaCertsCollection,Not(ID in certIDCollection))

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,445)