ComboBox People Filter Issues

[tagustin2020]

Hello,

I'm working on an Engineering request app. I have the search, sort and most of the filters working the way I want. The only two I am struggling with are the Requestor and Engineer filters I've bordered in red below.

The Requestor combobox formula is:

Items: 
Sort(ForAll(Distinct(galRequests.AllItems,Requestor.DisplayName),{Result: ThisRecord.Value}),Result, SortOrder.Ascending)
DisplayFields: ["Result"]
SearchFields: ["Result"]
OnChange: 
ForAll(Distinct(galRequests.AllItems, Requestor.DisplayName),{Result: ThisRecord.Value})

The Assigned To combox formula is:

Items: 
ForAll(ForAll(Distinct(galRequests.AllItems,'FA Assigned'.DisplayName),{Result:ThisRecord.Value}),If(IsBlank(Result),"pending",Result))
DisplayFields: ["Value"]
SearchFields: ["Value"]
OnChange: 
ForAll(Distinct(galRequests.AllItems,'FA Assigned'.DisplayName),{Result: ThisRecord.Value})

My Gallery Items formula is:

With(
    {
        galRequests: AddColumns(
            'Failure Analysis Requests',
            "status",
            Status.Value,
            "requestor",
            Requestor.DisplayName,
            "engineer",
            'FA Assigned'.DisplayName
        )
    },
    DropColumns(
        SortByColumns(
            Filter(
                galRequests,
                !ckAssigned.Value || 'FA Assigned'.Email = varUser.Email,
                !ckMine.Value || 'Requestor Email' = varUser.Email,
                TxtSearchBox.Text in 'Request Summary Title' || TxtSearchBox.Text in ID,
                IsBlank(dpCreatedFrom.SelectedDate) || Created >= dpCreatedFrom.SelectedDate,
                IsBlank(dpCreatedTo.SelectedDate) || Created <= dpCreatedTo.SelectedDate,
                IsBlank(dpModifiedFrom.SelectedDate) || Modified >= dpModifiedFrom.SelectedDate,
                IsBlank(dpModifiedTo.SelectedDate) || Modified <= dpModifiedTo.SelectedDate,
                IsBlank(dpReceivedFrom.SelectedDate) || 'Sample Received' >= dpReceivedFrom.SelectedDate,
                IsBlank(dpReceivedTo.SelectedDate) || 'Sample Received' <= dpReceivedTo.SelectedDate,
                IsBlank(dpAgreedFrom.SelectedDate) || 'Agreed Due Date' >= dpAgreedFrom.SelectedDate,
                IsBlank(dpAgreedTo.SelectedDate) || 'Agreed Due Date' <= dpAgreedTo.SelectedDate,
                IsBlank(cbStatusFilter.Selected.Value) || status in cbStatusFilter.SelectedItems.Value
            ),
            Coalesce(
                locSortColumn,
                "Title"
            ),
            If(
                locSortAscending,
                SortOrder.Ascending,
                SortOrder.Descending
            )
        ),
        "status",
        "requestor",
        "engineer"
    )
)

I've tried the following Gallery Items formula additions without success. Can you help me figure out what I am doing wrong?

Requestor Variation 1:
IsBlank(cbRequestorFilter.Selected.Result) || requestor = cbRequestorFilter.Selected.Result

Requestor Variation 2:
IsBlank(cbRequestorFilter.Selected.Result) || Requestor.DisplayName= cbRequestorFilter.Selected.Result

Engineer Variation 1:
IsBlank(cbEngineerFilter.Selected.Value) || engineer = cbEngineerFilter.Selected.Value

Requestor Variation 2:
IsBlank(cbEngineerFilter.Selected.Value) || 'FA Assigned'.DisplayName = cbEngineerFilter.Selected.Value

Thank you in advance for your kind assistance,

Teresa

[victorcp]

Hi Teresa,

what is wrong with these components? they are not patching the information or is something diferent?

[Amik · Accepted solution]

@tagustin2020, your Distinct function is returning a table with a single column called “Value”, so using Your_ComboBox.Selected.Value would be correct.

What does not make sense, however, is that in the Items property for your Combo Box, the Name value for your With function is using same name as your Gallery name. I think this would return a circular reference?

Consider changing the Items property formula for the Combo Boxes by explicitly referencing your data source name:

ForAll(

    ForAll(

        Distinct(

            'Failure Analysis Requests',

            Requestor.DisplayName

        ),

        {Result: ThisRecord.Value}

    ),

    If(

        IsBlank(Result),

        "pending",

        Result

    )

)

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[Amik]

apologies @victorcp - I had not refreshed my browser and did not see that you had already responded.

