I have categories separated by carriage returns in one field (category) in the category table. I can see this through a portal in my inventory table. The groups of categories are repeated in various combinations throughout the inventory. In all though there are only about 300 categories.
I would like to change the set up so that I can have only 300 (or whatever the exact number of categories is) categories, each in its own record in a new category table. I would like my portal in inventory to look at the various different category records that are linked to it. So my plan was to set up a temporary category table; export each of the values from the category fields into separate records; delete the duplicates from this new set of records; make this new set of individual categories join to the inventory with a join table in between. Is this the right idea?
My first step is to make a new record for each of the values I have in all the current category fields. I want to make a looping script. It goes something like this:
Go to Layout [ temporary category ]
Set Variable [ $V; Value:Get (RecordNumber) ]
Exit Loop If [ $V > ValueCount ( Category::category )]
Set Field [ temporary category::category ; LeftValues (Category::category ; Get ( RecordNumber ) ) ]
Set Field [ temporary category::serial ; Category::serial ]
Go to Layout [ Category ]
Go to Record/RequestPage [Next; Exit after last]
I tried getting this script to run. But it is not working when I get to the set field step. It is returning blank records to me. No error messages are showing in the script debugger. I substituted the calculated value with "1" and this worked. This suggests that maybe there is something wrong with the relationships which I set up between the table occurrences. Here is a picure of the relationships. The checkbox is ticked in the temporary category table to allow creation or records.