I want to use the cells in a single-line table as ‘constants’ that can be used by another table. For example:
The Products table has product details and one column is Colour. Associated with each Colour is a ColourValue (in an adjacent column).
The Colours table has a single line with one column for each permitted colour (Red, Green, Blue). In each cell is the value for that colour. Alternatively, it could be arranged with a pair of columns for each colour: ColourName1, ColourValue1, ColourName2, ColourValue2… etc.
I want to connect the two tables such that when a ColourValue changes in the Colours table, then the ColourValue entries in the Products table is changed for every record in the Products table that has that Colour. So, if in the Colours table the value for Red change from 5 to 7, then every entry in the Products table that has Red in the Colour column should have its ColourValue updated to 7.
Note that the Colours table is changed by an external source using the API so the whole process has to be automatic.
Can it be done? Or is there another way?