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.
Thank you, PhilModJunk. I've been racking my brain out!
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.
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...
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.
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.
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.
Got it, thanks.
I will continue working on this Monday morning....need a break...have a nice weekend!
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?
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.
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.
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.
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.
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.
Super. Thanks again!