Search in nested table

(0) Share

Report

Posted on by mrQ

482

Dear community

In my table "integration" there is a nested table (not record!) called "managersNested". In my gallery, I have added the following as for the Items property:

SortByColumns(
 Filter(
 AddColumns(
 integration,
 "managersNested",
 Filter(
 managers,
 Title = integration[@Title],
 (role = "390" || role = "420" || role = "932")
 )
 ),
 StartsWith(Title, textinput.Text) || 
 StartsWith(Street, textinput.Text) || 
 StartsWith(Zip, textinput.Text) || 
 StartsWith(Place, textinput.Text) 
 ),
 "Title"
)

I would now like to also be able to return entries in mentioned gallery above ("integration" data source) when searching for managersNested.UserId and was wondering how you would approach this?

Simply adding another "StartsWith(managersNested.UserId, trextinput.Text)" doesn't work, unfortunately 😞

Thanks in advance

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @mrQ ,

You can add another AddColumns function out of the one you have already:

SortByColumns( Filter( AddColumns(AddColumns( integration, "managersNested", Filter( managers, Title = integration[@Title], (role = "390" || role = "420" || role = "932") ) ),"UID",ThisRcord.managersNested.UserId), StartsWith(Title, textinput.Text) || StartsWith(Street, textinput.Text) || StartsWith(Zip, textinput.Text) || StartsWith(Place, textinput.Text) || StartsWith(UID, trextinput.Text) ), "Title" )

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
mrQ 482 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-jefferni

Thanks for your response.
Unfortunately, this doesn't work as I get the following error message:

Do you know how to work around this issue?

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @mrQ ,

Please post the error message along with the formulas within your formula bar as the screenshot so that I could figure out which StartsWith is not correct.

I found a typo within my last post reply, but it should not cause this kind of error.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
mrQ 482 on at

Like (0)

Report
Copy link

Link copied!
Hi @v-jefferni

The error appears for the StartsWith with the UserId.
I saved everything into a collection to make sure its not related to my data source:
ClearCollect( colTest, SortByColumns( AddColumns( AddColumns( integration, "managersNested", Filter( managers, Title = integration[@Title], (role = "390" || role = "420" || role = "932") ) ), "UserEmail", ThisRecord.managersNested.'User mail' ), "Title" ) )

Then I filter the collection with StartsWith:
When hovering over the managersNested.'User mail' I see the this error message:

Kind regards,

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @mrQ ,

For later threads you'd better post all the related formulas in text so others will be able to modify on it easily. The filter function on your end will be:

Filter(colTest, StartsWith(Title, TextInput1.Text) || StartsWith(UserEmail, TextInput1.Text) )

You have named the column "UserEmail" when you created the collection using the AddColumns function.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
mrQ 482 on at

Like (0)

Report
Copy link

Link copied!
Hi @v-jefferni

Thanks for your response.
I've sent screenshots because of your request 😊.
@v-jefferni wrote:
Please post the error message along with the formulas within your formula bar as the screenshot so that I could figure out which StartsWith is not correct.
the "managersNested" is the same as the additional "AddColumns" that you have suggested, except that this table includes also other data. From a filtering perspective this shouldn't make a difference.

As even after changing "managersNested" with "UserEmail" I get the same error message.
Filter( colTest, StartsWith(Title, TextInput1.Text) || StartsWith(UserEmail.'User mail', TextInput1.Text) )

I think it is because the nested table is not a record, but a table. Though I do not know how to get it to work with a nested table.

Kind regards,

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @mrQ ,

UserEmail is the column added by AddColumns function and in StartsWith you will need this column only, 'User mail' has already been replaced. So use the formula I posted in the last reply is enough, no need to change it.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
mrQ 482 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-jefferni

same problem:

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @mrQ ,

Sorry, I got the issue after reviewing the clearcollect. Please try below:

Filter(colTest, StartsWith(Title, TextInput1.Text) || TextInput1.Text in UserEmail.'User mail' )

The restriction is search text need to be full-match with the User mail then will return the correct records.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
mrQ 482 on at

Like (0)

Report
Copy link

Link copied!

hi @v-jefferni

That looks already much better and it seems to work, thank you! However, it is only searching for exact matches at the moment. E.g. if I only type in half of the email, it wouldn't return any results.

Is there a fix for this?

I also realized I can get rid of the second AddColumns now.

Was this reply helpful? Yes No