12 Replies Latest reply on Aug 6, 2012 3:46 PM by MaureenRay

    Help with Checkbox Value List English to Spanish automation

    MaureenRay

      Title

      Help with Checkbox Value List English to Spanish automation

      Post

      I created a Report Card Program for our school district and need help with the comments section.

      Currently, teachers choose from a Value List of comments using Check Boxes.  However, if the Report Card should also be printed in Spanish, they also must check the corresponding Spanish comment.  I would like to  automate this step so that when a box is checked in English, the corresponding Spanish box is check as well. 

      Any tips, thoughts are direction would be greatly appreciated.  Perhaps value lists/check boxes are not the way to go??

      Thank you in advance.

      Screen_shot_2012-08-03_at_9.55.36_AM.png

        • 1. Re: Help with Checkbox Value List English to Spanish automation
          philmodjunk

          Define a table named Comments where you have two fields: English, Spanish.

          Create one record for each comment and put the English version in the English field and the Spanish version in the Spanish field.

          Use Edit Value lists to create two value lists for your two fields. Use the Specify vales from a field option to list the values from English for the first Comments field and set up the second value list to list values from the Spanish comments field.

          Link it in a relationship similar to this:

          OriginalTable::Comments = Comments::English

          Define a text field, formatted with the Spanish value list to auto-enter the data in Comments::Spanish using this calculation:

          List ( Comments::Spanish )

          Clear the "do not replace existing value" check box.

          When you select an English Lanaguage comment, the second comment field will automatically look up the corresponding Spanish comment and enter it into the field.

          If haven't tested this, but I think you can create two table occurrences of Comments with two relationships  to your original table and set up the synchronization to work off either set of check boxes so that clicking a Spanish comment updates the English comment checkboxes in the same manner.

          This also looks like something that could be expanded to handle additional languages if that were needed.

          • 2. Re: Help with Checkbox Value List English to Spanish automation
            MaureenRay

            Thank you, PhilModJunk.  I've been racking my brain out!

            • 3. Re: Help with Checkbox Value List English to Spanish automation
              MaureenRay

              Okay, it's working great,  but a needs some fine-tuning:

              The English Language Value List is now alphabetized.  Is there a way to change that to a custom order? I've looked and tried

              just about everyting.

              Thank you for you help.

              • 4. Re: Help with Checkbox Value List English to Spanish automation
                philmodjunk

                Values in a value list, when taken from a field in a table are always listed in ascending order.

                Option 1: Change the values from:

                made significant progress....

                Achieved grade level...

                to:

                1. Made Signficant Progress...
                2. Achieved grad level...

                including the numbers makes the ascending order work--but only for 9 values. You could precede them with a. b. c. however.

                Option 2:

                Add two more fields to your table of comments: A number field named SortOrder and this calculation field named InvisibleKey:

                Substitute ( 10^SortOrder - 1 ; 9 ; " " )

                In your value list, select InvisibleKey as the second field in the value list. (Also include values from).

                Then specify that the values be sorted by the second field.

                Please note that I have not tried this with check box fields--only drop down lists and pop up menus, but see no reason why it wouldn't work.

                Option 3:

                Replace the current field with a portal to a related table where one record for each comment is list the comments. This portal would list all the comments from the comments table but buttons replace the check boxes. Clicking a button adds or clears that comment from the list of selected comments. Conditional formatting or a calculaiton field provides the visual indicator needed to show which values are selected. Since these values are listed in a portal you can specify a sort order for the portal that sorts on a number field in the table to control the order of the comments.

                This last option takes quite a bit more work to set up so I will wait to see if you are interested before describing the approach in more detail. This option requires a decent knowledge of scripting to make it work.

                • 5. Re: Help with Checkbox Value List English to Spanish automation
                  MaureenRay

                  Got it, thanks.

                  I will continue working on this Monday morning....need a break...have a nice weekend!

                   

                  • 6. Re: Help with Checkbox Value List English to Spanish automation
                    MaureenRay

                    Okay, after trying Option 2 (adding two more fields, SortOrder nad InvisibleKey), I get the error message "<no values defined>" in regards

                    to my value list.  Any thoughts?  Perhaps it doesn't work with Check Boxes?

                    • 7. Re: Help with Checkbox Value List English to Spanish automation
                      philmodjunk

                      I had to try it to be sure. It works, but make sure that Text and not Number is specified as the calculation's return type.

                      • 8. Re: Help with Checkbox Value List English to Spanish automation
                        MaureenRay

                        Yes, Text is selected for the Calculation result, still getting <no values defined>, after I select InvisibleKey as the second field in the value list.  See screenshots.

                        • 9. Re: Help with Checkbox Value List English to Spanish automation
                          philmodjunk

                          Why do you have the "Res-sort values base on:" check box selected? I don't see the reason for that, but in my test file, selecting it does not affect the results, it's just the one difference I can spot at this point.

                          Make sure that there is actual text in the invisibleKey field. Put it on a layout and double click the contents of the field. If you can't select any text in it, check your calculaiton and make sure that there is a space character between the quotes in the substitute function.

                          What version of FileMaker are you using? I can upload a simple demo you can use for comparison to yours.

                          • 10. Re: Help with Checkbox Value List English to Spanish automation
                            MaureenRay

                            There is no actual text in the InvisibleKey field. Yes, there is a space between the two quotes. I think I figured out the problem -- I need to acutally input data into the sort field.  I'm a self-taught file maker user, I appreciate all your help.  I know our teachers will be thrilled with the end-result.

                            I'm using FMProAdv11.  if you could upload the demo file, just in case, I"d be much obliged.

                            • 11. Re: Help with Checkbox Value List English to Spanish automation
                              philmodjunk

                              I tested in FMP12. I don't think that you need the demo as you have identified the issue. You have to enter numbers into the sortOrder field to specify the order in which they should appear in the checkbox field.