Is it only possible to search by Text-fields with gallery from Sharepoint list?

[Anonymous]

Hi all,

I have a gallery, in which I have the following items property:

Sort(
    Search(
    If(Dropdown1.Selected.Value = "All",(
        Filter(
        'Main Table',
        ('Creator Email'=VarUser.Email)
        )),Dropdown1.Selected.Value <> "All",Filter(
        'Borusan Main Table',('Creator Email'=VarUser.Email),
        ('Proposal Status'.Value = Dropdown1.Selected.Value))),TextSearchBox1.Text,
    "Title"),
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

Now, I would really like to be able to search for other fields or sort by date. However, none of these columns are text, which means I get an error with I try to add them as columns after "Title".

Is it ONLY possible to search/sort by columns that has a Text field?

[WarrenBelz]

Hi @Anonymous ,

I did some parsing of your code. You had a lot of brackets not needed and one at the top where I think a comma was needed. Before I start looking on your searching and filtering, can you please confirm that this code will do what you currently require.

Sort(
   Search(
      If(
         Dropdown1.Selected.Value = "All",
         Filter(
           'Main Table',
           'Creator Email'=VarUser.Email,
            Dropdown1.Selected.Value <> "All"
         ),
         Filter(
            'Borusan Main Table',
            'Creator Email'=VarUser.Email,
            'Proposal Status'.Value = ValueDropdown1.Selected.Value
         ),
      TextSearchBox1.Text,
      "Title"
      ),
   If(
     SortDescending1,
     Descending,
     Ascending
   )
)

[Anonymous]

@WarrenBelz 

The formula I used actually works just fine. I am primarily trying to figure out whether I can search/sort by different columns.

[WarrenBelz]

Hi @Anonymous ,

I did the parsing only  so I had something in which I could help you with code.

You can search by other field types, but dates for instance are not delegable. It also depends on the operator used. For instance not equal <> is not delegable not matter what the data type.

If you give me an example of what you want to do, I will have a look at it. Sorting is not an issue on any field type.

I am also in a different time zone, so may not get back immediately.

[Anonymous]

@WarrenBelz

Right. I understand, I will try and fit your code, it just gave me a lot of red scribble

What I ideally would like to do is for example be able to not only search by “Title” but also “From City”, “From Country”, both of which are lookup columns.

Regarding sorting, rather than it currently sorting by “Title” I would like to be able to sort the gallery after the filtering ascending/descending (depending on presses by the button) of the Date Column

[PowerAddict]

The simplest way is to use AddColumns to get text values put of the lookup fields.

For example:
Sort(Search(Filter(AddColumns(DataSource, "NewColumn1", LookupColumn1.Field1, "NewColumn2", LookupColumn2.Field2), FilterCondition1, FilterCondition2), "NewColumn1", "NewColumn2", "TextColumn1"), "NewColumn1", "NewColumn2", "TextColumn", If(sortOrderVar, Ascending, Descending))

Your sort button can have this code:
UpdateContext({sortOrderVar: !sortOrderVar})

Also on the OnVisible property of the screen, set the variable to true to false based on whatever default order you want.

Here is some more info about Sort and SortByColumns:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort

Here is some more info about Filter and Search:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Here is some more info about AddColumns:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping

One important thing to keep in mind is that the values that are passed to the AddColumns function can be delegable based on how they are written but the output of AddColumns is not. And this delegation error will not be flagged by the App Checker (scary right?)

Here is my blog post about this:
https://thepoweraddict.com/the-invisible-delegation-issue/

Let me know if this helps or if you need a detailed formula with your values.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

[WarrenBelz]

Hi @Anonymous ,

No need to fit into my parsing if it is giving errors. I can work with yours and show you where to put the code.

@PowerAddict has provided some valuable information since my post (I am in Australia and it is early morning here presently) including a good example of how to sort and toggle from ascending to descending.

The text component of SharePoint Lookup columns are generally .Value, so should not be hard to query.

Multiple search boxes are certainly possible (I have one that uses four) and the best way I have found is to use the StartsWith filter.

One thing to keep in mind is that if you have else then 2000 records, or can filter a data set that you want to search from using a text or numeric filter, then Delegation is not an issue and any filter including dates will work.

Regarding the errors you mention, without seeing the code, I am guessing, however if you are filtering for example on a day picker, the value will be <control name>.SelectedDate.

You can certainly sort by date.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Anonymous]

@WarrenBelz& @PowerAddict ,

Thank you both for the great responses, this is definitely very useful. I have been trying to implement this concept of AddColumn, however, as my items property of the Gallery is fairly long and a bit complex, I can't seem to find a way to implement the AddColumn()..

My gallery's item property looks like this:

Sort(
    Search(
        If(
            And(FilterProposalStatus.Selected.Value = "All",TarihAra = true),
            Filter(
                'Borusan Main Table',
                ('Creator Email' = VarUser.Email),
                ('Teklifin Son Gecerlilik Tarihi' >= Start.SelectedDate) && ('Teklifin Son Gecerlilik Tarihi' <= Finish.SelectedDate)
            ),
            And(FilterProposalStatus.Selected.Value <> "All", TarihAra = true),
            Filter(
                'Borusan Main Table',
                ('Creator Email' = VarUser.Email),
                ('Proposal Status'.Value = FilterProposalStatus.Selected.Value),
                ('Teklifin Son Gecerlilik Tarihi' >= Start.SelectedDate) && ('Teklifin Son Gecerlilik Tarihi' <= Finish.SelectedDate)
            ),
            And(FilterProposalStatus.Selected.Value <> "All", TarihAra = false),
            Filter(
                'Borusan Main Table',
                ('Creator Email' = VarUser.Email),
                ('Proposal Status'.Value = FilterProposalStatus.Selected.Value)),
            And(FilterProposalStatus.Selected.Value <> "All", TarihAra = false),
            Filter(
                'Borusan Main Table',
                ('Creator Email' = VarUser.Email),
                ('Proposal Status'.Value = FilterProposalStatus.Selected.Value))
        ),
        TextSearchBox1.Text,
        "Title"
    ),
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

if I wish to fx incorporate this ("Teklifin Son Gecerlilik Tarihi" is a date column) how do I do that: 

AddColumns('Borusan Main Table', "SonTarih", 'Teklifin Son Gecerlilik Tarihi'),

I get errors wherever or however I write it..