Hi, I am trying to change the color of a container based on if a specific code exists within a different collection. The caveat is that year after year the color changes, however the code remains the same against the record in the collection e.g.
I have two collections, one holds the main details (Records) and has a route_code. Based on that route_code I would like the container to be the associating color_hex value pulled from the (Codes) collection, so in this example for user 123456, the container color would be #EE0000 (red)...
Collection (Records)
user_id - 123456 / route_code - 999
Collection (Codes)
route_code - 888 / color_hex - #CCCCCC (grey)
route_code - 999 / color_hex - #EE0000(red)
I know I can probably do a load of conditional value statements e.g. if 'Records' route_code = 999 then fill color to #EE0000. However I don't really want to 'hard code' them as conditional statements because (1) there are about a hundred route_codes and (2) next year the colors will change so I would like to avoid another version of the app because of the intended audience.
I have split them in to separate collections so I can simply update the color_hex value in the 'Codes' collection and it would apply to all those records that have 999 as the route_code in the 'Records' collection. Hope that makes sense.
I 'sort of' have it working by using this conditional value statement...
IF route_code from 'Records' equals route_code from 'Codes' THEN Color From String is color_hex from 'Codes' (see screenshot)
However this only seems to work for one entry in the 'Codes' collection. By that I mean it will change the color of the container to #CCCCCC (grey) if the route_code in 'Records' is 888, but if I change the route_code in 'Records' to 999 it does not change the color of the container to #EE0000 (red) it just defaults back to the ELSE color.
I have added two database backend queries...
Wrap lists documents from 'Codes'
Column lists documents from 'Records'
What am I doing wrong? or is there a better way?
Thanks