Relation via relation table

Vi_Tu · 09-23-2021 10:00 PM

I have a these tables:

Client: id / name …
Equipment : id / name / model/ client_id
Project: id / name / client_id / status

Service: id / description / project_id / equipment_id

The issue I have is with Service. When i press on project_id i see all the projects in the system. I am trying to filter them like this : Project if status == open and if client.id == equipment.clent_id

In shot show only the projects that status open from the client that the equipment belongs to or project.

Any help is appreciated it .

Aurelien

Hi @Vi_Tu

About this:

I assume you also have a column [client_id] in your Service table.

If so, in the Valid_If expression of your column [project_id] of your table Service, set this expression:

FILTER("Project",
  AND(
    [status]="open",
    [client_id] = [_THISROW].[equipment_id].[client_id]
  )
)

We are here using dereferencing expressions. This is the [columnA].[randomB] kind of expressions.

For reference: