8 Replies Latest reply on Nov 17, 2011 9:08 AM by RobbWright

    Can you script this?

    RobbWright

      Title

      Can you script this?

      Post

      I have a database of about a thousand records imported from an Excel spreadsheet where several fields were filled with either checkmarks (√) or x's. I would like to instead express that data as a single field with several corresponding checkboxes. I have no trouble creating such a field and populating it with the relevant value list, but it's getting all the checkmarks in that kills me -  it would be a lot of work to manually convert that data from the several existing individual fields. 

      Does anyone know if there is a scripting function whereby I could automate this process in the software - ie have it, for each record, look up the values for each of the relevant individual fields and enter a check or not for the corresponding checkbox in the new field? Forgive me if this is scripting 101, but I'm more on the design side so go easy on me...thanks

        • 1. Re: Can you script this?
          philmodjunk

          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.

          • 2. Re: Can you script this?
            RobbWright

            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...

            • 3. Re: Can you script this?
              RobbWright

              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. 

              • 4. Re: Can you script this?
                philmodjunk

                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.

                • 5. Re: Can you script this?
                  RobbWright

                  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?

                  • 6. Re: Can you script this?
                    DavidJondreau

                    Robb,

                    If the calculation is stored, then after it calculates the result, you change the field from calculation to text and the values wll persist.

                    • 7. Re: Can you script this?
                      philmodjunk

                      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.

                      • 8. Re: Can you script this?
                        RobbWright

                        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!