Error in Gallery

[ankit_joshi11]

Hello,

I have been getting errors in the Gallery when including the Filter option. The error is "There is error in this formula" 

SortByColumns(Search(Filter([@'[database].[excel_datadb]'], If(FilterPending = true, Status_x0020_1.Value = "Pending/Not filed", Status_x0020_1.Value<>"")), TextSearchBox1.Text, "Ack_x002","Ack_x003","App_x0020_Form3B" , "Name_Dealer" ,"Ack_x004e" ), If(SortDescending1, Descending, Ascending))

I have started with Power Apps and cannot resolve this issue.

[BCLS776]

Welcome to the community and Power Apps

Your code has several problems in it, but I don't know enough what you are trying to do to suggest a fix for it. Could you provide?:

• A description of what you are trying to filter/search/sort from your datasource
• A description of what you are hoping to achieve with the FilterPending variable
• Which column you are trying to sort by?

That will give us more info to go on. A screenshot of your app and how you want the data to display could be helpful too.

[v-albai-msft]

Hi @ankit_joshi11 ,

I will give you some suggestions based on my experience:

1. From your code, you are missing a column which you want to sortby. So change your code as below: 

SortByColumns(
Search(Filter([@'[database].[excel_datadb]'],
If(FilterPending = true, Status_x0020_1.Value = "Pending/Not filed", Status_x0020_1.Value<>"")
), TextSearchBox1.Text, "Ack_x002","Ack_x003","App_x0020_Form3B" , "Name_Dealer" ,"Ack_x004e" ),

"ColumnNameYouwannaSortBy",

If(SortDescending1, Descending, Ascending)
)

(Above screenshot means it is sorted by "Title" column)

2. If this is a new app created from blank, not created from a datasource, and if you want to use If(SortDescending1,XX), you need to manually define a variable called SortDescending1. This variable is created automatically when you create an app from a datasource. 

Or, without using this variable, you can try formula like this:

SortByColumns(Search(Filter([@'[database].[excel_datadb]'], If(FilterPending = true, Status_x0020_1.Value = "Pending/Not filed", Status_x0020_1.Value<>"")), TextSearchBox1.Text, "Ack_x002","Ack_x003","App_x0020_Form3B" , "Name_Dealer" ,"Ack_x004e" ), Descending)

3. Make sure if your Filter function can get proper result. You can add another gallery, set its Items to:

Filter([@'[database].[excel_datadb]'], If(FilterPending = true, Status_x0020_1.Value = "Pending/Not filed", Status_x0020_1.Value<>""))

Check the result of the gallery, make sure if you can get the correct records. If no, edit this formula to get the proper records.

4. Make sure if your column name is correct:

Best regards,

Allen

[ankit_joshi11]

@v-albai-msft @BCLS776  Thanks for the Quick Reply I appreciate it.

I have created a web app using directly from the data. 

This is the app that includes Gallery, Refresh, Sort, Add & Button to see pending/ Not filed.

The Button Onselect "UpdateContext({FilterPending:!FilterPending})"

The Sort Onselect "UpdateContext({SortDescending1: !SortDescending1})"

Before including the Button the Gallery did not had any error. I used this

If(FilterPending = true, Status_x0020_1.Value = "Pending/Not filed", Status_x0020_1.Value<>"")

Excluding this command eliminated the error.

I included the "Name of Column to sort" Still showing an error.

[BCLS776]

OK, the extra info helps a bit. First, I have reformatted your code so it is easier to read - no changes yet:

SortByColumns(
    Search(
        Filter([@'[database].[excel_datadb]'], 
            If(FilterPending = true, 
                Status_x0020_1.Value = "Pending/Not filed", 
                Status_x0020_1.Value<>""
            )
        ), 
        TextSearchBox1.Text, 
        "Ack_x002",
        "Ack_x003",
        "App_x0020_Form3B" , 
        "Name_Dealer" ,
        "Ack_x004e" 
    ), 
    If(SortDescending1, Descending, Ascending)
)

The biggest problem is the Filter() with the nested If() statement. The second argument in a Filter is supposed to be a logical test and you are using the If() to try to inject one of two alternative tests into the Filter. As you are noticing, this does not work well. The If() statement gets hung up on thinking it has three logical tests to evaluate.

Instead, move the If() statement out one level and put the Filter() inside:

SortByColumns(
    Search(
        If(FilterPending,
            Filter([@'[database].[excel_datadb]'],            
                Status_x0020_1.Value = "Pending/Not filed",
            ),
            Filter([@'[database].[excel_datadb]'],
                Status_x0020_1.Value <> ""
            )
        ), 
        TextSearchBox1.Text, 
        "Ack_x002",
        "Ack_x003",
        "App_x0020_Form3B" , 
        "Name_Dealer" ,
        "Ack_x004e" 
    ),
    mySortColumnName, // Insert proper column name here 
    If(SortDescending1, Descending, Ascending)
)

Note too: your SortByColumns was missing a column name, so I have put a placeholder there. You will need to replace that with a valid, existing column name from the table.

I don't think this is quite the code you want yet. Have a closer look at the second Filter():

            Filter([@'[database].[excel_datadb]'],
                Status_x0020_1.Value<>""
            )

this condition will include any non-empty string value in Status_x0020_1, including "Pending/Not filed". Is that the correct logic for your app?

Hope this gets you closer to your intended results,
Bryan

[ankit_joshi11]

@BCLS776 Thanks for the Solutions.

But using the code you send it's showing an error.

"The functions Search has some invalid arguments."

[BCLS776 · Accepted solution]

Let's move the If() statement completely outside the others:

If(FilterPending,
    SortByColumns(
        Search(        
            Filter([@'[database].[excel_datadb]'],            
                Status_x0020_1.Value = "Pending/Not filed",
            ), 
            TextSearchBox1.Text, 
            "Ack_x002",
            "Ack_x003",
            "App_x0020_Form3B" , 
            "Name_Dealer" ,
            "Ack_x004e" 
        ),
        mySortColumnName, // Insert proper column name here 
        If(SortDescending1, Descending, Ascending)
    ),
    SortByColumns(
        Search(        
            Filter([@'[database].[excel_datadb]'],            
                Status_x0020_1.Value <> "",
            ), 
            TextSearchBox1.Text, 
            "Ack_x002",
            "Ack_x003",
            "App_x0020_Form3B" , 
            "Name_Dealer" ,
            "Ack_x004e" 
        ),
    mySortColumnName, // Insert proper column name here 
    If(SortDescending1, Descending, Ascending)
    )       
);

[v-albai-msft]

Hi @ankit_joshi11 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Best regards,

Allen