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.
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.
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:
- Unique across all the records in the table and all future records that might be created.
- 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...)
- 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.
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!
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.
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.
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.