Checkboxes >>> SubTable

[Wickerman]

I'm working on updating a database solution in which there is a Field, call it TYPE, which has been used with a Checkbox data-entry field. In the updated solution I am going to convert this field into a related table so that the multiple values can be manipulated on a more granular level.

So, I understand that TYPE is currently a Text field with multiple values separated by carriage returns.

I know I've done this before, but have forgotten how it is that I managed to take the data within a field like this and split the carriage-returned values into individual records in a child table. A looping script, I'm thinking?

But I'm forgetting what the steps are -- how to "grab the first value" /"grab the second value" etc. and then move on to the next record. I feel like there's a useful script step or function that isn't coming to mind . . .

Any pointers / reminders appreciated.

[bcooney]

Easier to show with a demo.

btw, I would have a Type table and the child table would store a TypeID.

CheckboxConvert.fp7.zip

[Wickerman]

Thanks Bcooney -- will have a look at this tomorrow.

Meanwhile, could you explain the benefit of storing a code from a Type table instead of simply entering the values as words? Is it the savings of using, say, 2 characters per 'Type' rather than the 4-9 characters for spelling them out? It would be really useful if I could also have a sense of what *functional* benefits, if any, you get from doing it this way. Thanks!

[bcooney]

The benefit of having a Type table are that you can add more info about each Type and change its name without breaking any relationships, since you reference the Type by its ID. To me, reporting by an ID is more accurate than a text value that might have been changed over time. I typically build an interface to editing the Type table in my Preferences area of a system (Admin Only).

[dansmith65]

In addition to what bcooney said; it will prevent breaking any scripts where you have referenced the value (like in a find request saved in the Perform Find step).

[comment]

See also:

http://fmforums.com/forum/topic/80943-one-to-many-to-many/page__view__findpost__p__376457

[bcooney]

Oh, my, how we did go on in that thread, comment! :rolleyes:

[comment]

Nostalgic already?

[Wickerman]

Heh -- that was quite a thread. Back to this one, though, thanks again for that demo file, bcooney. It was very instructive, and since I'm still getting comfortable with Looping scripts, it serves as a very useful template for me to refer to in similar tasks.

If anyone else should find this thread and use the demo, I'd point out one potential snag: if any of your Parent records have *no* Values in the checkbox field, then the script will get stuck in an endless loop as the $counter will go to -1, rather than =0 and never get out. You can allow for records with blank value fields by changing that ExitLoop step to $counter < 1.

Thanks for the other remarks about the Type Table -- I think I'm seeing the point but will need to keep mulling it over. I'm not quite convinced that a Type Table is called for in a situation where the Types have no further need for fields to be added to them, and where the values are unlikely to change or vary. Do advanced developers typically use Tables to hold almost all Values sets, and base Pull-down menus on these Tables, rather than using the simpler Define Values sets? Does it ever get to a point of silliness? -- a table for the values Yes/No/Maybe?

[doughemi]

Speaking as a non-professional developer who has been SERIOUSLY bitten by this situation, unless you are absolutely sure that no suit will ever stroll by and say, "we need to add 'x' to the list of choices", go with a table for anything more complex than yes/no.

[bcooney]

Custom value lists cannot be imported. So, if you allow a user to Edit a value list, and then need to migrate data....

By the way, I rarely do Yes/No. I just do Yes = flag_Active = 1 formatted as a checkbox, for example. Unless the requirement calls for a specific "No."