Power Platform Community Forum Thread Details

Hello all,

I have searched for an answer to this problem, but have been unsuccessful. I'll first describe the scenario and then give details.

I'm developing a case management application. One part of the process is for users to add client firms AND client employees to a case. The flow would be: (1) add a new case, (2) select the client firms (there can be more than one) who have hired us, and (3) add a list of client employees who we'll be working with. When the user adds a new client employee, I'd like for them to be presented with a combo box that lists only employees of the firms we've added to the case. (Bonus if we can further limit the list to those employees who have not also already been added to the case.)

The user interface is a screen with three galleries: (1) galCases - the list of cases, (2) galClientFirms - the list of firms that have been added to the case, and (3) galClientPeople - the list of client employees who have been added to the case.

Data tables are:

- Case (primary id Case): this is a list of the cases

- Firm (primary id Firm): this is the list of firms

- 'Case Firm': a junction table between Case and Firm (this is a many-to-many relationship).

- People (primary id People): a list of people in a many-to-one relationship to the Firm table.

- 'Case People': a junction table between Case and People (another many-to-many relationship).

Data sources for the galleries are:

- galCases: the Case table

- galClientFirms: Filter('Case Firms', Case.Case = galCases.Selected.Case).

- galClientPeople: Filter('Case People', Case.Case = galCasesCases.Selected.Case)

When the user clicks the button to add a new client employee to the case, I cannot manage to filter options in the "Employee" ComboBox to the list of employees at the firms related to the case.

Here is what I have tried as the Items property of the ComboBox:

Filter(People, People in Filter('Case People', Firm in Filter('Case Firms', Case.Case = galCasesCases.Selected.Case).Firm).People)

Needless to say, it does not work (I get error messages). I've tried CoPilot, but it hasn't solved the problem either.

Thanks for any help.

Chip

Categories:

Using Apps Using Formulas

Filter employees (People) who belong to firms added to the current case.

You need to filter the employees based on firms tied to the selected case in galCases.

Filter(People, Firm in Filter('Case Firms', Case.Case = galCases.Selected.Case).Firm)

Employees not already added to the case.
You can use the Case People table to exclude employees already tied to the case.

Filter(People,
Firm in Filter('Case Firms', Case.Case = galCases.Selected.Case).Firm &&
People.PeopleId not in Filter('Case People', Case.Case = galCases.Selected.Case).People.PeopleId)

You would use this combined filter as the Items property of the ComboBox:

Filter(
People,
Firm in Filter('Case Firms', Case.Case = galCases.Selected.Case).Firm &&
People.PeopleId not in Filter('Case People', Case.Case = galCases.Selected.Case).People.PeopleId
)

hope this helps.