You don't even need a script. You could use the calculation option in Replace Field Contents to use the contents of a group of fields to generate a return separated list of values that you could then format as a check box group and the list of values would then produce the expected selections in the checkboxes.
Say you have three fields named: Apple; Orange; Pear
And if there is data in the Apple field, you want your check box field to show "Apple" with a check box and so on for the other two fields.
Use this calculation to load a separate text field with the needed list of values:
List ( IF ( Not IsEmpty ( YourTable::Apple ) ; "Apple" ) ; If ( Not IsEmpty ( YourTable::Orange ) ; "Orange" ) ; If ( Not IsEmpty ( YourTable::Pear ) ; "Pear" ) )
Then format this field as a checkbox group with "Apple" ; "Pear" and "Orange" as its value list and you'll have combined the three separate fields with (√) or x's into a single field formatted as a check box group.
Thanks for such a useful and prompt response Phil. I've done what you suggest, the only thing is that it's making no distinction between √'s and x's - it checks the boxes either way. How would I modify it such that it responds only to checkmarks? I'm assuming there's something that replaces "Not IsEmpty", but I'd be groping beyond that...
Also, is there any way to bake this in as hard data? In other words, once the info is extracted from the other fields, delete them and have this field function as a normal alterable checkbox field? I probably should have mentioned that need from the start.
what difference does that difference between a tick and an x represent?
You can certainly use YourTable::Field = "x" to check specifically for that character, but now you have three possible values, an x, a check and empty and a group of check box values only has two possible values: selected or not selected.
In the original spreadsheet an x just meant no, and a √ meant yes, In some records the field was left blank when the info was indeterminate, but those fields can be edited manually as that info emerges.
I have implemented the alteration you suggested and it's working properly now - thanks again. Now I just have to somehow make the data independent of the original fields - any suggestions there?
If the calculation is stored, then after it calculates the result, you change the field from calculation to text and the values wll persist.
If you use this within a replace field contents operation, the result is fully independent of the original fields. It's data in a text field you can edit as needed after the fact. And you can even delete the original separate fields unless you will be doing additional data imports.
Yes, you're right - I just changed the filed to a straight text field and the data is still there and alterable - fantastic. Thanks for all the help!