cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Search Function with Lookup Fields

Hello,

 

I am trying to build a PowerApp to look up orders placed in the system. We have all our orders running through D365/CDS. The orders are on their own entity and the contact field is a lookup field. I am trying to build a search function to allow us to search by the name of the contact in addition to the order number of the order. 

 

This one works for the order number.

Search(Filter('External Orders', 'External Orders (Views)'.'Cancel Res Orders'), TextSearchBox1.Text, "gtwuk_externalid"),"gtwuk_extorderid",...

 

This is what I would like to have work where we can search by the name. However I am getting an error on this one. 

Search(Filter('External Orders', 'External Orders (Views)'.'Cancel Res Orders'), TextSearchBox1.Text, "gtwuk_externalid",Contacts.'Full Name'),"gtwuk_extorderid",...

 

Thoughts?

 

Thanks

 

Aaron

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @Anonymous ,

Do you want to filter your table based on these fields: gtwuk_externalid,Contacts,gtwuk_extorderid?

Could you tell me these three fields' data type?

I assume that gtwuk_externalid(text),Contacts(lookup),gtwuk_extorderid(text)

Firstly, please notice one thing:

Search function could only works for text type field directly.

To solve this problem, you have two solumns:

1)use filter function like @Drrickryp  provided.

Filter function could work for lookup field.

2)add a text column with the value of lookup field, then search based on this new column.

Try formula like this:

Search(
AddColumns(
Filter('External Orders', 'External Orders (Views)'.'Cancel Res Orders'),
"lookupfullname",
Contacts.'Full Name'
            ),
TextSearchBox1.Text,
"gtwuk_externalid","lookupfullname","gtwuk_extorderid"
)


Maybe you want to know more about search function:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

 

 

Best regards,

Community Support Team _ Phoebe Liu
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

2 REPLIES 2
Super User II
Super User II

Hi @Anonymous 

 

 

Filter(
        Filter('External Orders', 'External Orders (Views)'.'Cancel Res Orders'
         ), TextSearchBox1.Text in "gtwuk_externalid" || 
            TextSearchBox1.Text in Contacts.'Full Name'||
            TextSearchBox1.Text in "gtwuk_extorderid",|| ...
)

 

 

 

Community Support
Community Support

Hi @Anonymous ,

Do you want to filter your table based on these fields: gtwuk_externalid,Contacts,gtwuk_extorderid?

Could you tell me these three fields' data type?

I assume that gtwuk_externalid(text),Contacts(lookup),gtwuk_extorderid(text)

Firstly, please notice one thing:

Search function could only works for text type field directly.

To solve this problem, you have two solumns:

1)use filter function like @Drrickryp  provided.

Filter function could work for lookup field.

2)add a text column with the value of lookup field, then search based on this new column.

Try formula like this:

Search(
AddColumns(
Filter('External Orders', 'External Orders (Views)'.'Cancel Res Orders'),
"lookupfullname",
Contacts.'Full Name'
            ),
TextSearchBox1.Text,
"gtwuk_externalid","lookupfullname","gtwuk_extorderid"
)


Maybe you want to know more about search function:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

 

 

Best regards,

Community Support Team _ Phoebe Liu
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
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Users online (22,290)