7 Replies Latest reply on Aug 15, 2014 11:36 AM by philmodjunk

    Need help setting up relationships in starter solution and tables

    DanielKnott

      Title

      Need help setting up relationships in starter solution and tables

      Post

           Hi everyone, I've been working on a setup for my company to aide in interoffice communication and task management via Filemaker, though I'm a novice on the program so I apologize ahead of time. Anyway, I've gotten the hang of using the Tasks starter solution and then customizing it from there, but I've begun pulling in data from our Excel workbooks into tables on Filemaker and I've hit a bit of a snag at this point and would love some assistance. The short version of it is that I've set up pop-up menus, on the task manager interface, for Company and Contact, and ultimately what I need to be able to do is to only pull up the contacts from the selected company (company and contact information is in an Excel database, that I have yet to import into a table on my new FM project). If I need to make two tables and import the data twice I can, I'm just at a mental loop figuring out what I need to do to be able to accomplish that. If there is more information required please let me know and thank you very much. 

           I've included an image with a mock company set up to illustrate what I'm trying to do. Thank you. 

      Capture-1.PNG

        • 1. Re: Need help setting up relationships in starter solution and tables
          philmodjunk

               You will need a table of contacts and a table of companies in your solution. If this is imported from a single spreadsheet file, it is very likely that you have duplicates of your company information.

               So how is your spreadsheet set up for the data that you wish to import into your starter solution?

               Do you have tables in it for companies and contacts or will you need to add them?

               And here's a key wrinkle that often comes up with companies and related contacts:

               Presumably, a company can have more than one contact, but does any given contact link to one and only one company? (In some situations, a contact needs to be associated with more than one company--such as a broker that represents multiple vendors...)

          • 2. Re: Need help setting up relationships in starter solution and tables
            DanielKnott

                 To answer your last question first, there is only one contact type where there will be repeat, and that is from the financial advisor. Sadly, that means we have wrinkle that I'm not sure how exactly to overcome. 

                 As for the spreadsheet, once I get the finalized client information list, it will indeed have the company and contact info. I currently have added another layout in my starter solution but I think I may be looking at that the wrong way; should I instead create a new table within the Manage - Database submenu? If so, I've begun created tables but am currently unsure how to import my data from Excel into that table (not sure if it's taken care of in the same menu but I'm currently not seeing the ability within. 

                 Assuming we can figure out the aforementioned two questions, from there would it would be possible to write an "if" script or create a relationship that would limit the "contact" selection based on their "company"? 

                 Thank you again for your help and I cannot apologize enough for my inexperience, database software is not at all my area of expertise but I was just thrown the project by the company head. 

            • 3. Re: Need help setting up relationships in starter solution and tables
              philmodjunk

                   You would indeed use Manage | Database | Tables to create a new table for contacts and one for companies. You will find that this automatically creates a new layout for each new table and a new table occurrence in Manage | Database | Relationships--all with matching names.

                   To use Import Records | File from the File menu, first select a layout based on the table into which you will import your records. Then select this option from the File menu.

                   So you will select the companies layout, then select Import Records to import data into the companies table. Then you select the Contacts layout and do another import.

                   And you can import from different columns of the same file to import data into each of these tables.

                   How to filter out duplicates during import:

                   Identify the field in your table that must contain only unique values. In some cases this will not be a single field, but you need the combined values of several fields to be unique such as combining first and last names or first, last names and a phone number. In those cases, add a text field to your table and define an auto enter calculation that combines the data of the multiple fields such as FirstName & " " & LastName & " " & Phone. You now have a single field that must contain unique values. On this single field, select the "unique values" and "validate always" field options.  When you import records in to this table the duplicate values should be filtered out during import.

                   PS when doing mass data changes to your file--such as importing, deleting or updating records, it's a very good idea to save a back up copy of your file first so that you can start over is something does not turn out as expected.

              • 4. Re: Need help setting up relationships in starter solution and tables
                DanielKnott

                     PhilModJunk, as usual you have been fantastic. I am away from the office for a couple days but will get back to the grind and start implementing your advice, then I'll let you know how it goes. I've already been creating three copies which I rotate out after learning the hard when I destroyed my first solution. 

                • 5. Re: Need help setting up relationships in starter solution and tables
                  philmodjunk

                       I see that I left out a critical detail. If you have to add a field with an auto-enter calculation in order to filter out duplicates, you must also click the check box to enable auto-enter options during import. This check box appears in a funky little window that pops up after you click the button named import.

                       And speaking of back ups, you may find this script to be of interest: Saving Sequential Back Ups During Development

                  • 6. Re: Need help setting up relationships in starter solution and tables
                    DanielKnott

                         As usual, you've been amazing; it works perfectly. I didn't use the auto-cent calculation yet, as I wasn't sure where that comes into play; as I mentioned  earlier, we have financial advisors which deal with multiple companies that we take care of, so by default there will be duplicates. Will that matter? Also, now that I'm starting to actually make sense of what I'm doing with my layouts, tables, etc. I was wondering about when I actually need to make a relationship/table occurrence. For example, I imported the company and contact info into separate tables, but on my Tasks layout instead of drawing relationships through the relationship manager, I used the inspector and changed where the values come from (company pulls from the company table and contacts pull from contacts table). Does that alter any behavior of the system? 

                         Also, now that I'm getting the hang of things, how do I make a relationship or limiter so that when I choose Company A, it will only show participants in Company A in the drop down menu? 

                          

                          

                    • 7. Re: Need help setting up relationships in starter solution and tables
                      philmodjunk
                           

                                but on my Tasks layout instead of drawing relationships through the relationship manager, I used the inspector and changed where the values come from (company pulls from the company table and contacts pull from contacts table). Does that alter any behavior of the system?

                           You can't do it all through the inspector. This type of change only works if you already have the correct relationships in place to support it. If you specify that a field draw data from table A, on a layout that specifies Table B in Layout Setup | Show records from, there has to be a relationship linking those two table occurrences or you'll see <unrelated table> in the field instead of the data you'd expect to see. I don't recall if I've posted this link yet, but to learn more about table occurrences, see: Tutorial: What are Table Occurrences? This is a critical concept to master in order to be able to get the most out of a FileMaker database.

                           

                                how do I make a relationship or limiter so that when I choose Company A, it will only show participants in Company A in the drop down menu?

                           This is called a conditional value list. Brace yourself, I have a lot of links on the subject:

                           There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                           The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                           Forum Tutorial: Custom Value List?

                           Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                           Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                           Hierarchical Conditional Value lists: Conditional Value List Question

                           Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                           Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                           And stay tuned! I have a single FileMaker file in development (Filemaker 12 or newer only though) that covers all the concepts from the above links plus some new ones all in a single working FileMaker file with voluminous notes, pictures and explanations. It's not quite finished but since it's is both a resource document on different types of conditional value lists and also a working database file that demonstrates each technique, you can both read about the concepts and also modify data and design details in the file to see how those changes affect the function of the file.

                           I'll post a download link here in the forum when I'm ready to share it.