14 Replies Latest reply on Dec 15, 2011 4:12 PM by philmodjunk

    How to Create a Lookup Table??



      How to Create a Lookup Table??


      I am incredibly green when it comes to FileMaker, so please bear with me.  I am working on a database application in FileMaker that will be used on the iPad.  That is just a side issue.  What I am looking to have is a list of names in one area of my layout where users can select that name, then, in another area of the layout information about that selected individual will show.

      For example, I would be showing a list of 100 people (Last Name, First Name) in some form of lookup table.  When the user selects Doe, John - the layout updates with a photo of John Doe, his address, phone, email, etc...  I have looked at portals, but they seem to go the opposite way.  They seem to show detail related to a selected record.  In my case, I need to show the list of names from which the user can select to then show detail elsewhere in the layout.

      For my design, I don't want to have a drop down box or something like that.  I want the names showing in a visible list where the user (on the iPad) can simply tap the name and magically the photo, phone, etc.  show in another area of the layout.

      Does this make sense?  Any ideas and help will be appreciated.  Sorry if this is very basic.  Thanks in advance.

        • 1. Re: How to Create a Lookup Table??

          This sounds like a typical master - detail layout where you select from a master list and then get a detailed look at the data associated with your selection.

          You can do that with a special cartesion self join so that all records in your table are listed in the portal.

          I'll call your table PersonalInfo. You need this relationship in Manage | Database | Relationships:

          PersonalInfo::AnyField X AllPersons::AnyField

          AllPersons is a new occurrence of your PersonalInfo Table. You create it by selecting it, then clicking the duplicate button (two green plus signs) to make a copy of this table occurrence box. You can double click this box to pop up a dialog where you can rename it like I have here.

          Now you can place a portal to AllPersons on your PersonalInfo layout and it will list all records in the PersonalInfo table. You can set up the fields in your portal row as a button so that tapping a portal row will select that portal record and perform a script to bring up the detail info.

          The script can use one of two approaches to produce the same results.

          1) It can perform a find for the Serial ID of the selected portal record. You'd then add fields from PersonalINfo and/or any related tables and the find will switch you to that record and display the detail info.

          2) you can add a field with global storage to PersonalInfo and make yet another self join relationship like this:

          PersonalInfo::gSelectedPersonID = SelectedPerson::PersonID

          where PersonID is the serial number field that uniquely identifies each record in your table. Then you add fields from SelectedPerson or that are from table occurrences that are related to SelectedPerson to your layout to show the details.

          In this second case, your portal script just uses set field to copy the PersonID of the selected portal record into gSelectedPersonID and then, if the layout doesn't refresh smoothly, commits the record.

          • 2. Re: How to Create a Lookup Table??

            The instructions make sense.  I did create the second occurrance of the table and linked the two together.  I then created the portal using the field in the new occurrance of the table.  However, when I exit layout mode, the portal is only showing the name of the person who is showing in the other part of the layout - it isn't giving me a list of all names from the new table occurrance.  Did I miss a step?

            • 3. Re: How to Create a Lookup Table??

              What operator did you specify in the relationship? = or X? It should be the cartesian join operator: X. You have to double click the relationship line to bring up a dialog where you can change the = into X.

              • 4. Re: How to Create a Lookup Table??

                Phil.....  Perfect!  Worked like a charm.  Wondering if you could give me some advice on how I can tweak this a bit.  The list of names are actually members of the Utah legislature.  For users, it would be helpful if I could redefine the portal contents to include only House members, and then select a button above the portal to include only Senate members.  I got the inital to work fine, as I set up the portal, I had it filter on my House field (either "House" or "Senate").  When it initally loads names into the portal, I have it filtered to "House" or members of the House of Representatives.  I have a button above called Senate.  I would like to run a script that changes the contents of the portal to Senate member only - seems like I would simply be changing the filter.  I can't seem figure out how to do this in a script, but it seems like it would be fairly easy.  Thanks again.  You are awesome.

                • 5. Re: How to Create a Lookup Table??

                  If you set up a field on your layout (usually, this is a field with global storage) for specifying either "House" or "Senate", then you can define a portal filter such as:

                  LayoutTableOccurrence::FilterField = PortalTableOccurrence::LegislativeBranch //use your table and field names in place of mine here

                  The trick is to add a script trigger on the filter field that does this each time a different value is selected in the filter field:

                  commit Record
                  Refresh Window [flush cached join results]

                  There is an alternative approach that you can consider: This method would allow you to select House, Senate or Both and does not require a script.

                  Define your Filter field (must be text) to have global storage so that you relationship changes to:

                  PersonalInfo::gFilterField = AllPersons::LegislativeBranch

                  Format gFilterField with a check box group of the two branches and then, if you select "House", you see house members. If you select "Senate", you see senate members. If you click both check boxes, you will see both.

                  • 6. Re: How to Create a Lookup Table??

                    I did get the alternative approach to work, but it isn't quite what I am looking for.  I would prefer the user "tap" or "click" on a button that says "House" or "Senate", and then the portal would show House or Senate members.  Not a big fan of the checkbox approach.  I can't figure out your first suggestion.  Here is what I have......

                    Main (table):  includes all of the information on individual legislators - I did create a global field here called gHouse

                    HouseLookup (table):  new occurance of Main

                    Within Main, I have a field called House which contains either "House" or "Senate"

                    That is the basic layout.  I can't figure out what to include in a script to attach to a "Senate" button and a "House" button to change the filter.  I think I have the raw materials established, but I'm not understanding your guidance.  However, I really do appreciate your help.  I'm beginning to get it.

                    • 7. Re: How to Create a Lookup Table??

                      Create the two line script.

                      Select the filter field and select a script trigger for it. If this is a pop up menu formatted field, use OnObjectModfiy. If you prefer to use a drop down list, use OnObjectSave.

                      Since you are planning to use an iPad, you might want to use this script instead:

                      Using the original filtered portal with X relationship:

                      Set Field [PersonalInfo::gFilterField ; Get ( ScriptParameter ) ]
                      commit Record
                      Refresh Window [flush cached join results]

                      Now add two buttons to your layout and specify "House" as the script parameter for one and "Senate" as the script parameter for the other.

                      Using the second, global field based relationship (and no portal filter), you can just use:


                      Set Field [PersonalInfo::gFilterField ; Get ( ScriptParameter ) ]
                      commit Record

                      But this limits you to an either/or selection, you won't be able to select both branches to see all members, Though a Third button for "All" that uses:

                      List ( "House" ; "Senate" ) for the script parameter could be used for that.

                      • 8. Re: How to Create a Lookup Table??

                        That is all working now.  Back to the initial intent, I am not able to select a row from the portal and have it automatically update the detail information for the selected legislator that is also on the layout.

                        Within the portal, I did the "Button Setup" and pointed to a script I developed that was supposed to find the record to display the detail for.  Here are the steps in the script I am running:

                        Enter Find Mode[]
                        Set Field[Main::ID;HouseLookup::ID]
                        Set Error Capture[On]
                        Perform Find[] 

                        ID is the serial ID (numeric) for each row in the table.  Main is the main legislative table that has all of the individual legislator's information.  HouseLookup is the duplicate version of the Main table.  When I select a name from the portal, the entire portal turns dark.  The detail information on the layout stays static - it doesn't change based on the Name selected in the portal.  

                        • 9. Re: How to Create a Lookup Table??

                          When you enter find mode, all non global field go blank so the second line, the set field step, won't work here. You'll see example scripts in the forum here where this script does work, but only when the field from which the value is taken has global storage specified.

                           Since ID cannot be a global field, do it this way:

                          Set Variable [$ID ; value: HoustLookup::ID]
                          Enter Find Mode[]
                          Set field [Main::ID ; $ID ]
                          Set Error Capture [on]
                          Perform Find []

                          If you use a global variable, $$ID instead of $ID, you can use conditional formatting on the fields in your portal row to specify a fill color when $$ID = HouseLookUp::ID and then the portal row will automatically "highlight" to show which record was selected by the tap or click on the portal row.

                          • 10. Re: How to Create a Lookup Table??

                            Phil....  I'm sorry I keep bugging you.  What you note above makes sense and is very helpful.  I didn't know all of the fields went blank when entering Find.  I did make the change to my script.  Now, when I select a name from the portal, regardless of which one, it always goes to the same record.  Additionally, the result set now only has one record (the one it found).  When I select a different name, is it going to try to search through the entire table, or only the result set of the previous Find - which only has one record.  I could understand at that point why it isn't moving to another record.

                            However, it isn't making sense that regardless of the name I select from the portal, the same person is found.

                            • 11. Re: How to Create a Lookup Table??

                              A bit more info.  As I set this up, with a ton of your help, I can choose to have the portal show House members or Senate members.  What I am noticing is it appears to be going to the first record in either result set.  So, if I am looking at the portal with only House members and I chose a House member from the portal, it displays the first House member in the result set.  If I select Senate, the portal now only shows Senators.  When I select any Senator from the list, detail comes up for the first Senator in the result set - not the one I selected.

                              • 12. Re: How to Create a Lookup Table??

                                The find will produce a found set of one, but it should be the record selected in the portal by clicking or tapping a button in that portal. Each find searches the entire table so the fact that you now have a found set of just one records should not be a problem.

                                Make sure that the button is fully within the portal. The easiest method is to select all fields in the portal while in layout mode and then use button setup to turn them into the button to run this script.

                                If you have FileMaker Advanced, enable the script debugger and then run this script to watch everything that happens step by step. If you do not have advanced, add a Show Custom Dialog step just after the perform find to pause the script. Put $ID in side the dialog so that you can see what value was entered into the variable. Compare this value to the ID's of the records in the portal to see which record has this ID number.

                                • 13. Re: How to Create a Lookup Table??

                                  I suppose that goes back to a few very basic questions.  First, I need the portal to be showing only the names of the legislators.  If it is including every field in the table, it is going to be about 25 fields in width.  In Layout view, if I select the portal, it just selects the entire portal.  I can't see that it has discrete fields within the row.  I would guess I am tying my script to the entire portal, but I have no idea how to select a single field within the portal in Layout.

                                  • 14. Re: How to Create a Lookup Table??

                                    First, I need the portal to be showing only the names of the legislators.

                                    That should not be a problem. You only need add the fields to this portal that you need to see. No other fields need be added.

                                    Enter layout mode.

                                    Select all of the fields in the portal row. Then pull down the layouts menu and select button setup. Specify Perform Script as the button action and select your find script as the script to be performed.

                                    What should then happen is that if you click or tap the name field in the portal, the record for that one legislator should be found and fields from that one record should be displayed.

                                    The detail fields are added to the layout, but located out side of the portal.