search and lookup in gallery

[Anonymous]

Hello All,

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

Gallery 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

[v-yutliu-msft]

Hi @Anonymous ，

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

[K-A-R-L]

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.

[Anonymous]

@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.

[v-yutliu-msft]

Hi @Anonymous ,

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.

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