Hi! I have a "Projects" table with 2 columns of the professional specialties that need those projects to be executed.
Both columns are Linked Records to a master table of "Specialities.
The specialties can be repeated in the 2 columns, because each column means a group of specialties, and the project could be executed if the assigned professional meets one group or another.
For example: "PROJECT 1"
- can have in the column "SPECIALITIES_1", the specialties: "specialty_1, specialty_2"
- and in the column "SPECIALITIES_2" the specialties: "specialty_2, specialty_3"
- being "specialty_2" common in both groups.
The Projects have an associated price.
I need to know the % of income per specialty.
Each specialty is weighted equally in the project price, if a project has 3 specialties, then each one takes ~33% of the price.
The problem I don't know how to solve is the following.
As I have 2 columns with groups of specialties that can be repeated, I do not know how to make a list that groups both columns discarding duplicates.
In the previous example of "PROJECT 1" with "specialty_1, specialty_2" in "SPECIALITIES_1" and "specialty_2, specialty_3" in "SPECIALITIES_2",
I need to be left with a list such as "specialty_1, specialty_2, specialty_3", and therefore weight the price with these 3.
This I need to transfer to a Dashboard, which fits nicely with Linked Records to group and do the %'s, but I don't know a way to "merge" 2 Linked Record columns, discard duplicates and have a format left for the Dashboard to make me the proper percentage chart.
Any advice on how I can achieve this?
