8 Replies Latest reply on Aug 14, 2012 8:21 PM by philmodjunk

    Combine several dates into one column?...

    tman1425

      Title

      Combine several dates into one column?...

      Post

      I'm having trouble figuring out if this is even possible and I've received a lot of good advice here in the past...so here it goes...

      I have several different fields for each client that contain a specified date in the future, such as:  PolicyExpire1 PolicyExpire2 PolicyExpire3.  I would like to combine these dates to show up in a single table column in order to sort the dates by those occuring first.  I've done this for a birthday list, but it is more difficult since I have multiple fields this time.  As an example, if one client has two policies expiring, I would like for the client to show up twice (once for each date listed in the column).

      Is this possible?  Right now, I have the three fields just set up in three different columns and I sort each field separately to see which dates are coming up first.  It would be easier if they were all combined into a single column.

      Thanks in advance for any input!

        • 1. Re: Combine several dates into one column?...
          philmodjunk

          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-----<PoliciesPurchased

          Clients::__pkClientID = PoliciesPurchased::_fkClientID

          • 2. Re: Combine several dates into one column?...
            tman1425

            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.

            • 3. Re: Combine several dates into one column?...
              philmodjunk

              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.

              • 4. Re: Combine several dates into one column?...
                tman1425

                Phil,

                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!

                • 5. Re: Combine several dates into one column?...
                  philmodjunk

                  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:

                  Clients----<Client_Policy>-----Policies

                  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.

                  • 6. Re: Combine several dates into one column?...
                    tman1425

                    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.

                    • 7. Re: Combine several dates into one column?...
                      tman1425

                      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!

                      • 8. Re: Combine several dates into one column?...
                        philmodjunk

                        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:

                        Option 1:

                        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.

                        Option 2:

                        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.