1 2 3 Previous Next 32 Replies Latest reply on Aug 3, 2012 5:15 PM by AzizaAbaButain

    Help with relationships on call log



      Help with relationships on call log


      Let me start by apologizing for my noobness, I recently purchased FMP 11 and the FileMaker Pro 11 The Missing Manual book and am rather new at databases and how they function. Nonetheless, I've managed to set up a database with two tables in it and would say I've gotten past the beginner level and moved onto creating relationships, key fields and portals - though I barely understand what it is that I'm doing. That being said, here's what I'm trying to do and what I woudl appreciate some help on. 

      Basically, I run my boss's call log and use it to roll calls. I have simple text fields in this order:


      When I get a call in, I'd like to be able to enter the caller name and have the phone number for that person auto-fill for me, rather than me have to search and find the number and copy/paste it into the number entry. It seems like such a simple action that I'm sure I'm missing some simple step here. I'm willing to rebuild the database from the getgo in order to make this work. 

      I tried making two tables and creating a relationship for them, I tried linking keyfields on both tables and had no luck. I feel totally lost in a foreign country with foreign language directions. Would anybody be able to help me?

      Thanks in advance!


        • 1. Re: Help with relationships on call log

          It's not as simple as you might think. You could set up a relationship based on name instead of ID and then a value list of callers could be set up that auto-completes as you type in the name. But, names aren't spelled in obvious ways and names are not unique. Thus, you might enter Kris instead of chris and not find the correct person and you if you get two or more people with the same name, you could see the wrong phone number appear.

          The solution is to use a script that performs the find for you. It can present a list of matches when more than one record matches and there are several ways to make working with alternate spellings easier to work with.

          Here's a demo file that illustrates several such methods, but you'll need a basic understanding of scripting and script triggers to be able to understand and apply them.


          • 2. Re: Help with relationships on call log

            Hi PhilModJunk, Thank you so much for your reply. I was afraid you were going to say those words, "It's not as simple as you might think." I generally make the caller info unique by putting the company he/she is from in parenthesis aftewards, e.g. Neil Armstrong (NASA) so I think I could make the value list of callers unique in that way. If I did that, could I make that field a key field and have a second table in the database with the phone number associated with the individual callerID (the name and company) so that when that same caller ID is typed in the main table (which manages the incoming and responded to calls), it auto-fills the phone number field next to the CALLER field? Do you think that would work? Here's a better idea of what I'm trying to articulate as a possible solution. 

            One database with two tables. The first table is CALL LOG the second table is CALLERS. 

            The CALL LOG table contains the following fields:


            The CALLERS table contains the following fields: 

            CALLER, NUMBER

            I make the CALLER field a key field in the CALLERS table (though I'm not sure what settings I should choose as far as writing and deleting permissions across tables - do you have any insight here). 

            Then, I take and input our rolodex of caller and associated phone number in the CALLERS table. example: Neil Armstrong (NASA) 650-555-1234  (with the name and company in the CALLERS field and the phone number under the NUMBERS field. With the rolodex entered in the CALLERS table, and the names now individual keys (right?) I should be able to enter in the CALL LOG table and have those auto-fill after I create the relationship. 

            So on the CALL LOG table, do I create the CALLERS field as a key field? Then how do I set up the relationship to associate the phone number and caller from the CALLERS table to auto-fill in the CALL LOG table in the right fields once I start typing a caller's name? 

            I hope that makes sense. Thanks again for any and all help.

            • 3. Re: Help with relationships on call log

              Yes and no.

              You really need a CallerID field that is an auto-entered serial number that links a record in your Phone Sheet to contacts. When you use a name, the uniqueness issue is only part of the problem. You can solve the uniqueness issue by setting up a unique values validation on the field so that if you enter a name in contacts that is not unique, an error message pops up and you can then include additional info to make it unique.

              But that will still leave you with two other issues unresolved:

              1. The name was typed into contacts::CallerID by a human and all humans make mistakes when entering data. If someone enters a contact name as Smith when it should have been spelled as Smythe, but you don't catch the error before creating some related records, trying to edit the name to fix the mistake, breaks the connection to the related records.
              2. People change their names for a variety of reasons. And that sets you up with the same issue as item 1.--changing the name, breaks the links.


              Using a serial number field entered by the computer and which is never permitted to be changed, avoids all of these issues.

              That doesn't mean that you can't use a name--if validated as unique to do what you want, but to avoid the above issues will take adding a few extra features to your database.

              1. Define CallerID as an auto-entered serial number in Contacts and as a number field in Phone Sheet and keep them related in that manner. Update any existing contacts records by putting CallerID on the Contacts layout so you can put the cursor in it and use Replace Field Contacts to assign Serial Numbers to them.

              2. Add a new field to Phone Sheet, gCallerLookUp. Use field options to specfiy global storage.

              3. In Manage | Database | relationships, make a new table occurrence of Contacts 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 ContactLookUp.

                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:

                Phone Sheet::gCallerLookUp = Contacts::FullName     (Make sure that FullName has unique values specified as a validation rule.)

              4. Go to Phone Sheet::Caller ID in Manage | Database | Fields and double click it to bring up field options. Click the auto-enter tab and use the looked up values setting to copy Caller ID from ContactLookup into this field.

              5. Put Phone Sheet::gCallerLookUp on your layout and give it a drop down list of values from contacts::FullName. (Open manage value lists and select "use values from a field" option.) In the inspector, select the auto-complete option.

              6. Finally add any fields that you want from Contacts (not ContactLookup) to your Phone Sheet layout and when you select a contact in the drop down, the contact information from that contact will appear on your layout. You can also use the same Looked up value setup to copy values from Contacts if you would rather copy the data instead of link to it. (Copied data won't change when you update info in contacts so decide on whether or not you want the data to update automatically or not.)
              • 4. Re: Help with relationships on call log


                This is great. Thank you so much for yoru generosity and the time you've put into this response. I think I can follow along despite my noobness but I do have a couple of questions, and will probably have more as I go through and re-create this database: 

                In #1, when I define auto-entered serial number, should I tell it to generate on creation or on commit. (I'm guessing on commit?)

                In #2, "Use field options to specify global storage"  -  do you mean that I should click on the "Use global storage (one value for all records) check box under the Storage option for the gCallerLookUp field options? Are there any other settings there I should change?

                Honestly, I'll probably have more questions but this is so helpful! I'll post my progress and report back if (most likely, when) I hit any snags. PhilModJunk, you rock. 

                • 5. Re: Help with relationships on call log

                  #1 on creation. In all my years of Filemaking, I've never ever found On commit to be the better option. In your case, however, it won't really make much difference which you use.

                  #2  yes, that's it and no, no other options. This setting isn't absolutely necessary, but sets up your database to make it work better should you ever share it over the network.

                  • 6. Re: Help with relationships on call log

                    Hi PhilModJunk,

                    Ok, so I started with a new file/database without any pre-existing contacts or calls in the log. (I figured it'd be easier to use tests for now and then once everything is working to add in the contacts and update the call log)

                     Also, thank you for clarifying "on commit" - you're right, it doesn't really make a difference so I went with commit. And yes, definitely sharing over a network so I selected it. 


                    I created the two tables with the fields you can see in the screenshot. I made the Contacts::CallerID a number with auto-enter serial and then added a CallerID field to the Call Log table as a number. Then, I went into relationships and dragged a line from Contacts::CallerID to Call Log:: CallerID. Is that all I need to do in order to keep them related? Do I need to designate in their relationship "allow creation of records in this table via this relationship?" I'm guessing that under Call Log I should select that for Contacts to be ableto write into the Call Log table? Did I create that relationship correctly? (dragging from the Contacts::CallerID to the Call Log::CallerID? 



                    I created the gCallerLookUp field and specified Use global storage. Do I need to increase the maximum number of repetitions? I ask because if I am maintaining a call log in which the same person may call several times a week and have several entries on the Call Log will that affect anything?


                    I created the reference duplicate of the Contacts table and then created the Call Log::gCallerLookUp = Contacts::CALLER but I'm not sure hwo to make sure that CALLER has unique values specified as a validation rule. 

                    • 7. Re: Help with relationships on call log

                      4. When I attempted to use the looked up values settings for Call Log::CallerID to copy CallerID from ContactLookup, I don't have that option (see screenshot)

                      • 8. Re: Help with relationships on call log

                        it doesn't really make a difference so I went with commit.

                        Hmm, you did notice that I recommended "on creation"? It makes no difference now, but could cause issues in the future as you continue to add features to your database.

                        1) this isn't set up right.

                        you need one relationship line from Phone Sheet to contacts and a separate line to ContactLookup.

                        line 1:

                        PhoneSheet::callerID = Contacts::CallerID

                        Create it by dragging from CallerID in Phone Sheet to callerID in Contacts.

                        Line 2:

                        PhoneSheet::gCallerLookUp = ContactLookUp::CALLER

                        Create it by dragging from gCallerLookUp in PhoneSheet to CALLER in ContactLookup--that's where you went wrong here. And this is why you can't set up the looked up value setting.

                        • 9. Re: Help with relationships on call log


                          Thank you - that definitely worked now that I have the relationships setup properly. Now I'm stuck at the last step (#6) you mentioned above. You mentioned that I should add any fields I want from Contacts (not ContactLookup) to the Call Log layout. I already have the fields I wish to sync in that layout (CALLER, NUMBER). Do I need to delete those fields and somehow re-add form contacts? 


                          Also, I added the dropdown to the Call Log::gCallerLookUp - does this mean that when I get a call and want to input the contact info that I can start typing and select from the drop down menu that is supposed to appear? Do i need contacts in the contact table before this will work? This is what I have so far. I'm sure I'm missing a step somewhere...



                          • 10. Re: Help with relationships on call log

                            Do I need to delete those fields and somehow re-add form contacts?

                            It sounds like you have them defined in the Phone Sheet table. Remove them if this is the case--unless you want to copy the data via a looked up value setting and I don't recommend that unless you have a specific reason to keep a copy of this data in Phone Sheet.

                            Then, Enter layout mode and use the field tool to add fields to the layout. When the specify fields dialog opens, select Contacts from the drop down list and click the name of the field you want to add.

                            does this mean that when I get a call and want to input the contact info that I can start typing and select from the drop down menu that is supposed to appear?

                            Make sure that you have selected the auto-complete option for this field. That's what will take a list of all your contact names and filter them down to smaller and smaller lists of names as you type more and more text.

                            Do i need contacts in the contact table before this will work?

                            Yes, what you have at this point lists contacts from the contacts table. You'll need to add records to the contacts table before they will appear in the drop down list of contact names. Once you have that working, there are ways to set up a script that offers to create a new contact in the contacts table if the name entered does not appar in the value list, but first things first.

                            • 11. Re: Help with relationships on call log

                              Ok, I removed the CALLER and NUMBER fields from the Call Log table and inserted fields via the Layout viewer with the results in the attached photo.


                              I have a test contact in the Contact table so I can try to make this work. I managed to get the contacts::caller and contacts::number (in the call log table) to auto-fill but only when I enter in the corresponding callerID, not when I begin to type in the name under Contacts::CALLER. Obviously, I don't want to have to memorize hundreds of CallerIDs for different clients but I feel like I'm much closer than I was before but how do I get the number to auto-fill once I start typing in the Contacts::Caller field? Now when I attempt to place my cursor in that field I get a pop warning that says, "Before typing, press Tab or click in a field, or choose the New Record menu command." 


                              Also, I'm not sure what the gCallerLookup is functioning as.


                              And re: script to setup new contact - I definitely want to get that incorporated in there but you're absolutely right. first things first. 

                              • 12. Re: Help with relationships on call log

                                Are you seeing a value appear in the CallerID field?

                                What is supposed to happen is that when you select or enter a name in gCallerLookUp, a value is automatically looked up and entered into CallerID. This then establishes the needed link to Contacts so that the Caller and Number fields from contacts can display data from that contacts record.

                                It sounds like nothing is being looked up in the CallerID field.

                                • 13. Re: Help with relationships on call log

                                  Ok, I think I may not have setup gCallerLookUp properly. When I begin typing in that field, nothing happens except that my text appears, no dropdown menu (that's on the Contacts::Caller field). Nothing is generated in CallerID. However, when I manually type in a number to the CallerID the correct corresponding contact DOES fill correctly into the rest of the call log (which is exactly what I want). 

                                  The below process is what I'm going for, right?

                                  When I receive an incoming call, do I start typing into gCallerLookUp, then a short list gets shorter as my typing more correctly mirrors any of the Contact::Caller entries, and when I select that person, it should auto fill the callerID whihc then auto-fills the Contacts::CALLER and Contacts::NUMBER fields on the CALL LOG TABLE. 

                                  Side note: I'd like to be able to automatically insert the current date and time in this process but can tackle that once the rest of this is working. :)

                                  I know I keep saying this, but seriously this is the best help I've received on any technical issue I've ever posted in any forum. Most sincere thank you, philmodjunk. You are such a patient and immense wiz at this (this being FMP and helping noobs). If this forum had some reddit gold or something, I'd get it for you. 

                                  • 14. Re: Help with relationships on call log

                                    You are correct in your analysis of the issue. I had not anticipated using a table view--I usually use Layouts set up with list or form view. Here's what you need to do.

                                    Enter layout mode. Your layout will not look anything like your table view, but that's OK.

                                    If gCallerLookUp is not on your layout when you are in layout mode, use the field tool to add it.

                                    Select the field by clicking it and use the options on the Inspector's data tab to set it up as a drop down list (change it from "edit box").

                                    There's a pencil icon next to the "values from:" check box that you can click to open manage | value lists where you can create a value list of names from the contacts table. Select the "use values from a field" option. Select Contacts in the first drop down and click Caller to select it as the field from which you will list caller names.

                                    Click OK in each dialog box until you are back to the layout.

                                    Click the auto-complete check box in the inspector.

                                    Now return to browse mode, saving your layout changes and see if you get the needed drop down list that autocompletes as you type. (To see the full functionality of this drop down, you'll need to have several similarly named Contacts.)

                                    1 2 3 Previous Next