Prevent Creating Duplicate Projects with Same Component Team + Main BU Combination if Status is Open

[Harsha007]

Hi everyone,

I need help designing a rule in my App Sheet app related to project creation.

Scenario:

I have a Project Master table where each project has:

Component Team (Enum)

Main BU (Enum List with 4 possible values: CS, ASUX, EDS, OTHERS)

Status (values: Open, Closed)

I'm referencing the Component Team and Main BU from a table called D2.

What I want to achieve:

1. I should not be able to create a new project with the same Component Team and any already-used Main BU if the existing project for that combination has Status = Open.

Example:

Project 1: Component Team = PCB, Main BU = CS, EDS → Status: Open

Now if I try to create another project with:

Component Team = PCB

Main BU = EDS, OTHERS → This should not be allowed because EDS is already used in an open project for PCB.

2. If I create a project with:

Component Team = PCB, Main BU = CS

Then later, I can create another project like:

Component Team = PCB, Main BU = ASUX, OTHERS, EDS → This is fine because there's no overlap with the earlier BUs used in Open status.

3. Once the Status becomes Closed for a project, I should be able to use those Main BUs again for the same Component Team.

Goal: I want to enforce this logic automatically when creating a new project, and ensure it applies to all Component Teams.

What would be the best way to implement this logic in App Sheet?