[Ethan_R]

Hi @tagustin2020 ,

If you are using Multi-select on combobox then you have to change your formula.

Also, considering "requestor" and ".Selected.Result" provides same datatype then its good else you need to add its sub-attributes such as "requestor.DisplayName" and compare with its related value ".Selected.FullName" or anything that matches your need.

Your syntax will be:

//Requestor:
IsBlank(cbRequestorFilter.Selected.Result) || IsEmpty(cbRequestorFilter.SelectedItems) || requestor in cbRequestorFilter.Selected.Result

//Engineer:
IsBlank(cbEngineerFilter.Selected.Result) || IsEmpty(cbEngineerFilter.SelectedItems) || 'FA Assigned'.DisplayName in cbEngineerFilter.Selected.Value

Hope this helps

[tagustin2020]

@Amik 

@victorcp 

Hello Amik,

Thank you for the advice. I am able to filter the gallery by requestor and engineer now. The only small glitch is that I am not able to filter by the value of "pending" which is the placeholder text in the engineer column when no one has been assigned to the task yet. Any ideas on how to account for that value? 

The Text property of the gallery label is:

If(IsBlank(ThisItem.'FA Assigned'.DisplayName), "pending", ThisItem.'FA Assigned'.DisplayName)

Thank you,

Teresa

[tagustin2020]

@victorcp 

Hello Victor,

Thank you for the reply. I don't really understand the question, but I copied you on reply to Amik as his suggestion seems to be working with the exception of one minor consideration.

Kind regards,

Teresa

[Amik]

@tagustin2020, you do not need to reference any controls in the Gallery. 

Instead, in the expression you're using for the Gallery Items property, include a condition in your Filter function.

In the below example, we can use Switch:

            IsBlank(cbRequestorFilter.Selected.Value) || Switch(
                cbRequestorFilter.Selected.Value,
                "pending",
                requestor = Blank(),
                requestor = cbRequestorFilter.Selected.Value
            )
        )

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[tagustin2020]

@Amik 

@Ethan_R 

@victorcp 

Thank you, Amit! This solution worked. I just had to adjust it for the 'FA Assigned' field since that is the one that has a "pending" filler. Here is the latest Items formula for anyone else who might look to this post for help.

With(
    {
        galRequests: AddColumns(
            'Failure Analysis Requests',
            "status",
            Status.Value,
            "requestor",
            Requestor.DisplayName,
            "engineer",
            'FA Assigned'.DisplayName
        )
    },
    DropColumns(
        SortByColumns(
            Filter(
                galRequests,
                !ckAssigned.Value || 'FA Assigned'.Email = varUser.Email,
                !ckMine.Value || 'Requestor Email' = varUser.Email,
                TxtSearchBox.Text in 'Request Summary Title' || TxtSearchBox.Text in ID,
                IsBlank(dpCreatedFrom.SelectedDate) || Created >= dpCreatedFrom.SelectedDate,
                IsBlank(dpCreatedTo.SelectedDate) || Created <= dpCreatedTo.SelectedDate,
                IsBlank(dpModifiedFrom.SelectedDate) || Modified >= dpModifiedFrom.SelectedDate,
                IsBlank(dpModifiedTo.SelectedDate) || Modified <= dpModifiedTo.SelectedDate,
                IsBlank(dpReceivedFrom.SelectedDate) || 'Sample Received' >= dpReceivedFrom.SelectedDate,
                IsBlank(dpReceivedTo.SelectedDate) || 'Sample Received' <= dpReceivedTo.SelectedDate,
                IsBlank(dpAgreedFrom.SelectedDate) || 'Agreed Due Date' >= dpAgreedFrom.SelectedDate,
                IsBlank(dpAgreedTo.SelectedDate) || 'Agreed Due Date' <= dpAgreedTo.SelectedDate,
                IsBlank(cbStatusFilter.Selected.Value) || status in cbStatusFilter.SelectedItems.Value,
                IsBlank(cbEngineerFilter.Selected.Value) || Switch(cbEngineerFilter.Selected.Value, "pending", engineer=Blank(),
                engineer=cbEngineerFilter.Selected.Value),
                IsBlank(cbRequestorFilter.Selected.Result) || Requestor.DisplayName=cbRequestorFilter.Selected.Result
            ),
            Coalesce(
                locSortColumn,
                "Title"
            ),
            If(
                locSortAscending,
                SortOrder.Ascending,
                SortOrder.Descending
            )
        ),
        "status",
        "requestor",
        "engineer"
    )
)

Thank you and have a great day! 

Teresa

[Amik]

You're welcome @tagustin2020!