cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashaikh
Level: Powered On

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 -

 

image.pngGallery 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 Team
Community Support Team

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.
K-A-R-L
Level 10

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.

ashaikh
Level: Powered On

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.

Community Support Team
Community Support Team

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
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

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