7 Replies Latest reply on Aug 20, 2014 9:21 AM by philmodjunk

    Relationship Management



      Relationship Management


           So I'll start by saying, I have a general lack of understanding of how relationships work.  I get the principal, I don't get how to use it for my purposes.

           Each one of my contacts (a table and layout of its own) owns one or more assets (a separate table.)  The asset table is made up of several fields, probably 5 of which are critical and I would like to show on the contacts layout, basically showing ownership of the asset.  I'm thinking maybe a portal is the way to do this.  The only purely unique way to identify the asset is by the manufacturers serial number... all other fields could also be the same as other assets.  I'd also like to be able to show ownership of the asset on the assets page... but that would just be a bonus.  

           Which field in the contacts table do I use to match with the asset that it owns? I'm sure this is a pretty basic question that would be best solved by reading a help topic BUT, I have done that and I'm still at a loss.  Thanks for the help and patience!

        • 1. Re: Relationship Management

               The concept is fairly straight forward once you get you head around it.

               A relationship such as the one your looking at would use a one to many (one client - many assets)

               The best way to think of this, each client must have a unique id (primary key), it can be an account number or any unique identifier you choose.

               You will use this unique ID to link the client to the asset. So you should create a (primary key) pk_ClientID field in your client table if you don't already have one

               So now in the Asset table you will need a field for the owner of the asset which will hold the unique ID of the owner, this is generally referred to as the foreign key, so create a fk_ClientID field in your assets table.

               In the relationship graph if you link the Client tables pk_ClientID to the Assets tables fk_ClientID and check the enable creation of records in the assets table via this relationship checkbox, you have your one to many relationship.

               Now on your client layout you can create the portal to the assets table. Add the necessary fields to the portal. Every time you enter a new asset in the portal filemaker will automatically enter the ClientID into the fk_ClientID and maintain the link between the client and the asset.

               Similarly, on the Assets layout you have your fk_ClientID to show you who owns the asset but you can now also place a merged text field to display the client name from the clients table to have a more meaningful description or even more detailed info regarding the owner with other merged fields from the client table.

               Hope that helps.

          • 2. Re: Relationship Management

                 That did it!  So far it seems to be working perfectly.  Thanks so much!

                 Bonus Question: Can I use the pk_ClientID to create a new record on a "Companies" table?  So, just like in the real world, the Client owns the Company which owns the asset.  It's not all that necessary but the increase functionality could come in handy.  By the way, as I currently have it setup, the value of the pk_ClientID is the name of the Company that owns the asset.

            • 3. Re: Relationship Management

                   Names are not a good choice for primary keys. Even companies change their names. One company that I used to work for has gone through 4 or 5 name changes in its history. Even if your companies didn't change names, should you enter a name incorrectly, create some related records and then discover the error, correcting the error disconnects the related records. And company names are not always unique either.

                   Thus the ideal primary key should be:

              1.           Unique across all the records in the table and all future records that might be created.
              3.           Free of any "additional meaning" beyond that of uniquely identifying each record. (If you include any extra "meaning" you open the door to having to change the value in the future...)
              5.           Implemented in as simple and "bullet proof" a manner as possible.

                   In FileMaker, that makes for either an auto-entered serial number or an auto-entered Get ( UUID ) text string as your best bet for a primary key.

              • 4. Re: Relationship Management

                     Okay... good point.  It's likely that a typographical error would cause a lot of issues using the Company Name.  So if doing as you propose, would the pk_ClientID generate the serial number ID (something like "CAC00001") and then we would enter that serial number in the fk_ClientID on the Assets table?  Or would it auto-magically link somehow?

                     Also, I have a post out there about creating a to-do list if anyone can get to it.  thanks!

                • 5. Re: Relationship Management

                       That serial number needs to be as simple as possible. The value literally need to be 1, 2 ,3... no leading zeroes, no added text.

                       The basic set up will depend on the real data you have to work with.

                       If there can be many companies linked to one client but a company cannot be linked to more than one client, you use a ClientID value generated in the client table.

                       If there can be many clients linked to one company, but a client cannot be linked to more than one company, you use a CompanyID value generated in the company table.

                       And if you might have many companies linked to one client and also it is possible to have many clients linked to one company, you add a third table, called a "join" table in between clients and companies and use ID's generated in both clients and companies to link to records in the join table.

                       Let me know which scenario matches your actual situation and I can describe how to set  that up in more detail.

                  • 6. Re: Relationship Management

                         Well, what I'm trying to accomplish is a very, very simple CRM software solution.  If I stay very, very basic, I just need to track the person (first name, last name) and what asset they own.  It just so happens that the way they own said asset is through a company which they either own or are a partial owner of.  So the Companies table, which does not yet exist would add a layer of complexity I'm not sure I want... for the simple reason that it's just more data to keep up with and have to make sure isn't GIGO.

                         But, to answer your question: I think "many companies linked to one client and also possible to have many clients (4 max) link to one company."  In my setup that would be more than one "Contact" linked to more than one "Company" which can also own / link more than one "Asset."  If you'd like to decode that puzzle, it would be great but the way it's been proposed and I currently have it is all I need for now.  Once implemented across other users though, we'll probably want to get more complex and I'll be able to refer back to this post.

                         Now, second question: I have several fields in the Contacts table (Task, Description, Due Date).  On another layout I would like to get these three fields to appear (I assume in a portal) IF a certain condition is met.  That condition would be that the Due Date falls within the next seven business days.  (It would be great if the amount of business days were a selectable option too... 5, 7, 30, 180, etc).  I'm thinking the portal from the Dashboard layout to the Contacts layout would need to link via the Due Date field?  Here too, I think its the concept behind the relationships that are getting to me.

                         Thoughts when able!  Greatly appreciated.  I'm in a trial version and as of this morning, committing to buy because of the functionality.

                    • 7. Re: Relationship Management

                           Then you'll need to decide if you really need a companies table or not. If you don't need the company table and can just link clients to Assets, your database design becomes simpler. What data about each company do you need to record. If you don't need to record more data than the name of the company, you probably don't need a table of companies linked in a many to many relationship with clients.

                           If you decide that you need a companies table....

                           Start with these relationships:


                           Companies::__pkCompanyID = Company_Client::_fkCompanyID
                           Clients::__pkClientID = Company_Client::_fkClientID

                           You can place a portal to Company_Client on the Companies layout to list and select  Clients records for each given Companies record. Fields from Clients can be included in the Portal to show additional info about each selected Clients record and the _fkClientID field can be set up with a value list for selecting Clients records by their ID field.


                                I have several fields in the Contacts table (Task, Description, Due Date).

                           I have addressed this issue in your other thread. These fields should be in their own table of Tasks, not in the contacts table.