4 Replies Latest reply on Aug 25, 2010 10:54 AM by ValerieFusco

    Database design modification

    ValerieFusco

      Title

      Database design modification

      Post

      I am trying to add some functionality to an existing database. The company I am working with is adding on to their capabilities, and will be providing additional services both to their own customers as well as to customers from other companies. I am relatively new to database design, although I have worked with Filemaker for several years. Here are the issues:

      For new services for the company's customers, I want to be able to use some data (name, address, etc.) from an existing Filemaker table. But, I also need a way to enter that data for clients from other companies. I thought I would build a table with all of the required fields for the new services (Table B), and then have a script in the existing table (Table A) to create a new record and copy the appropriate field contents into Table B. I don't know how to define the key(s) in Table B or the relationship between the tables since Table B will contain some records that have connections to Table A and others that are unrelated.

      I would appreciate any guidance that you can give me. Thanks so much!

        • 1. Re: Database design modification
          philmodjunk

          Instead of copying data, you may want to link to it instead. This is much simpler to set up and then edits in the source table are automatically shown in your second table.

          If you truly do need to copy the data, there are auto-enter calculations and looked up value field options that do not require a script to copy the data from one table into another.

          Both approaches start with the same requirement, a relational link between the two tables in Manage | Database | Relationships.

          Once you have a relationship, linked data can be shown simply by adding fields to your layout from the related table. To copy the data, you'd use field options in Manage | Database | Fields for each such field to copy the data over.

          • 2. Re: Database design modification
            ValerieFusco

            Thanks for your input! I understand what you're saying about linking to data vs. copying it. But, how is this accomplished within this mixed situation. For example, in Table B the Name may come from Table A, or it may be a new name that is input directly. If the field is called Name, how can I link to the Name field in Table A for some records and input info into the field for other records?

            I've set up a script that runs from Table A, writes values to variables, and then sets the field values in Table B from those variables. It works, but I'm not sure it's the smoothest solution.

            Again, I appreciate your willingness to share your expertise!

            • 3. Re: Database design modification
              philmodjunk

              "If the field is called Name, how can I link to the Name field in Table A for some records and input info into the field for other records?"

              You wouldn't, you'd create a new record in Table A for your new information. This has the advantage of keeping all your data in one table. Depending on your database design, this could be as simple as entering the linked field and typing in new information, though more typically, you'd have a button for adding a new contact.

              Thanks for your input! I understand what you're saying about linking to data vs. copying it. But, how is this accomplished within this mixed situation. For example, in Table B the Name may come from Table A, or it may be a new name that is input directly. If the field is called Name, how can I link to the Name field in Table A for some records and input info into the field for other records?

              "I've set up a script that runs from Table A, writes values to variables, and then sets the field values in Table B from those variables. It works, but I'm not sure it's the smoothest solution."

              If you decide to copy the data, you can use auto-entry field options to copy the data (either a calculation or a looked up value setting) and no script is needed.

              • 4. Re: Database design modification
                ValerieFusco

                I think I need to work with two tables. The existing table tracks data for funerals, and the new table will track cremations. Some of the funerals will be cremations, and some of the cremations will be for funerals handled by other funeral homes. The cremation business is a separate entity, and needs to be tracked separately.

                So, some info from Table A will need to be moved into Table B, and other info will be directly input into Table B. Unfortunately, the existing system wasn't built with a separate "customers" database - the customer info is embedded into the funeral info. I don't want to rewrite the existing system, but am trying to find a way to capture information from it.

                Scenario I would be that a funeral record is created; if there will be a cremation, a cremation record must be created using data from the funeral record in addition to cremation details that will be added manually. Scenario II is that an outside funeral home opts to use the cremation service - in that case the entire cremation record would be entered manually.

                I will look into "auto-entry field options" - I am not familiar with that functionality.

                Thanks for your time and patience!