5 Replies Latest reply on Nov 23, 2010 2:51 PM by Danxyz

    Newbee question on related lists



      Newbee question on related lists


      I am transitioning from MySQL to FileMaker and am stuck on a simple concept.  I have two tables related as shown in the diagram.   I want to be able to view test and select names from related for each record in test.

      I have created a Value List that contains both fields from related and displays only relatedname.  So when I click on relatedID I get the list of relatednames and can select the one I want.  This works but is not the user experience I want. The relatedID field displays the index into related and a separate column is needed to display the actual relatedname.

      What I want is the following:

      - A field that is a combo box listing all of the relatednames in related.  Upon choosing one of the these names, the Id from the corresponding relatedname is entered into relatedID in test.

      - If the combo box does not contain a value I want, I want to be able to type a new value into the combo box and have the entry appended as a new entry in related and the corresponding id entered into relatedID in test.

      This is a simple database process and I just can't seem to figure how to get it to work.  Can anyone give me the key I'm missing?



        • 1. Re: Newbee question on related lists

          [You know, when I wrote this I didn't notice that you're already showing only values from the 2nd field. Maybe the popup will be better for you?]

          The problem is that IDs are what we use to tie data together. But the user does not usually need or want to see them. FileMaker made this possible a long time ago (5.5?). There are options when you create a Value List, and "Use values from a field". 

          Lower down in the 2nd dialog is the option: Show values only from 2nd field

          Then the relatedID field will show the related::names when it drops down or pops up, but when you make a choice it will enter the ID. If you make this a "popup" instead of a drop-down, it will show only the name all the time. Which means you can remove the related::name field from the layout, and see/use only the popup.

          It works the same in Find mode also, shows only the name, but Finds on the ID; this can make a relational search much faster than finding on the related::name field. It is not flexible however, as you can only use values from the value list; but usually that is fine.

          Or, if you really want the drop-down, you can overlay the related::name field on top of the ID, make it opaque and non-enterable. (You can make it transparent by making the ID field 1 pixel tall, with a button/script step to Go to Field, but this is a PITA to work with; can't remember the details.)

          One caveat. When you "show only values from the 2nd field", the INDEX of the 2nd field determines what shows. This matters, because indexes never show duplicate values. If you have 2 related::names which are duplicates, the value list will only allow you to pick the 1st, never the 2nd. So if you use this method you cannot have duplicates in that "2nd field". Best to validate the field to stop that. If you must allow duplicates, then you must show the IDs, or use some technique to make that 2nd field unique for each.

          • 2. Re: Newbee question on related lists

            This can be done, but requires scripting to accomplish.

            First, you'll need a text field, Name, in Test that you can format as your text based combo box. You won't be able to use related::Relatedname for this as you won't have a valid relationship between test and related until you select or enter a name.

            An OnObjectSave script trigger set on this field might work like this:

            Set Variable [$Name ; Value: Test::Name ]
            Freeze Window
            Go To Layout [Related]
            Enter Find Mode []
            Set Field [Related::RelatedName ; $Name ]
            Perform Find[]
            If [Get ( FoundCount ) ]
               Set Variable [$ID ; Value:  Related::id ]
               Set Variable [$Name ; Value: Related::Name ]
               New Record/Request
               Set Field [ Related::Name ; $Name ]
               Commit Record
               Set Variable [$ID ; value: Related::id ]
            End If
            Go To Layout [original layout]
            Set Field [test::RelateID ; $ID ]
            Set Field [test::Name ; $Name ]

            You can format this name field with a value list that works with Auto-Complete BTW.

            Note that this script will need enhancement if your find pulls up more than one matching record and you don't want it to simply select the ID value of the oldest matching name. If you enter partial text, the final Set field step will update name with the full name of the first matching record.

            • 3. Re: Newbee question on related lists

              Thank you for both replies.

              On the first suggestion, pop-ups do solve the problem.  But my understanding is that pop-up menus are not good for long lists ... and I expect my lists to be long.  This is why I was trying to use the combo box.

              On the second person's suggestion, I'll play with some.  You make the comment that a text box can be formated as a Combo box.  How is that accomplished?  From what I can see, a text box cannot be formated as anything other than a text box unless it is linked to a data field in the database.

              One of the issues I'm grappling with is the translation from VBasic on an SQL database to this interface on Filemaker.  In the former, creating UI objects and populating them via programming code was what we used to do.  There does not seem to be a similar model with the built in objects in FileMakerPro.  i understand I could write code in some language to address the FMaker database separately but was under the impression that most anything was possible with FMaker on it's own.  Any more thoughts in this direction would be appreciated.  



              • 4. Re: Newbee question on related lists

                You make the comment that a text box can be formated as a Combo box.  How is that accomplished?  From what I can see, a text box cannot be formated as anything other than a text box unless it is linked to a data field in the database.

                You are correct that you can't use a text box separately from the underlying table like you can with Access or other  Visual Basic type form. That's why I specified defining a field and formatting it as a drop down list with the auto-complete option. This approach, as specified does have a significant limitation. If the name is edited test, it won't update automatically in test 2.

                In many such cases, FileMaker developers define a global field--which has properties very similar to your text box as it's value does not apply to any one record, in the header of the form, format it as a combo box and attach a script for finding the related record and creating a new record in Test if it does not exist.

                Current versions of filemaker, do not support an ID number based value list with auto-complete on the second, name column of data. Thus the scripted work arounds.

                • 5. Re: Newbee question on related lists

                  Very helpful.  I'll explore the global field as you suggested.  Thanks for the pointers.