7 Replies Latest reply on Sep 24, 2012 7:21 AM by philmodjunk

    Retrieve data from a record based on 2 search fields



      Retrieve data from a record based on 2 search fields




           I'm relatively new to FileMaker, and I'm using FileMaker Pro 11, but for this issue I don't think there should be a difference.  I've read lots of posts, the help guide, and browsed through FileMaker books but haven't found a situation like the one I'm trying to solve.

           I'm trying to auto-populate fields for "room information" from a separate table (not associated with the current layout) once the user inputs the 'Building Abbreviation' and 'Room Number'.  Those two elements should be sufficient to find a unique record that already exists in a static, 'master-room-list' table (and yes, it's already a related table by the 'Building Abbreviation' field).  Once the user selects a building and room I'd like FileMaker to run a script that finds the record in that other table and automatically fills in the rest of the blanks (fields) in the current table/layout based on the matching data from that record.  I essentially need to do a "lookup" or "find" with a 2-field criteria, not just a single field.

           Any input on which scripting commands to use would be greatly appreciated.  I've gone about as far as trial and error will take me with no success.  I also already understand the scripting triggers aspect of this issue, so I really just need help on the script and calculations needed to perform the task.  Below you'll see a screenshot of what I'm working with.  The two drop-down lists are the two fields the user would fill in, and they are working fine.  I'd like the rest of the fields, which all exist with the same names in a separate table, to auto-populate based on the entry.  Thanks!


        • 1. Re: Retrieve data from a record based on 2 search fields

               If master-room-list has one record for each room, then you need a relationship that matches by more than building abbreviation. It should also match by room number. This is sometimes referred to as a "multi-predicate" relationship.

               RoomInformation::BuildingAbbreviation = master-room-list::BuildingAbbreviation AND
               RoomInformation::RoomNumber = master-room-list::roomNumber

               With such a relationship in place, no scripting is needed. You can now use either a dynamic or static method to display the data from the master-room-list table. Which option is best depends on the needs of your users when using this system.

               See this thread for a description of how to set up both methods and how they differ from each other:  Auto Fill

               Note that no scripting at all is needed to make this work.

          • 2. Re: Retrieve data from a record based on 2 search fields

                 Thanks for the quick response!

                 So your solution works, and I had tried it before but probably had some other variable out of whack at the time.  It does screw up one thing I had going for me though.  Before adding the 'Room Number' to the table relationship, when I would select the 'Building Abbreviation' it would auto-populate the 'Room Number' drop-down with the room numbers in that particular building.  When I add 'Room Number' to the table relationship, this feature no longer works.

                 This feature worked by setting a value list for 'Room Number' using the settings shown in the image below.  I know there's got to be another way to do this, but what would you suggest?  Why does changing the table relationship cause this setup not to work?

                 Thanks again.

            • 3. Re: Retrieve data from a record based on 2 search fields

                   Because this is a conditional value list that uses the "include" option, with the changed relationship, no room numbers appear in the value list because the new version of the relationship no longer matches to any records until a room number is present in the room number field.

                   This can be easily fixed.

                   In Manage | Database | relationships, make a new table occurrence of master-room-list by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as master-room-listByBuilding.

                   We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                   Add it to your relationships like this:

                   RoomInformation::BuildingAbbreviation = master-room-listByBuilding::BuildingAbbreviation

                   Then you can update your value list to list values from master-room-listByBuilding instead of master-room-list and your value lists will work once again.

              • 4. Re: Retrieve data from a record based on 2 search fields

                     It took me a little monkeying around but I got it to work.  I really appreciate it.  I hope you get paid to answer all these posts!


                     I have another question.  It's similar but different circumstances.

                     I want to be able to add pieces of inventory to this "Room Info" table by inputting a unique serial number I've called 'CTS ID'.  Each inventory item is its own record in a separate table, "Inventory".  When I enter an item into the "Inventory" table it assigns it a unique, 7-digit serial number in the field 'CTS ID'.  So on my other layout (Room Info), I'd like to be able to put in the CTS ID for each inventory item in that room and have it fill in related fields from each record.  The problem I'm up against is that it's returning the serial number for the first item I enter in each of the serial number fields that follow.

                     You can see in the screenshot the fields that I'm trying to populate.  What I've done at this point is create a unique 'CTS ID' field for each type of item (so one for Mac, one for PC, one for Projector, etc.).  Then when I input data in that particular 'CTS ID' field it can store the value in the "Room Info" table but also fill in the other fields (Mac_Serial, PC_Serial, etc.), grabbing the data from the "Inventory" table.  Does this make sense?

                     I'm fairly positive this will take some scripting, and I'm not adverse to putting a little "Go" button next to each 'CTS ID' field that makes the lookup happen.  If that's the method I go I'd like to script it in such a way the same script works for each object type instead of writing a script for each, but the script would have to know to only do the lookup for the current field, not all the fields on the layout.  Thanks for any help...


                • 5. Re: Retrieve data from a record based on 2 search fields

                       You need to use separate records for each item instead of separate fields. A releated table listing such items will make things simpler and more flexible to work with in a number of different ways.

                  • 6. Re: Retrieve data from a record based on 2 search fields

                         I don't disbelieve you, but my goal was for all this equipment to be contained inside the room record, so that a single room record would show all the room info plus all the equipment in that room.  Is this unrealistic?  I already have the single-item inventory records where each piece of equipment will have its own record.

                    • 7. Re: Retrieve data from a record based on 2 search fields

                           A portal to the related table of equipment is one way to achieve that visual effect. List view layouts based on the equipment list and data from the related table of room information is another.