Filter/Search Data table based on field in currently viewed item

[entertain89]

Hello, I have two related questions.

1) I'm customizing a sharepoint form for a list (list a) in powerapps and want to display data in a data table from a second list (list b) based upon a reference number in one of the fields in "list a" when I'm viewing an item from "list a". I hope this makes sense.

For example: I have records in "list a" with a field called Reference Number (lets say the number is 12345), the field type is "single line of text". "List b" may have one or more records that also share this reference number in a lookup type field. When I click the linked item reference number in "list a" I want all records with that matching reference number returned from "list b" and displayed in a data table at the bottom of the form.

I've got the table inserted and the columns I want are in place but I'm struggling with how to reference the reference number (12345) of the record currently being viewed in the "list a" form.  I also want to have one of the returned fields in the table to be a clickable link to display the record or records from table B.

This is my filter query 

Filter('List B', 'Reference Number' = ThisItem.'DataCardValue1')

*DataCardValue1 is the name of the actual text field in the form

  I'm getting a record type comparison error but I'm unsure how to do this properly and my random googling isn't turning up a solution.

2) as part of question 1's data table query above I'm retrieving several columns from list b, one of which is a "choice" type column called "Reason Code". I'm getting the below error, how do I fix this so I can get the one or more choices that may have been selected in the column?

Thanks

[Devvj]

Hi,
for question 2 you could concat the selected values and show them as a list, like such:

Concat(ThisItem.MultiSelectChoiceColumn.Value, Value, ",")

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

[entertain89]

That seems to have resolved the error from flow checker. Currently no data is being retrieved into the table because of my broken filter statement in question 1 so once I figure that out I'll reply back and let you know if this does in fact fix my second issue.

[Devvj]

@entertain89 
No sure since i rarely use forms, but I think you need to add a ".Text" to the DataCardValue-field to get the actual text of the Input-field.

Filter('List B', 'Reference Number' = ThisItem.DataCardValue1.Text)

[entertain89]

That doesn't fix it unfortunately. To compare them PowerApps says they need to be of the same type:

Is there a way to convert their type as part of the formula then?

[Devvj]

You can cast both value to text using the Text()-function.

Filter('List B', Text('Reference Number') = Text(DataCardValue1.Text))

Have you tried removing "ThisItem" before the datacardvalue btw?

[entertain89]

Hi @Devvj yes I removed ThisItem from the data card value as I'm now using your suggested filter: 

Filter('List B', Text('Reference Number') = Text(DataCardValue1.Text))

I've got some new errors now and it still isn't working unfortunately. 

[entertain89]

Ok I tweaked it a bit and it appears to be working!

Filter('List B',Text('Reference Number'.Value) = Text(DataCardValue1.Text))

A few more follow questions if you would be so kind as to continue to assist me.

The reason code field works but if there are multiple reason codes they easily run out of view. I tried inserting a new line character after each code output in your concat statement but the data table seems to still restrict it to one line.

Is there any way to get the row to expand downward to accommodate multiple reason codes? 

Then my only other question, is it possible to make one of the fields in the data table clickable so it can open the item view for the records displayed from list B?

[Devvj]

@entertain89 
What I know of there is no current functionality to "Word Wrap" a column in a data table unfortunatly.

As for clickable rows i think you can use the syntax to get the record selected

DataTable.Selected

For example, I have a small datatable and a label to display the title of the selected item to the left.
It works when i set the text property on the label to "DataTable1.Selected.Title

Hope that solves the problem!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

[entertain89 · Accepted solution]

Apologies for the delay in my reply, got busy with some other stuff.

I suppose if I can get the item from the other list I can just drop the reason code from the table all together. Users can just click the linked item if they want to see the codes.

That's definitely handy, but could I use it to display data that isn't in a column on the data table? I don't think I was clear on what I was trying to accomplish.

What I want is to be able to make each entry have a hyperlinked column of my choosing that when clicked opens up the sharepoint item view form for that record from list b. For example:

The responsible person column is clickable (name of person censored)

and when you click it it takes you to the list b item view: