I have a few apps that store multiple values in a single field and would like to look into normalizing the data. I am starting to work on analysis graphs for the data, and with the multiple values, it is difficult to do that.
Example: I have a field “things” that has a value stored as “1;10;12;16”. The values are semi-delimited and refer keys on a different table for the labels of those items.
I think to better analyze the data, I should break the field “things” out to a different table. The new table would use the record ID as the foreign key. Each value would be a new line.
Does anyone know of any examples of this being done? I can wrap my head around a few things, but I am missing some basics, such as how to refer to that new table for building a menu on a form and selecting the correct values. SC breaks apart the semi-delimited values easily, but I can’t seem to find how to do something like I feel I should do.
Also, is my thought about breaking the semi-delimited values to a new table warranted? Is there a better way to treat multiple value fields?
Thanks in advance.