cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

search and lookup in gallery

Hello All,

 

I have an app created which does search on a table with 2000 + records. This is how my app looks -

 

Gallery Bar SearchGallery Bar Search

For this, I have written a search function to search for text written in the search box. The code for that is below - 

 

SortByColumns(
Search(
Filter('[dbo].[note_attachment_time_entry]', task_id <> -1),
TextSearchBox3.Text, "description"),
"task_id", If(SortDescending1, Descending, Ascending)
)

Here table [dbo].[note_attachment_time_entry] has records more than 2000+ records and its written as such that it is delegatable and it is working accurately.

 

 

I want something in addition when the search result is displayed below, I want to show task_id which is currently displayed as well as task_number which comes from [dbo].[wh_task].

 

[dbo].[wh_task] has again more than 2000 records. I have written a sample code which can work to the lookup, but I want to combine this with the search function.

So that it displays task_id from [dbo].[note_attachment_time_entry] and task_number from [dbo].[wh_task].

 

 

AddColumns('[dbo].[note_attachment_time_entry]',
"tasknumber",
LookUp('[dbo].[wh_task]', '[dbo].[wh_task]'[@task_id] = '[dbo].[note_attachment_time_entry]'[@task_id]).task_number)

 

task_id is also available in [dbo].[wh_task].

 

Thanks in Advance

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: search and lookup in gallery

Hi @ashaikh ,

Do you want to filter items and diaplay additional tasknumber field at the same time?

Try this formula:

SortByColumns(
Search(
Filter(AddColumns('[dbo].[note_attachment_time_entry]',
"tasknumber",
LookUp('[dbo].[wh_task]',
'[dbo].[wh_task]'[@task_id] = '[dbo].[note_attachment_time_entry]'[@task_id]).task_number
)
),
task_id <> -1
),
TextSearchBox3.Text, "description"
),
"task_id", If(SortDescending1, Descending, Ascending)
)

 

Best regards,

Community Support Team _ Phoebe Liu

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.
Highlighted
Solution Specialist
Solution Specialist

Re: search and lookup in gallery

Hi @v-yutliu-msft ,

Have you already tested @v-yutliu-msft 's idea?

I think you might want to add the Reduction Formula on the LookUp() Syntax:

LookUp( Table, Formula [, ReductionFormula ] )

You might want to try this:

SortByColumns(
               Search(
                       Filter(AddColumns('[dbo].[note_attachment_time_entry]', 
                                         "tasknumber", 
                                         LookUp('[dbo].[wh_task]', //Data source
                                                '[dbo].[wh_task]'[@task_id] = '[dbo].[note_attachment_time_entry]'[@task_id]).task_number, //Formula - This will return the first record that results in true
                                              task_id //Reduction Forumula - this reduces record to a single value that matches your formula above
                                                )
                                         ), 
                              task_id <> -1
                             ), 
                       TextSearchBox3.Text, "description"
                     ), 
               "task_id", If(SortDescending1, Descending, Ascending)
             )

Best of luck.

 

Thanks,
K-A-R-L


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If you thought this post was helpful, please give it a Thumbs Up.

Highlighted
Frequent Visitor

Re: search and lookup in gallery

@K-A-R-L  and @v-yutliu-msft I have tried both these formula but I am not getting correct result.

 

@K-A-R-L the solution you suggested gives me partial result but it does not give result for all the searches.

@v-yutliu-msft by using reduction I am not getting any result.

 

And in both the cases I still have delegation warning.

 

I am using thisItem.tasknumber to display value.

 

From the formula I see that we are first doing the lookup and then search where as in my case I am looking to first search and then do lookup between wh_task and note_attachment_time_entry.

Highlighted
Community Support
Community Support

Re: search and lookup in gallery

Hi @ashaikh ,

If you want to do Search first, you could save your search result as collection and then do look up in the collection.

Try this formula:

ClearCollection(result,Search('[dbo].[note_attachment_time_entry]', TextSearchBox3.Text, "description"));
SortByColumns(
Filter(
AddColumns(result, "tasknumber", LookUp('[dbo].[wh_task]', '[dbo].[wh_task]'[@task_id] = result[@task_id]).task_number, task_id ) ), task_id <> -1 ), "task_id", If(SortDescending1, Descending, Ascending) )

About delegation, that's a little complex.

If your record is larger than 2000, your app may not perform well.

That's PowerApps itself limit.

Firstly , I suggest you change the limit to 2000.7124.PNG

 

Secondly,I suggest you modify your formula with delegable functions.

Here's a doc about delegable functions in SQL server for your reference:

https://docs.microsoft.com/en-us/connectors/sql/

 

 

Best regards,

Community Support Team _ Phoebe Liu

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,821)