Fields named: "PolicyExpire1 PolicyExpire2 PolicyExpire3" Strongly suggest that you need a related table listing each policy purchased by a different client. A list layout based on such a table can list a client once for each policy owned and this will give you one expiry date field for all policies purchased instead of multiple fields.
Clients::__pkClientID = PoliciesPurchased::_fkClientID
I had a feeling you were going to say something along those lines. The only problem is that I have no clue to how to set up multiple tables and relationships. The relationship aspect goes way over my head....time to do some research...
Thanks for the quick response.
Please doen't hesitate to ask questions if you try to set up a relationship after researching the concept and encounter an issue.
As a hint, look up "portal" in Filemaker help and any other reference materials on FileMaker at your disposal.
I'm picking up a little bit more about how tables work, but most is still going over my head. Would you suggest I create a new table for each life insurance policy (i.e. PolicyTable1, PolicyTable2, PolicyTable3) and link each table back to the client? Or is it better to combine all three policies into one table?
Thanks for your help!
Create one table where you have one record for each policy. This can be your list of Policies that a customer can "buy".
Create a second table linked to both the client table and the Policies table where a given record documents the "purchase" of a given policy by a given client.
In Manage | Database | Relationships it might look like this:
The match fields used might be:
Clients::__pkClientID = Client_Policy::_fkClientID
Policies::__pkPolicyID = Client_Policy::_fkPolicyID
The fields starting with __pk are primary key fields usually defined as auto-entered serial number fields. The _fk fields are "foreign key" fields usually defined as simple number fields with no auto-enter features.
A portal to Client_Policy can be set up on a Clients layout where you'd select the policies that belong to that client. A portal to Client_Policy on the Polices layout, on the other hand, can list all clients that own that policy.
Thanks for the quick response. It becomes very difficult to do it that way because there are literally hundreds of different type of policies that our client have. And each policy is structured differently to fit the client.
Could you give me a nudge in the right direction?
So far, I have created a separate table for Policy1, Policy2, and Policy3. I linked the fk_Client Number in each of the three policy tables back to the pk_Client Number located in the main Clients table. The idea was to link each policy back to the client. Is this right so far?
Now I'm trying to create a portal which lists all of the policy numbers for that client. As an example, it will be a quick reference and list the Policy# in each Policy table. Is this possible?
I'm not sure how to describe it, but hopefully that made sense. Thanks!
I do not recommend separate tables for each policy. They will be much more difficult to work with. For example, a portal lists all records from a single table, you won't be able to use a single portal to list multiple policies if they are stored in different tables.
There are many ways to handle this with a unified table. Every policy will have a certain list of items that is common to all policies. Setting up a record to record these common items of data is a start. And now you have your single table. It's how you structure you database to handle the differences between each policy is where it gets interesting and there is no one right answer.
there are literally hundreds of different type of policies that our client have.
And many companies have 1000's. "Hundreds of different types" is well within the ability for this structure to handle.
And each policy is structured differently to fit the client.
We seem to have two different entities here: a Policy "type" and a Policy. Your policy types should be fairly easy to list in a table.
Does this mean that every single policy is different and that there is only one client for any given policy?
Can you post an example of how a policy might be "structured differently to fit the client"?
Setting up tables and relationships to handle this situation is generally handled with one of two basic approaches:
Set up a single table with lots of fields. Any given record in the table uses only a sub set of all the fields, leaving the rest blank. This makes for very simple relationships, but a very complex table.
Set up a single table with an ID number to uniquely identify each and every record in the table. Add only those fields common to all or nearly all records in the table. Then define additional tables that link to the central table using the ID Number field. Each table functions to document the details for one category of records.
This simplifies your table definition, but leads to a more complex relationship graph.
In both options, you can define layouts that are specialized to work with a specific sub group of records such as a specific policy type. In fact, to the user, the end result can be exactly the same.