1 2 Previous Next 27 Replies Latest reply on Jan 30, 2013 8:00 AM by infinityBBC

    Field Value List?



      Field Value List?



           i'm trying to accomplish something with FM Pro Advanced (version 10) that i would imagine is possible, but i just don't have enough skill in programming and wondered if anyone here might help me.  in fact, in the course of trial and error, i somehow accidentally wiped out all my data and work up until this point.  thankfully, i made several backups and found one of them which got my work back.  here's what i'm trying to do…


           i have 10 calculation fields formatted as numbers.  i have another field i wish to format as a pop up list displaying the names of those 10 calculation fields, whereby upon selecting this new field, the user would simply select one of those names which corresponds to the names of the 10 calculation fields.


           the trick is that upon selection of ONE of the 10 names listed in this new field which corresponds to one of the names of those 10 calculation fields, i want the VALUE of that selected field to be placed into the new field which is a calculated number.


           basically, i am trying to create a value list for fields.  is this possible?


           i hope this makes sense!  8-)  i would be glad to email the database to someone if they think they could help me figure this out.


           thank you!






        • 1. Re: Field Value List?

               What you describe is a bit odd. A more detailed description of these 10 fields and what you are trying to accomplish may reveal that there's a very different way to get what you want than what you have currently set up.

               Two methods to do what you want without redeigning your database:

               Method 1:

               Define two fields for your table. Define a text field, FieldName and place it on your list with a drop down list of field names.

               Define a second field as a calculation field:

               Case ( FieldName = "NameofFirstField" ; FirstField ;
                          FieldName = "NameOf2ndField ; SecondField ;
                          // and so forth for all 10 fields )

               Place this calculation field on top of FieldName and use behavior setting in the inspector to prevent access to the field when in browse mode. Give it an opaque fill color so that you can't see the FieldName Field behind it.

               When you click or tab into this field, the drop down list pops to the front and deploys. When you select a field, the drop down list disappears back behind the calculation field, which then displays the value of the selected field.

               Note that you do not have to put one field on top of the other. You can put them side by side if you want to show the name of the selected field and also its value.

               Method 2: Use an OnObjectSave (dropDownLIst) or OnObjectModify (PopupMenu) to perform a script like this:

               Set Field [YourTable::FieldName ; //Put case function shown in Method 1 here ]

               Note that "10 fields" suggests that you may be much better off replacing those 10 fields with a set of 10 related records with just 1 field. And there are very simple ways to select from such a table via a drop down to get the same result as the above methods produce.

          • 2. Re: Field Value List?

                 WOW!  thanks for the help, i just hope i can follow your instructions.  i've used FM for YEARS (i believe since it first came out around 1985-86), but i've never gotten into any real advanced programming.  well, to me everything you've share is "advanced"!  8-)

                 i don't even yet understand "tables" and how they work.  as i mentioned in my post, i somehow deleted all the tables, not knowing how to work with them — i was trying to somehow connect the new field to the 10 calculation fields via those interesting connection lines that you can drag between fields in one of the windows, and the next time i opened the database EVERY field had a missing table!  8-)  but i was able to find a backup i made prior to that mess up.

                 so i'll give your "Medhod 1" a shot, as i THINK i can follow it.  i'm just surprised that FM doesn't have an easy way to use actual fields as value lists, whereby one could simply have those field names BE the list so that when selected the contents of that field are displayed.

                 thank you and i'll let you know if i can get it to work!


            • 3. Re: Field Value List?

                   i'm just surprised that FM doesn't have an easy way to use actual fields as value lists, whereby one could simply have those field names BE the list so that when selected the contents of that field are displayed.

                   I've worked with Filemaker databases since the Flat File days of FMPro 2.5 and have never seen a need for such a value list--probably because I would use an alternative approach that makes such a value list unecessary.

              • 4. Re: Field Value List?

                     thanks so much for your help — i'm trying!  8-)  upon thinking about what would work best for my project, i've decided to follow your "Method 2" option, using a Script trigger & OnObjectModify.  however, i just don't have something correct and would greatly appreciate some additional help.

                     within te script editor, i've chosen the "Set Field" script function.  in the script editor, i've chosen "Calculated result:" and within that window i've set the "Case" function and have attempted to follow your instructions:


                     Case ( "∞CharacterDB::Physical" ; ∞CharacterDB::Physical ; "∞CharacterDB::PhysicalTechnical" ; ∞CharacterDB::PhysicalTechnical )


                     i've tried several variations, including:


                     Case ( FieldName = "∞CharacterDB::Physical" ; ∞CharacterDB::Physical ; FieldName = "∞CharacterDB::PhysicalTechnical" ; ∞CharacterDB::PhysicalTechnical )


                     and a couple others, but nothing seems to work.  sometimes i get "table not found"; other times nothing happens.


                     i'm also concerned about the "Calculation result must be Text" notice in the bottom of the window there.  the thing i'm trying to do is opposite — i'm trying to have the name of those fields be options on the value list (which i've done), but upon selection, the result would be what that field equals already.


                     if it's easier for me to just send you the DB to see what i'm trying to do, please let me know.


                     thanks for helping me with this!  8-)


                • 5. Re: Field Value List?

                       What is the name of the field formatted as a drop down list of field names?

                       Case ( ∞CharacterDB::FieldName = "∞CharacterDB::Physical" ; ∞CharacterDB::Physical ;
                                 ∞CharacterDB::FieldName = "∞CharacterDB::PhysicalTechnical" ; ∞CharacterDB::PhysicalTechnical )

                       Is the correct syntax, but only if you have a field in your table named "fieldName" that you have placed on your layout formatted with a value list of: "∞CharacterDB::Physical" ; "∞CharacterDB::PhysicalTechnical".

                       Note that you do not have to use the actual field names, which will be a bit cryptic for the user, if you set it up like this:

                       Case ( ∞CharacterDB::DropDownField = "Physical" ; ∞CharacterDB::Physical ;
                                 ∞CharacterDB::DropDownField = "Technical" ; ∞CharacterDB::PhysicalTechnical )

                       In this example, the DropDownField's value list would consist of two values: "Physical" and "Technical".

                  • 6. Re: Field Value List?

                         i tried to use what you posted, but it didn't seem to work.  i thought i'd take a screen snapshot so help convey what's going on.

                         the 10 fields circled in red called in the "Aptitudes" section of the sheet are all calculated fields in a number format based upon the Attributes fields above.  they all work great.

                         the field circled in blue under the "Aptitude" column called "W•Apitutde" is where i'm stuck.  i already have the value list created and assigned to this field, so that the 10 Aptitude field names make up that value list.  clicking on or tabbing to this field brings up the selectable pop-up list.  the goal, of course, is to select ont of the Aptitude names corresponding to the Aptitude fields, and have its calculated value be represented in the "W•Aptitude" field.

                         you'll see the script i've created along with the dialog boxes on the right.  when i click "OK" upon setting the "Case" function as i interpreted from your instructions, i get the error "The specified field cannot be found."  so i'm at a loss there, of course!  8-)

                         now, to get to this area to input the "Case" function, i selected "Calculated result:" circled in purple and one of my concerns is that note down below also circled in purple says "Calculaton result must be Text"... which isn't my goal.  my goal is to have the field propogate whatever calculated result exists for the selected Attribute from the pop-up list.

                         i hope this graphic helps to better explain what i'm trying to do and where i'm currently stuck.

                         thank you for all your help!

                    • 7. Re: Field Value List?

                           Set field is not correctly set up.

                           When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                      • 8. Re: Field Value List?

                             OK, i just deleted what i had and started over.  i added "Set Field" to the script, clicked "specify target field", then when the dialog box popped up, the table (only one i have) was automatically selected by default, so i selected the first "Attribute" field from the list entitled "Physical".  i then clicked OK.  then i clicked on "calculated result", but when the box came up, there was nothing in the box (where i previously put the "Case" function).  is there spposed to be a semicolon there already?  i've uploaded a new screen snapshot for clarification.

                             i could create a new "Case" function at this point, but i wasn't sure what to do with it at this point.

                        • 9. Re: Field Value List?

                               Yes, you would enter the case function at this point. And you would not enter the semi-colon.

                          • 10. Re: Field Value List?

                                 OK, i feel that we're getting close!  8-)  however, here's the error i got when i tried what i think you're instructing me to do.

                                 Q: i still don't understand this thing about the semicolon.  which semicolon?  don't semicolons separate the "if/then" type data within the Case function?

                                 Q: if "Set Field" only incorporates ONE of the Attribute fields i'm trying to have this script associate with the value list i created, how will the script be able to deal with the other Attribute fields?

                            • 11. Re: Field Value List?

                                   All Set field script steps have this format:

                                   Set Field [TargetTable::targetField ; Some Kind of calculation here ]

                                   You do not enter the semi colon shown in red above. You use the first specify button to select the targettable::field you want and you use the second specify button to enter the calculation. If that calculation includes semi-colongs, then yes THOSE semicolons must be entered. But the one shown here in red is supplied by FileMaker once you click OK to close the specify calculation dialog box.

                                   The error message indicates that you have entered a reference to a field that does not exist in the table you specified. To avoid small typos that can then produce this error message, find the desired field in the list of fields in the top left part of the specify Calculation dialog and double click it to add it to your calculation.

                              • 12. Re: Field Value List?

                                     OK, cool — that makes sense to me, thanks.

                                     however, and i meant to ask as part of my previous "Set Field" Q, shouldn't that be set to the field that i'm working with here — the "W•Aptitude" field, rather than ONE of the Aptitudes?  i'm trying to understand the logic of what's going on, and it seems that the field i'm trying to "set" is this one, while i use the Case fuction wthin that programming to associate the names of the Aptitude fields with the Aptitude value list.  please correct me if i'm off base here.

                                     however, i've clicked on the "Calculated result:" button to add the Case function to this "Set Field" function, and i selected the "Physical" and "PhysicalTechnical" fields (just to again try TWO as a test) from the actual list (not typing them manually), however i have to type in that "DropDownField" text as you previously inscructed manually, in between the "∞CharacterDB::" & "Physical", and then the "∞CharacterDB::" & "PhysicalTechnical", yet i'm still getting the same error... and those fields definitely exist — i'm choosing them from the tabe list that is there.

                                • 13. Re: Field Value List?

                                       if they existed exactly as you have typed in these names, you would not get this error. I am suggesting that you NOT type in the names, but double click them from the list of fields. This enters both the table occurrence and field names for you with perfect syntax. You can select fields from other tables by first selecting the table from the drop down above the field list.

                                       I can see from your screen shot that you do not have a field named "DropDownField". This was a placeholder name I used in my examples as I did not know the name of hte field you are using. You need to subsitute this name for the name of the field in your table where you select the field name from a value list.

                                  • 14. Re: Field Value List?

                                         OK, cool — i didn't realize the "DropDownField" was a placeholder.

                                         i placed all of the 10 Attribute fields into the Case function, hopefully according to your instructions, and it seems to be almost working... i'm getting no errors at this point, but no matter which Attribute i choose from the value list, the result always equals the "Physical" field (which is 34).  i can select the other field names from the value list when i click on the "W•Attribute" field, but the result is always 34.  any ideas there?

                                         ... but we're so close now i can taste it!  8-)

                                    1 2 Previous Next