Help needed with the code

[PeKi72]

Dear community,

I am struggeling with my code (to many brackets and curlies). What i would like is not to display records in my gallery when the textbox called Searchbox and both the comboboxes cbAfdeling and cbTitel are empty. 

Where should i put :

If(
IsBlank(SearchBox.Value) && IsBlank(cbAfdeling.Selected.Value) && IsBlank(cbTitel.Selected.Value);

And the sorting should apply to everything not only on the cbAfdeling or cbTitel

Where should i put the code below so it applies on everything:

        "crcfa_achternaam";
        If(
            cbSort.Selected.Value = "A-Z";
            SortOrder.Ascending;
            SortOrder.Descending
        )

This is my code:

If(
    IsBlank(cbAfdeling.Selected.Value) && IsBlank(cbTitel.Selected.Value);
    Search(
        dbEmployeeDirectory;
        SearchBox.Value;
        "crcfa_voornaam";
        "crcfa_achternaam";
        "crcfa_personeelsnummer";
        "crcfa_gebruikersid";
        "crcfa_computer"
    );
    SortByColumns(
        Search(
            Filter(
                dbEmployeeDirectory;
                Afdeling = cbAfdeling.Selected.Value || Titel = cbTitel.Selected.Value
            );
            SearchBox.Value;
            "crcfa_voornaam";
            "crcfa_achternaam";
            "crcfa_personeelsnummer";
            "crcfa_gebruikersid";
            "crcfa_computer"
        );
        "crcfa_achternaam";
        If(
            cbSort.Selected.Value = "A-Z";
            SortOrder.Ascending;
            SortOrder.Descending
        )
    )
)

Gr. p

[LaurensM]

Hi @PeKi72,

Did I understand it correctly that you do not want any records displayed when no selections are made and the search input is empty? In this case we can use the following approach:

If(
    //Only display record when at least 1 is not blank
    !IsBlank(SearchBox.Value) || !IsBlank(cbAfdeling.Selected.Value) || !IsBlank(cbTitel.Selected.Value);
    SortByColumns(
        Search(
            Filter(
                dbEmployeeDirectory;
                //Adjusted condition to remove our initial If() statement
                (IsBlank(cbAfdeling.Selected.Value) || Afdeling = cbAfdeling.Selected.Value) || (IsBlank(cbTitel.Selected.Value) || Titel = cbTitel.Selected.Value)
            );
            SearchBox.Value;
            "crcfa_voornaam";
            "crcfa_achternaam";
            "crcfa_personeelsnummer";
            "crcfa_gebruikersid";
            "crcfa_computer"
        );
        "crcfa_achternaam";
        If(
            cbSort.Selected.Value = "A-Z";
            SortOrder.Ascending;
            SortOrder.Descending
        )
    )
)

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[Prabhakar_S]

Hi @PeKi72 ,

Try this,

If(
IsBlank(cbAfdeling.Selected.Value) && IsBlank(cbTitel.Selected.Value),
Blank(), // If all fields are empty, return a blank result
SortByColumns(
Search(
Filter(
dbEmployeeDirectory,
IsBlank(cbAfdeling.Selected.Value) || Afdeling = cbAfdeling.Selected.Value,
IsBlank(cbTitel.Selected.Value) || Titel = cbTitel.Selected.Value
),
SearchBox.Text,
"crcfa_voornaam",
"crcfa_achternaam",
"crcfa_personeelsnummer",
"crcfa_gebruikersid",
"crcfa_computer"
),
"crcfa_achternaam",
If(
cbSort.Selected.Value = "A-Z",
SortOrder.Ascending,
SortOrder.Descending
)
)
)

The 'If' function checks if all two fields are blank. If they are, it returns 'Blank()' to ensure no records are displayed in the gallery.

Thanks!!!

Please consider marking my response as the accepted solution if it successfully resolves your concern. If you found the information beneficial in other aspects, kindly express your appreciation by giving it a thumbs-up.

[PeKi72]

This part should be put back. Otherwise the search will not work when cbAfdeling and cbTitel are empty

    IsBlank(cbAfdeling.Selected.Value) && IsBlank(cbTitel.Selected.Value);
    Search(
        dbEmployeeDirectory;
        SearchBox.Value;
        "crcfa_voornaam";
        "crcfa_achternaam";
        "crcfa_personeelsnummer";
        "crcfa_gebruikersid";
        "crcfa_computer"
    );

[PeKi72]

This works fine:
If(
    !IsBlank(SearchBox.Value) || !IsBlank(cbAfdeling.Selected.Value) || !IsBlank(cbTitel.Selected.Value);
    If(
        IsBlank(cbAfdeling.Selected.Value) && IsBlank(cbTitel.Selected.Value);
        Search(
            dbEmployeeDirectory;
            SearchBox.Value;
            "crcfa_voornaam";
            "crcfa_achternaam";
            "crcfa_personeelsnummer";
            "crcfa_gebruikersid";
            "crcfa_computer"
        );
        SortByColumns(
            Search(
                Filter(
                    dbEmployeeDirectory;
                    IsBlank(cbAfdeling.Selected.Value) || Afdeling = cbAfdeling.Selected.Value;
                    IsBlank(cbTitel.Selected.Value) || Titel = cbTitel.Selected.Value
                );
                SearchBox.Value;
                "crcfa_voornaam";
                "crcfa_achternaam";
                "crcfa_personeelsnummer";
                "crcfa_gebruikersid";
                "crcfa_computer"
            );
            "crcfa_achternaam";
            If(
                cbSort.Selected.Value = "A-Z";
                SortOrder.Ascending;
                SortOrder.Descending
            )
        )
    )
)

[PeKi72]

Now i need to move the sort, because the sort is not applying on the search:

   IsBlank(cbAfdeling.Selected.Value) && IsBlank(cbTitel.Selected.Value);
    Search(
        dbEmployeeDirectory;
        SearchBox.Value;
        "crcfa_voornaam";
        "crcfa_achternaam";
        "crcfa_personeelsnummer";
        "crcfa_gebruikersid";
        "crcfa_computer"
    );

[LaurensM · Accepted solution]

@PeKi72,

It is best practice to avoid nested If statements and code duplication whenever possible. With text fields you can use the following delegable alternative to Search & Filter nesting in Dataverse:

If(
    //Only display record when at least 1 is not blank
    !IsBlank(SearchBox.Value) || !IsBlank(cbAfdeling.Selected.Value) || !IsBlank(cbTitel.Selected.Value);
    SortByColumns(
        Filter(
            dbEmployeeDirectory;
            //Adjusted condition to remove our initial If() statement
            IsBlank(cbAfdeling.Selected.Value) || Afdeling = cbAfdeling.Selected.Value;
            IsBlank(cbTitel.Selected.Value) || Titel = cbTitel.Selected.Value;
            //Using in operator combined with Or statements instead of Search
            SearchBox.Value in voornaam || SearchBox.Value in achternaam || SearchBox.Value in personeelsnummer || SearchBox.Value in gebruikersid || SearchBox.Value in computer
        );
        "crcfa_achternaam";
        If(
            cbSort.Selected.Value = "A-Z";
            SortOrder.Ascending;
            SortOrder.Descending
        )
    )
)

 You may have to adjust the columns swapped from the Search function should they result in an error. (e.g. faulty capitalization)

When no selection is made, you search the full dataset for a match in one of the columns. When a selection is made, you will only be able to search on a subset of data (dependent on the selected value in your combobox).

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!