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 Supplier
Solution Supplier

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.

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,069)