Filter DataTable based on DataField

[TWolsten]

Hi all,

Hope you can help as I have tried to find a solution on the Boards but I must be either missing something or just having a brain freeze.

So I have two lists, bp_changelog and bp_live.

On the bp_live list ViewForm there is a ID Field (DataCard1).

I would like to pull the ID Field and filter the DataTable (bp_changelog) below with just the relevant IDs based on the bp_live ID Field.

I currenlty have:

Filter(bp_changelog, "DataCard1.Value" in Lower(Title) )

As my formula but the "DataCard1.Value" is the part I am struggling with as if I put a value (e.g "760-1") instead the list filters nicely.

Sorry for the probably simple issue but help would be greatly appreicated!

[Jeff_Thorpe]

@TWolsten,

If your list is under 2,000 you could try the new feature that lets you increase the delegable row limit up to 2,000.

PowerApps data row limit for non-delegable queries

[ThatAPIGuy · Accepted solution]

@TWolsten

Do you have any specific reason of using the data table instead of a gallery?

If not, you can format the gallery to almost like a data table -

This way you can use filter and it will be delegable.

[Jeff_Thorpe]

I have been playing around with the Data Table and I am able to get fileter to work. Are you saying the Filter formula isn't returning any values or that you want to use search but search only works on the first 500? Since the data source is SharePoint you will want to use StartsWith or "=" to avoid 500 limit.

Here is how you can use the StartsWith inside of a Filter:

Filter(bp_changelog, StartsWith(Title, DataCardValue1.Text))

 I set-up a test app and the other formula is working for me.

Filter(bp_changelog, DataCardValue1.Text = Title)

[TWolsten]

Thanks vivekb!

Love the work around for the DataTable, I didnt realise you could customise the Gallery to look like that.

I have amanged to get it working in Gallery view and the DataTable now.

Thanks again.

[TWolsten]

That is odd the:

Filter(bp_changelog, DataCardValue1.Text = Title)

formula returns no values when I know there are matching values... very odd!

The:

Filter(bp_changelog, StartsWith(Title, DataCardValue1.Text))

forumla works but say I have 760-1, as the DataCardValue1, I also get 760-2, 760-3 & 760-4 for some reason?

Very odd, I have switched over to a Gallery view as this seems to be much easier to configure and the:

Filter(bp_changelog, DataCardValue1.Text = Title)

 forumla works perfectly with the Gallery.

Thanks again for all the help and I have no idea what is wrong with my DataTable, I am just glad the Gallery works 

[Anonymous]

Good Proposal! How did you get the vertical columns?

[RyanXExner]

FYI - We got this to work by enabling the newly released Updated Controls in the app settings: https://powerapps.microsoft.com/en-us/blog/improved-data-table-control-with-column-formulas/

After enabling, we were able to add the filter operation to get the value from another list.  Unfortunately, SharePoint doesn't return lookups of a lookup, so we implemented this as a workaround.

Hope this helps!

[Keyur-Alstom]

✔️Yes, Named Formula will solve this issue with Filter!!! (Delegable) 
Named Formula: FxData=Filter(Source,StartsWith('ColumnName',TextInput.Text));
DataTable Items: FxData

[Keyur-Alstom]

✔️ Named Formula will solve this issue with Filter!!! (Delegable) 
Named Formula: FxData=Filter(Source,StartsWith('ColumnName',TextInput.Text));
DataTable Items: FxData