7 Replies Latest reply on Jul 15, 2014 1:18 PM by MichaelAngelo

    Multiple lookups from same serial number field , pulling serial numbers script and goto record...



      Multiple lookups from same serial number field , pulling serial numbers script and goto record linking


           I'm very new to Filemaker Pro so please excuse any improper use of terminology or syntax. I preplanned my database as recommended, have built the basic layouts, have already entered some basic data with much success but still have a couple questions if I may. Here’s the structure for my Dbase, pretty simple so far:


           - Each record has a unique Contact ID with "Auto-enter Serial Number"

           - Each record contains contact info, type of contact check boxes, Projects ID numbers, Projects titles and some notes

           - As it stands now there are fields to manually enter " Projects ID Numbers" and titles


           - Each Projects record has a unique Projects ID with "Auto-enter Serial Number"

           - Each Projects record via validation requires the user to enter the Contact ID for the Projects lead

           - Once the Contact ID for the Projects lead is entered there are "Look Ups" in place that fill the minimal contact info

           - As it stands now there are fields to manually enter " Additional Contact ID Numbers and their minimum info"

           - Each record contains Projects title, description, Projects type check boxes, Projects lead contact with minimum info, additional contacts with minimum info and some notes


           There's much more to the Dbase, but in the sake of simplicity you get the idea. When I create a new Projects record and enter the Contact ID for the Projects lead, the "Look Ups" cause their info to auto enter, wonderful. If I change their contact records it will update in the Projects record, great. So here are my questions


           QUESTION #1


           I created a relationship between the Projects Layout and Contact layout Contact IDs by going into layout mode, copying the Contact ID from the Contact layout then pasting it into the Projects Layout. Then in the Manage Database/Relationships menu I linked the two fields. The trick is I want to have DIFFERENT Contact IDs in the same Projects record and have it "Look Up" the unique contact info associated with each different Contact ID. When I copied the Contact IDs multiple times into the Projects layout of course all that did was repeat the same data in all the Contact ID fields. I'm not really sure how to proceed to have multiple unique contact IDs and their associated info all in one Projects record.

           QUESTION #2


           As it stands now we manually enter the Projects IDs and Projects Titles that any given contact is associated with into each Contact record. There is much room for error and I am wondering if there is a way to create a field in the contact record that looks at all the Projects records, sees if the contact is on a specific record in both the Projects lead and additional contacts fields and if so lists ALL the Projects IDs associated with that contact ID. Bonus points if it can pull Projects titles too.

           QUESTION #3


           The last challenge I'm trying to solve is when looking at a Projects record, I'd like to be able to double click the Contact ID or click on a button and have FMP take the user to that contact record either in a new window or if need be in the current window, setting this up for both the Projects lead contact ID  and the additional contacts IDs,. This would also work in reverse ie being in a contact record, clicking on one of the Projects IDs (preferably from the list in question #2) or a button and having it take you to the appropriate Projects record.

           Thanks in advance for any help with all of this.

           Thank you






        • 1. Re: Multiple lookups from same serial number field , pulling serial numbers script and goto record...


               Well, things are slow on the weekend here, so I'll give this a shot. Things look like they are starting off well, for the most part. There is one thing I'd do differently, and one addition of a table.
               One small change to the syntax. Beginners often use the phrase "Layout" when what they are really talking about is "Table". They seem somewhat similar, as FileMaker automatically creates a layout when you create a table; and every layout must have one and only one table it belongs to. But the fields belong to the table; they may or may not appear on the table's layout(s), and there are often several layouts for a table (actually for the table occurrence, on the Relationship Graph, but let's talk about that another time). You can think of "table's layout", but the table owns the fields.
               Difference: I would NOT add "Projects ID numbers" to the Contacts. As a "contact" might have a different "project" layout. You might add ONE "contact" to a "project" (the "lead"). 
               Answer 1: The way to handle multiple "contacts" to a "project" is to add another table (I'll call it "ProjectContacts"); it is more or less a "join" table. It would have 2 critical IDs, "contact id" and "project id". Then you can add as many contacts to a project as you wish. Each one of them could Lookup whatever you wish from the Contacts table. However any data which remains much the same as the value on the Contacts table could just be seen directly via a (new) relationship, from "ProjectContacts" to a relationship of a new table occurrence of the "contacts table" [I'd name it will all the names getting there, but that's my method].
               So, there could be several records for a project, each with a Project ID and a Contact ID. Each one can either see or Lookup data from the Contacts table (via the above relationship). 
               Answer 2: It is not too difficult if you do the above to see all the "contacts" involved with a Project. It can see the "lead" contact. You can see all the other contacts via the ProjectContacts table (thru the Project). [Or, you could just add the "lead" contact into that table also, and mark it as "lead" (with just [x] 1, Boolean). I think I'd do that. If you ever wont 2 leads to a Project, you'll be glad you did.]
               You can show all these in a Portal. You can sort the portal by [x] lead, then name, or whatever. [Or you can put the lead on its own if you did it your original way.]
          • 2. Re: Multiple lookups from same serial number field , pulling serial numbers script and goto record...

                 Appreciate the response and expertise Fenton. Got it, "tables" from now on, makes sense, thank you.


                 Not sure I understand why I shouldn't include Project ID numbers in Contact table. Both Projects and Contacts have their own tables and layouts (see attached rough Contact layout). Is the idea that this data belongs in a portal only via a "join table"? If I understand correctly I will be creating a separate "join table" that collects the associated Project and Contact IDs then using portals inside both the Projects and Contact layouts to access the data?

                 Please excuse my ignorance. I'm going to review portals and join tables via the manual and google it this week.


                 From a functionality standpoint the goal is to have staff members look at any contact record and see the various project that contact is involved in. Staff could also look at any project record and see all the contact people associated with that project.

                 Heck of a learning curve so I will check back after I do some homework and try to catch up to you folks

                 Wonderful you responded so quickly!!!


                 PS At some point I will also want to deal with question #3 hyperlinking to contact, project or asset records, I'm guessing that will be some sort of "button script" that I can dig up in the manual.

            • 3. Re: Multiple lookups from same serial number field , pulling serial numbers script and goto record...


                   The Contacts table is for data about the "contact." It should NOT be about the data for the Projects. That is basic; especially because a "contact" is going to have several projects. The method of "project 1, project 2, project 3, etc." is NOT the way to do this. It is something that only beginners do. Eventually you will realize that it is actually much more work than setting up the tables and relationships the way they should be. I'm not trying to seem rude; we all started with "data 1, data 2, data 3, etc…" in our early days (I started with FileMaker 3 :-). 


                   It is much better to think of putting the "contacts" in the Projects area. Don't worry, each Contact can see all the their "projects". However, the fact that multiple contacts can share one project means that you need to create another table, "project|contacts", known as a "join" table (because it has 2 critical IDs, both of which come from another table; it also should have its own auto-enter serial ID; may be needed, and just in case). 


                   The only real question here is: Should "lead" contact be in the Project table, or the "project|contacts" table? Well, that's up to you. However, I think I would put it into "project|contacts"; because you can add a simple field as a "flag" (1, Boolean) to show that "this is the lead of this Project". You can then use a relationship including that flag to show ONLY the "lead" when needed, and a relationship with NOT that flag to show all the others. Then another relationship without the flag would show them all. It seems like "extra work", but it allows 3 uses, and that's all you'd need. You could also use any other kind of Find, etc., to deal with these "Project|Contacts" any way you wanted, as they're all together in the join table. Also, as I said, if you ever want to have 2 "lead contacts" handle the same project, it could be easily done; otherwise not.


                   [ I am fond of "flag" fields with have a result (number) of 1 (or nothing). I use their friends, from other tables, which have a calculation, with the result: 1, to let me create a reliable and fast link; good for counts also.]


                   You can see these "project|contacts" in a portal, from the Projects table (layout). Then there is no need for any "contacts 1, contacts 2, etc." nor "project 1, project 2, etc.", as those can also be seen from the Contacts table. Since they're all in the "project|contacts" table, they can be seen from either direction, using they needed ID.


                   This is all handle by "table occurrences" of the tables on the Relationship Graph, and by portals on the "parent" table/layout, looking, thru the correct "table occurrences" to the "child's table occurrences". I likely have such somewhere on my computer, but it's rather late for me to find it.



                   P.S. I have a medical condition, named Aphasia, from a stroke 3 years ago. Which makes it impossible for me to be a FileMaker "developer". So I still do some as a "hobby", and try to answer some questions. However, the writing may seem a bit odd sometimes. I also can only read poorly; I have to listen to know what's been written. Which is an odd way to write. I have to listen to myself to know what my mind and hand has done :-0

              • 4. Re: Multiple lookups from same serial number field , pulling serial numbers script and goto record...

                     Fenton, it is great to see you participating and doing a wonderful job of explaining basic and important concepts.

                • 6. Re: Multiple lookups from same serial number field , pulling serial numbers script and goto record...


                       First, thanks to Bruce and Phil. I can be sure that I said it right with the two of you (I am not always so sure of myself :-).


                       I think that these things are actually easier to see than to read, so I create some quick test files. The "simple" one is the way that someone might do at the beginning stage. It has, I think, the basic methods.



                        The other file shows the same, but more like a developer would do it.* It does much the same as the above. But it leaves you more room to add more operations, without trying to put it all together (which can get messy eventually).



                       Notice the naming convention. It is what I use. It lets me know what exactly I'll looking at; which can matter, so I don't mind the long space it takes.


                       *Well, many, not all :-} (there are other methods). It uses the "Anchor Buoy" method of "table occurrence group" separation. It may not be the absolute fastest, but it is the least confusing (to me), especially if the database becomes more complex.

                  • 7. Re: Multiple lookups from same serial number field , pulling serial numbers script and goto record...

                         Hello Fenton,

                         Sorry for delay in responding been off the computer for a few days. So wonderful you taking all the time to explain join tables, making sample files, and all this with a medical condition, wow! A bit of serendipity as this database is for documentary research and one of the film's biggest topics is examining the nature of joy we receive when we gift to a stranger, your efforts fit right in! What a wonderful community in this forum, thank you so much.


                         Meanwhile I've downloaded your thoughtful examples and we are examining them. I'm guessing it will take us more than a minute to decipher and conceptualize but it is really making sense already. Thank you again and I'll send you a link to the film offline ;)