Display Name ComboBox Filter

[tagustin2020]

Hello,

I am building a Project List app. All of the filters are working fine except for the Project Owner combobox filter. If someone could help me get this app over the finish line I would greatly appreciate it. Here is what the gallery looks like.

Here is the Project Owner ComboBox Items property:

Sort(ForAll(Distinct(galProjectList.AllItems,'Project Owner'.DisplayName),{Result: ThisRecord.Value}),Result, SortOrder.Ascending)

Here is the gallery Items formula minus the Project Owner filter (since it kept breaking things). I tried adding IsBlank(cbProjectOwner.Selected.Value) || projOwner in cbProjectOwner.SelectedItems.Value, but it didn't work. I also tried .Result and that didn't work either. What am I doing wrong?

With({galProjects:
    AddColumns('Project List',
        "projOwner", 'Project Owner'.DisplayName,
        "projType",'Project Type'.Value,
        "projStatus",Status.Value
    )
    },    
    DropColumns(
        SortByColumns(
           Filter(galProjects,
               'Checkbox-Closed'.Value || (projStatus <> "Closed"),
               TxtPLSearchBox.Text in 'Project Number' ||
            TxtPLSearchBox.Text in 'Project Name',
            IsBlank(cbProjectType.Selected.Value) || projType in cbProjectType.SelectedItems.Value),
           Coalesce(locSortColumn, "PROJECT_NAME"),
           If(locSortAscending, SortOrder.Descending, SortOrder.Ascending)
        ),
        "projOwner",
        "projType",
        "projStatus"
    )
)

Kind regards, 

Teresa

[WarrenBelz · Accepted solution]

Hi @tagustin2020 ,

Please change your Combo Box Items to

Sort(
   Distinct(
      galProjectList.AllItems,
      'Project Owner'.DisplayName
   ),
   Value
)

to get rid of the Microsoft changed workaround when .Result was changed to .Value and then your Gallery Items should be (assuming 'Project Owner' is a Person field and cbProjectOwner is a single selection)

With(
   {
      galProjects:
      AddColumns(
         'Project List',
         "projOwner", 
         'Project Owner'.DisplayName,
         "projType",
         'Project Type'.Value,
         "projStatus",
         Status.Value
      )
   },    
   DropColumns(
      SortByColumns(
         Filter(
            galProjects,
            (
               'Checkbox-Closed'.Value || 
               projStatus <> "Closed"
            ) &&
            (
               TxtPLSearchBox.Text in 'Project Number' ||
               TxtPLSearchBox.Text in 'Project Name'
            ) &&
            (
               Len(cbProjectType.Selected.Value) = 0 || 
               projType in cbProjectType.SelectedItems.Value
            ) &&
            (
               Len(cbProjectOwner.Selected.Value) = 0 ||
               ProjectOwner.DisplayName = cbProjectOwner.Selected.Value
            )
         ),
         Coalesce(
            locSortColumn, 
            "PROJECT_NAME"
         ),
         If(
            locSortAscending, 
            SortOrder.Descending, 
            SortOrder.Ascending
         )
      ),
      "projOwner",
      "projType",
      "projStatus"
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[tagustin2020]

@WarrenBelz 

Hello Warren,

Thank you for the quick reply. I replaced the Items formula of the ComboBox with your recommendation. The Gallery Items formula is still acting up at the Project Owner portion. I tried the following variations:

First, your recommendation - ProjectOwner is the internal column name:

&&
            (
               Len(cbProjectOwner.Selected.Value) = 0 ||
               ProjectOwner.DisplayName = cbProjectOwner.Selected.Value
            )
         ),

'Project Owner' which is the column display name:

&&
            (
               Len(cbProjectOwner.Selected.Value) = 0 ||
               'Project Owner'.DisplayName = cbProjectOwner.Selected.Value
         )),

projOwner which is the local variable:

(
               Len(cbProjectOwner.Selected.Value) = 0 ||
               projOwner = cbProjectOwner.Selected.Value
         )),

 Any idea why it is acting up?

[WarrenBelz]

Hi @tagustin2020 ,

What type of field is ProjectOwner ?

[tagustin2020]

@WarrenBelz 

Hello Warren,

I pasted in your formulas as shown, but I'm getting a circular reference error between the combobox and the gallery - "This rule creates a circular reference between properties which is not allowed. A property cannot reference itself or other properties affected by its value. This is the shortest circular reference: (galProjectList.'Items' -> 'cbProjectOwner'.'Items' - > 'galProjectList'.'Items'."

To confirm,'Project Owner' is a Person field, but I would like the cbProjectOwner to allow for multiple selections. Also, I changed the name of the checkbox control to ckClosedItems. 

Thank you,

Teresa

[tagustin2020]

@WarrenBelz 

It is a Person type SharePoint List column.

[WarrenBelz · Accepted solution]

Hi @tagustin2020 ,

You are going to need to get your Items for the Combo Box as

Sort(
   Distinct(
      ' Project List',
      'Project Owner'.DisplayName
   ),
   Value
)

as you cannot filter a Gallery when the Combo Box you are relying on is also doing the same thing (you will go around in circles as the warning suggests). You will have a Delegation issue here if ProjectList is a large list.

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[tagustin2020]

@WarrenBelz 

Thank you, Warren. That adjustment did the trick. The Project List is under 200 items for now so it will be a while before delegation becomes an issue. 

I appreciate your help!

Thank you,

Teresa