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?

[Suvrutt_Gurjar · Accepted solution]

Please try in valid_if of project name column

ISBLANK(
FILTER(
"Project Master",
AND ([Component Team] = [_THISROW].[Component Team] ,

ISNOTBLANK( INTERSECT( [Main BU],   [_THISROW].[Main BU] )) ,

[Status]="Open"

)
)
- LIST([_THISROW])
)

Please test well 

[Harsha007]

It is working well, but for user easiness — can we make Main BU disappear after selection of the project for that Component Team when the project is open? Those should not be visible. Then, if the status is "Closed," those Main BUs should become visible again. Also, the Component Team should filter the available Main BUs. Is there a workaround for that?

[Suvrutt_Gurjar]

---

@Harsha007 wrote:

Also, the Component Team should filter the available Main BUs.

---

Could you elaborate what you mean by this?

Also could you mention with what expressions  the columns [Main BU] and [Component Team] are referenced from the table  D2 and what are those column types in the table D2?

[Harsha007]

Let’s say I have a Component Team called PCB, and the total Main BU options are 1, 2, 3, and 4.

But for PCB, only Main BU values 1 and 2 are assigned or applicable — not 3 and 4. So, when I select PCB, the Main BU dropdown should only show 1 and 2. The other values (3, 4) should not be visible.

Now, let’s say a project is already created using PCB + 1, and the status is "Open". Then the Main BU dropdown should only show2.

If later another project is created for PCB + 2, and both projects are still "Open", then the Main BU dropdown should show no values, and the user should not be able to create a new project until at least one of those Main BU projects is marked as "Closed".

Even when multiple Main BU selection is allowed, the system should only allow selection of those Main BU values that are:

Assigned to the selected Component Team, and

Not already used in an Open project.

If there are no available Main BU values left, the form should not be savable.

need help with this type of conditional filtering and validation  using Valid If or any other workaround?

In D2 component team is of long text and main Bu  is of text and in my project master component team is of Enum and main Bu Enum List and I used in valid if d2[component team] and select(d2[Main Bu],true).   

[Suvrutt_Gurjar]

---

@Harsha007 wrote:

But for PCB, only Main BU values 1 and 2 are assigned or applicable

---

---

@Harsha007 wrote:

But for PCB, only Main BU values 1 and 2 are assigned or applicable — not 3 and 4

---

Thank you for other details. Please update which table and how you are doing this assignment of BUs for a particular component team?

or , are you looking for solution for this requirement also?

[Harsha007]

Yeah, Actually I am looking for a solution for this scenario I used a formula it is working quite good but for one scenario where if no main Bu's are left it is ignoring required field and main Bu is hiding and I am able to save the project but i did not want to save as I did not given any main Bu here is my formula I used in main Bu valid if 

SELECT(

  D2[Main BU],

  AND(

    [Component_Team] = [_THISROW].[Component_Team],

    NOT(

      IN(

        [Main_BU],

        SPLIT(

          TEXT(

            SELECT(

              Project Master[Main BU],

              AND(

                [Component Team] = [_THISROW].[Component Team],

                [Status] = "Open"    )

            ) ),  " ,"

)) ))

[Suvrutt_Gurjar]

Thank you. Please try in valid_if of [Main BU] in Project Master table

SELECT( D2[Main BU],   [Component_Team] = [_THISROW].[Component_Team]) -

SPLIT( TEXT( SELECT( Project Master[Main BU], AND(  [Component Team] = [_THISROW].[Component Team],  [Status] = "Open"  , [Project Master key column]<>[_THISROW]. [Project Master key column] ) ) ),  " , " )

The major change I have highlighted in bold. Also please notice the subtraction of two expressions instead of wrapping one SELECT() in another SELECT() that can be very extensively evaluated expression.

[Suvrutt_Gurjar]

@Harsha007 ,

Could you respond if the following suggestion worked for you or not ?

---

@Suvrutt_Gurjar wrote:

Thank you. Please try in valid_if of [Main BU] in Project Master table

SELECT( D2[Main BU],   [Component_Team] = [_THISROW].[Component_Team]) -

SPLIT( TEXT( SELECT( Project Master[Main BU], AND(  [Component Team] = [_THISROW].[Component Team],  [Status] = "Open"  , [Project Master key column]<>[_THISROW]. [Project Master key column] ) ) ),  " , " )

---

[Harsha007]

No, @Suvrutt_Gurjar 

I implemented this solution, but it is also ignoring Main Bu even though it is marked as required when there are no Main Bu values are there for that component if I click on the component since no Main Bu values are there field itself hiding, and I can save it in this one scenario only it is failed but in every scenario it is working perfectly fine