10 Replies Latest reply on Jan 7, 2015 8:49 AM by RolandGomez

    retrieving name from multiple fields in multiple records.



      retrieving name from multiple fields in multiple records.


      Filemaker World!,

      Oye. I'm sure this is an easy one for y'all. I've been playing with filemaker for around 6 months and this is a hiccup I can't figure out. Here we go. We're a real estate company with 1) Multiple Employees who protest taxes for 2)Multiple properties. In some cases our multiple employees tag team to reduce taxes for a specific property. My goal is to retrieve all of the properties a single employees is associated with. Due to billing this is how I've had to set it up. Tax Consultant No. 1 and 2 are individual fields(which is located in our Properties table) that pull from a Value list that is connected to Employees table

      Property A: Tax Consultant No. 1-Daniel;  Tax Consultant No. 2-Joshua

      Property B: Tax Consultant No. 1-Daniel;  Tax Consultant No. 2-Casey

      Property C: Tax Consultant No. 1-Roland; Tax Consultant No. 2-Daniel

      Property D: Tax Consultant No. 1-Joshua; Tax Consultant No. 2-Daniel

      In this instance I need to build a report that displays every property (record) Daniel is associated with. My thought was to create a separate field "tax consultant merge" and have a calculation that searches for those records where daniel is either TC No. 1 or TC No. 2. I can't seem to get it right. HELP!!!!!!!!!

      Any information is greatly appreciated

      -Roland (an aspiring filemaker guru...boy do I have a ways to go)

        • 1. Re: retrieving name from multiple fields in multiple records.

          With the exception of the exceptional PhilModJunk, we all have a way to go! :)

          I think for starters, did you try create a find that gives you a found set of the related records?  First a hard coded example. 

          If you went into find mode, typed "Daniel" into field one, then clicked 'New Request' and typed "Daniel" in field two, you would get a found set of records where Daniel appears in Field one OR Daniel appears in Field two.

          Second approach, if Daniel can only appear in fields 1 or 2 would be to use QuickFind in the toolbar, or if you don't show the toolbar, then a global search field with a script, Perform Quick Find attached to an OnObjectExit script trigger.  The script could be as simple as Perform Quick Find, or it could include sorting option, go to layout, etc.  If you use a global field for your own quick find, you should make a provision to clear this field at some point, so the next time you go to the layout you don't see the word 'Daniel' there.

          Speaking of Phil, he did something amazing and published Adventures in Filemaking, here, for free.  It is a wonderful learning tool that covers practically every aspect of finds, conditional value lists, relationships, portals, portal sorting, etc.  Every time I have some free time, I pick thru the sample files and learn something I didn't think was possible--turns out it's pretty easy when a genius shows you the way.


          • 2. Re: retrieving name from multiple fields in multiple records.

            Another idea, is to put a portal on the employees table that shows related records from Properties.  You will see all the Properties associated to that employee, and could use a button with GTRR to pop open a layout of just that record, from the portal row, or all the records in Properties that have been assigned to the employee.  My one concern with this method is how the relationship is set up between employees and properties.  I would think you would need 2 one row, one field portals in place of your 2 fields, with the 2 portals being records related to properties.

            • 3. Re: retrieving name from multiple fields in multiple records.


              We're headed in the right direction and thank you for that! I did have some success in pulling all of the properties "Daniel" was associated with...but...here's a little more detailed I should have mentioned previously. The following picture demonstrates the report that I have to create and just like you instructed these are the four properties that "Daniel" is associated with. Great. every field located to the right of the Property Tax Consultant has to deal with his personal income. Great. 

              Please note: Record No. 2 states David (not daniel). Why? because daniel is Property Tax Consultant No. 2 in this instance. What I am desperately trying to get Filemaker to do is to demonstrate the string of information associated with only "Daniel". So It would show like this.

              First Line: Daniel(PTC No. 1)-string of related information

              Second Line: Daniel (PTC No. 2)-string of related information

              Third Line: Daniel (PtC No. 1-string of related information

              and so on.


              • 4. Re: retrieving name from multiple fields in multiple records.

                Here's the report for Property Tax Consultant 2 and just as I mentioned before, you'll see "Daniel" in the second row because he was PTC No. 2 in that particular record

                Thanks again for your insight!

                • 5. Re: retrieving name from multiple fields in multiple records.

                  Well since I am learning new things from this forum myself from time to time, I have a ways to go also.. wink

                  You might find this thread of scripted find examples a helpful resource: Scripted Find Examples

                  The Adventures In FileMaking series only consist of two issues narrowly focused on specific concepts with a third one on Many to Many relationships getting off and on attention from me hopefully to be released in the not to distant future. So Steve's glowing testimonial while welcome, describes a scope to this series that does not exist at this time.

                  And while the files are free to download and use however you want, please note that they are my personal venture into "Geek Busking" and I do have a "tin cup" for donations proffered in the introduction layout for each file with instructions in how to say "thank you" in a material way if you choose to do so..

                  The current files:

                  Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
                  Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

                  Caulkins Consulting, Home of Adventures In FileMaking

                  • 6. Re: retrieving name from multiple fields in multiple records.


                    Thanks for the response. It had everything to do with the relationships just like you briefly mentioned. You've helped with a two week stump! Now to finish if off!!!!

                    Best regards!!!!!

                    • 7. Re: retrieving name from multiple fields in multiple records.

                      Matching records by name in a relationship--except for certain narrowly focused uses such as is used in some examples in the adventure files, is not a good idea. Names are not unique, people change their names and they are very vulnerable to data entry errors. If you base a relationship on a name, any of the above can result in having to change the name in a field and this then breaks the link to previously related records--and this is readily avoided when you link by a unique Serial number or Get ( UUID ) generated value.

                      • 8. Re: retrieving name from multiple fields in multiple records.

                        Really? The fields named Property Tax Consultants 1 and 2 refer to our employees that protest taxes for the general public. Those names will stay the same in perpetuity. As new employees come in they are added to the value list of which I have linked to the Employee Table. A peer and I are the only ones who have access to alter these fields. thanks again for sharing your knowledge.

                        • 9. Re: retrieving name from multiple fields in multiple records.

                          And how do you guarantee that those employees will never change their names--say due to a change in marital status or because one doesn't like their birth name? And you'll never get a new employee whose name is the same as a current one? Or you hire someone named John Smith and after a while, he comes to you and tells you "my name is "John Smythe" not "John Smith"....

                          • 10. Re: retrieving name from multiple fields in multiple records.

                            good point. I'll make the change.