Solved: Relationship between tables - list through virtual...

Matias_Santiban · 07-05-2021 08:02 AM

Hello everybody,

I’m having trouble inserting an expression into a virtual column that will return a list of the data that relates between tables.
My application is based on issues of Health and Industrial Safety. The relationship I am trying to find is the following:

1. I have a table with the list of people belonging to an organization, where the employee number is the Id of the table, (EMPLOYEE ID).

2. I have another table with the tasks assigned to those people within the organization, where the key is TASK ID that are assigned to “Assigned Employee”.

3. I have another table that identifies the personal protection elements that the company has, the key is ID EPP.

4. Finally, I have a fourth table where for each task (Task ID) I have assigned personal protection elements (ID EPP). In this table the key is ASSIGNED EPP IDs.

I am trying to create a virtual column in the first EMPLOYEES table, where by selecting a particular employee this virtual column can show me the Personal Protection Items that should be assigned according to the tasks that they perform. I understand that there is a relationship between the tasks that are assigned to employees and that in turn personal protection elements are assigned to these tasks, what I cannot achieve is being able to write a formula that expresses this in a list in the virtual column.

I would appreciate any comments to guide me to the solution.

From already thank you very much

Steve

Edit the column’s configuration:

Make sure the Element type is set to Ref and the Referenced table name is set to EPP.

View solution in original post

Steve

Maybe this?

SELECT(
  ASSIGNED EPP[ID EPP],
  IN(
    [Task ID],
    SELECT(
      TASK[TASK ID],
      AND(
        ISNOTBANK([Assigned Employee]),
        ([Assigned Employee] = [_THISROW].[EMPLOYEE ID])
      )
    )
  )
)

This is an expensive computation and will likely extend sync times of your app, especially as you data grows.

Matias_Santiban

Steve,

First of all, thank you for your prompt response. Also, let me tell you that the function is working properly, it just doesn’t show me the list in the view, just the count of the items in the view title. What can be caused this?
By the way, another thing I see is that it shows the summation of all the elements, so it would be showing me repetitions of personal protection elements, how can I include in the formula that shows me a unique value for each element.

(Each element can be assigned to more than one task, but it does not mean that you must give that person that many repeated elements, but only one that you can use in each task in which it is necessary)

Also, ask you what other way you could recommend me to prevent this from making the application work less efficiently. For now I have no problems, but could in the future if the volume of data increases.

From already thank you very much for your time.

Steve

Please post a screenshot of this.

Matias_Santiban

Steve, I was able to solve this problem, now it shows me the list of personal protection elements assigned to tasks according to the person who does those tasks.
The drawback is that it shows me its EPP ID and not its Name (label) and they are repeated, since for different tasks you can have the same EPP assigned. It should take unique and non-repeating values.

Steve

Make the ID EPP column of the ASSIGNED EPP table a Ref to the table that identifies the personal protection elements that the company has.

Wrap the SELECT() expression with UNIQUE().

See also:

Matias_Santiban

Steve,
I have done what you have indicated and the UNIQUE() addition worked perfectly!

However, I have the tables configured as you have indicated and I cannot see the name (EPP) of the personal protection element instead of the EPP ID. I attach the configuration here:

EPP table according to Task:

Assignment column ref EPP ASSIGNED to table EPPA Attach the configuration here:

EPP table settings

THANK YOU AGAIN!!!

Steve

Are these the same column?

EPP ASIGNADOS is of type Ref, but EPP Asignados segun tarea appears to be of type EnumList.

Matias_Santiban

EPP virtual column current CONFIGURATION Assigned according to task

I have tried assigning enumlist to the virtual column (it shows the same values) and that message.

Steve

Edit the column’s configuration:

Make sure the Element type is set to Ref and the Referenced table name is set to EPP.

Matias_Santiban

EXCELLENT, THANK YOU AGAIN FOR YOUR HELP

Relationship between tables - list through virtual column