All,
We have a table called Product which has many columns but the tricky one is multilist, basically its comma separated list. Following is the table structure
| ID | Number | Description | Multilist_01 |
|---|
| 123 | A12341 | Test Description | ,98778,98779,98780,98781,98782, |
| 124 | A12342 | Running Product | ,98782.98781,98780, |
| 125 | A12343 | Obsolete Product | ,98782.98781,98780,98783.98784,98785, |
| 123 | A12344 | Production Part | |
The columns "Multilist_01" is storing all the Id's where the data is stored in a separate table(lookup table called "lists") which has the following table structure
| EntryId | EntryValue | Enabled |
|---|
| 98778 | RTX | Yes |
| 98779 | NTX | Yes |
| 98780 | MTX | Yes |
| 98781 | OTX | Yes |
| 98782 | QTX | Yes |
| 98783 | PTX | Yes |
| 98784 | STX | Yes |
| 98785 | BTX | Yes |
| 98786 | ATX | Yes |
| 98787 | CTX | Yes |
| 98788 | XTX | Yes |
Problem: How would i convert multilist_01 values to actual vlaues?
This is how the end output should look like:
| ID | Number | Description | Multilist_01 |
|---|
| 123 | A12341 | Test Description | RTX,NTX,MTX,OTX,QTX |
| 124 | A12342 | Running Product | QTX.OTX,MTX |
| 125 | A12343 | Obsolete Product | QTX.OTX,MTX,PTX,STX,BTX |
| 123 | A12344 | Production Part | |
Two things happened
1. ID's are converted to Values
2. leading and trailing comma's are removed
Interesting thing which can happen
1. if Lists.Enabled = "No" then even if Multilist_01 field has the ID but it shouldn't be converted
2. Multilist_01 is a character length of 200, if its overflows its stored in a different table
Currently the table had 5 million items with 50 multilists (mulitilist_01 - multilist_50) so I would like to know which approach i need to consider in developing this?
Thanks in advance
Nash