I am attempting to filter a combo box on a form that lists choice values from another table. Below is a fictitious simple scenario to describe the requirement.
The main form shows the order with a combo box that allows the user to choose a customer. For a new order, I want the combo box to only display customers that are active. When editing the order, I want the combo box to display the customer associated with the order and only active customers if they want to change the customer.
Assume the tables below where the ORDER table has an established relationship to CUSTOMER.
ORDER
| order_id | customer_id |
| 1 | A |
| 2 | B |
| 3 | C |
CUSTOMER
| customer_id | name | active |
| A | Jack | Yes |
| B | Jill | Yes |
| C | John | No |
So on a new order, only customers A and B would be displayed in the combo box. If the form was displaying an existing order for customer C, customer C would be displayed and customers A and B would be displayed in the combo box.
I have tried using the Filter function without success. Any help is appreciated. The data source is Dataverse for Teams. Thank you.