Filter gallery by text box and multi select combo box on choice field

[gizmobrat]

Hello,

    I have a table called Jobs with the following fields that I need to filter a gallery on. 'Job Name' which is the primary collum of the table and is a short text field and the second is Job_Phase which is a choice collum synced to a global variable. In my app I have two fields which I want to sort by. A Text Box which is named Job_Search_Box located in same container as the gallery. The second item a Moden combo box named Job_Status_Selector with selectMultiple set to true and the Items field set to:

["Complete","Construction","Design","RFP","Programming","Warranty","Pre-Award","Contracting",""]

My goal is to do the following. Allow a user to select zero, one, or more job phases to filter by and to be able to search for a job name within these filtered items. I have the formula below which returns values when the text box is used and when the Job_Status_Selector is blank. However, once Job_Status_Selector has a selected item no items are returned.

Below is the Items field for the Gallery:

SortByColumns(
    Filter(
        Jobs,
        StartsWith(
            'Job Name',
            Job_Search_Box.Value
        )&& (Job_Status_Selector.Selected.Value = Blank() || Job_Phase = Job_Status_Selector.Selected.Value)
    ),
    "crb66_job_name",
    SortOrder.Ascending
)

[Amik]

@gizmobrat - I do not quite understand what you mean by "Job_Phase which is a choice collum synced to a global variable. "

Given you have manually set the Items property for Job_Status_Selector ComboBox, I am going to assume the Job_Phase field in your table is a Single Line Text field. If that is correct then try:

SortByColumns(
    Filter(
        Jobs,
        StartsWith(
            'Job Name',
            Job_Search_Box.Text
        ),
        CountRows(Job_Status_Selector.SelectedItems) = 0 || IsBlank(Job_Status_Selector.SelectedItems) || Job_Phase in Job_Status_Selector.SelectedItems
    ),
    "crb66_job_name",
    SortOrder.Ascending
)

[gizmobrat]

@Amik,

    This solution is still giving me values only when empty. The Job_phase collum is a choice type. See below for the Collum details. 

[Amik]

 @gizmobrat given the Job_phase field is a Global Choice, what is the point of hardcoding the values into items property the Job_Status_Selector Combo box?

["Complete","Construction","Design","RFP","Programming","Warranty","Pre-Award","Contracting",""]

[gizmobrat]

@Amik, As I have not found a way to use choices as an item in a combo box.

[Amik · Accepted solution]

@gizmobrat you do not need to do this. For example, insert an EditForm and link that Form to your Dataverse table, and then add your Choice column into the Form. If you select the Combo Box in the DataCard and look at the Items property, you will see it is directly referencing the Global Choice.

With this understanding in mind, in the the Items property of the Job_Status_Selector Combobox, enter:

Choices(DPW_Job_Phase)

Then use the following in the Items property of your Gallery:

SortByColumns(
    Filter(
        Jobs,
        StartsWith(
            'Job Name',
            Job_Search_Box.Text
        ),
        CountRows(Job_Status_Selector.SelectedItems) = 0 || IsBlank(Job_Status_Selector.SelectedItems) || Job_Phase in Job_Status_Selector.SelectedItems
    ),
    "crb66_job_name",
    SortOrder.Ascending
)

[gizmobrat]

@Amik,

That worked Thank you. The only thing that could be better is if there is a way to use the text box to search for text in any location instead of just the start of the field. Although I believe that this is not possible. 

gizmobrat

[Amik · Accepted solution]

@gizmobrat - this is indeed possible using the Search function, but this is not a delegable function with Dataverse.

The "in" function however is delegable with Dataverse:

SortByColumns(
    Filter(
        Jobs,
        CountRows(Job_Status_Selector.SelectedItems) = 0 || IsBlank(Job_Status_Selector.SelectedItems) || Job_Phase in Job_Status_Selector.SelectedItems,
        Job_Search_Box.Text in 'Job Name'
    ),
    "crb66_job_name",
    SortOrder.Ascending
)

Further reading:

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/connection-common-data-se... 

[gizmobrat]

That worked for me Thank You. Although for some reason I needed to change the text box to a .value.