5 Replies Latest reply on Feb 15, 2013 10:52 AM by philmodjunk

    Common TO-DO list covering multiple tables



      Common TO-DO list covering multiple tables


           I have a data base with several Employee Records, related to these employee records are multiple tables/records tracking their vacation/sick time taken, Vacation and Sick Time Entitlements, Years of service, Salary Increases etc etc.

           What I would like to create is a TO-DO list that I can refer to everyday to see if there are upcoming changes and mark them as done.  That part of course is easy, what I'm looking for help with is finding the easiest way to tie all these tables and records together in one central area to avoid complicated scripts running and creating all sorts of records every time a child record is created from the Parent table (Employee Records).

           As I'm typing this I realized that the easiest route might be to try and house all these records under one table which in this case might be possible because they all more or less revolve around a type, start date, end date, explanation with a few other variances here and there but that could be easily dealt with constant calculations creating relationships with each kind of transaction.  I can then create a TO-DO table that calls upon all the records and create dated relationships that have them show up in the time frames I need them to show up in.

           But now that I'm here and about to post this anyways, I'm wondering if there would be any suggestions if housing them under one table isn't really an option without having an insanely long list of fields to cover each potential type of record that could be housed under this same table.... or does it always have to be unless we want to get complex with script triggers?

           Is it possible to have a record created in Table2 based on a relationship with Table1 and simultaneously having a second record created in Table3 based on a relationship with Table2 without using triggers or scripts of any kind?


           Hope I've made myself somewhat clear, didn't sleep a wink last night so I can't promise anything.

        • 1. Re: Common TO-DO list covering multiple tables

               You have encountered one of the classic data modeling issues that can come up in Relational Database Design.

               Put all the data in one table or a series of separate tables?

               And there is no clear cut definite answer. Not only are there trade offs with either option, but the individual preferences of the developer can play a role in this as well.

               The biggest factor is to guage the level of similarity for each set of data that you might put in a separate table. The fewer fields you need just for one type of record, the stronger the argument to put all the data in a unified table.To add to that, many reporting tasks in FileMaker are much easier when the layout is based on a single table and yet can display all data without using any portals

               There are two approaches you can keep in mind as you look at both the data you have and what you need to do with it:

               1) There's no law that says you can't have a table with a lot of fields that are used with just a few types of records. This will produce a larger file, but when hard drive capacity is now measured in hundreds of gigabytes, a larger file size isn't as big a deal as it once was. You can create any number of layouts dedicated to one type of data and put just the fields needed for that type on that layout.

               2) There's a compromise approach that you can use: Set up a "backbone" table that defines just the fields common to all record types. Add separate related tables that link in one to one relationships to the backbone table for the fields unique to one subset or another of your data. A record of Type A would link to a record in the Type A details table. A record of type B would not link to a record in that table, but would link to a record in the Type B table.

          • 2. Re: Common TO-DO list covering multiple tables

                 Right, hmm tough one.

                 Are you aware of a setup that can simultaniously create a child and grand child without scripts?  Example:  Employee records creates a vacation entitlement record and when the vacation entitlement record is created that record automatically creates a to-do record for itself.

            • 3. Re: Common TO-DO list covering multiple tables

                   Why would you want to do that?

                   If you had these related tables:

                   Table A----Table B----<Table C

                   and "allow creation..." is enabled for B in the A-B relationship and for C in the B-C relationship....

                   Entering data in a field from Table C (could be in a portal to table C) on a Table A layout could create records in both Table B and Table C if the related records do not yet exist.

                   But that isn't always the best approach for managing your data.

              • 4. Re: Common TO-DO list covering multiple tables

                     Yeah I know it isn't the best approach .... I suppose I will take the backbone route.  The thought of overhauling all my tables and calculations to have them housed under one roof seems too daunting right now and I don't think I will get much performance difference between the two, maybe even slower performance???   My only compromise was that I was hoping to have every to-do task appear in one single list instead of having to browse different records for different types but it's a small trade off.  I think I will have a summary field counting all the relavant records and have them appear in every page so that you can at a glance know where you have to go to find the to-do tasks.


                     Thank you Phil!

                • 5. Re: Common TO-DO list covering multiple tables

                       My only compromise was that I was hoping to have every to-do task appear in one single list instead of having to browse different records for different types

                       That would be one of the key reasons for adding this table. You might consider just adding this new table to your existing one and only giving it foreign key fields to link to your existing records. A script could go through all your existing records in each table and create a matching, linked "backbone" record.

                       That would be step one. Step two would then be to gradually identify fields that exist in all the separate tables and recreate them in the "backbone" table a bit at a time.

                       FileMaker Advanced's Database Design report would be very helpful in working with such changes to say nothing of some of the third party utilities that can work from the design report.