Site status classficiation based on different project status

[Netope]

Dear community:

I have two tables one for site status, and the other table with project status by site where one site may have multiple projects.

In the site status table I have a column for status, but I need this colums to be calculated based on a logic wich considers the different status for projects assigned to that site in the project status table. I have a relationship built for both tables using the site column.

This is the logic:

If at least one project is running, the site status is running (no matter is there are other projects with status closed or not started in the project status table)

If all projects are closed, then the sites status is closed

If all projects are not yet started then the site status is planned

If there are no projects for a site, then the site status is not planned

Here is an example:

Project Status Table

Project	Site	Status
1	A	Running
2	A	Closed
3	A	Not Started
4	B	Closed
5	B	Closed
6	C	Not started
7	C	Not Started

Then, using the logic, I shoud see the following values in the site status colum:

Site	Status
A	Running
B	Closed
C	Planned
D	Not Planned

As site D is not listed in the project status table, the status for that site should be Not Planned

Thanks in advance for your support!

[lbendlin · Accepted solution]

1. Create a reference table

Status	Status ID
Not Planned	0
Not Started	1
Running	2
Closed	3

2. For each Site find the MINX of the Status ID. Add 0 if you want to cover site D.

3. Lookup the Status name for the result.

[Netope]

I am struglin with the coding, would you please help wiht it? Thanks!

[lbendlin]