4 Replies Latest reply on Oct 8, 2012 7:52 PM by ChrisSmythe

    Planning my FileMaker Pro 12 Database

    ChrisSmythe

      Title

      Planning my FileMaker Pro 12 Database

      Post

           Hello, I am a newbie to FileMake Pro 12 running on Windows 7 (64-bit).
           I have read the section About Planning a Database: http://www.filemaker.com/11help/html/create_db.8.2.html#1027557
           However, I would greatly appreciated if someone could be so kind as help me in the planning of my FileMaker database (I have not a clue).

           The purpose of this database is to import from a Microsoft Excel spreadsheet most of the personnel details data, as well as, the emergency contact details data into our FileMaker database.
           This will enable us to lookup (search) a member in our organisation and list all their emergency contacts.
           It will also enable us to to list our members that have a deployment status of: Pending (defined as a date that is less than today's date) for each of the five Services (Army, Navy, Air Force, Civilian and Contractor) so that we can export their emergency contact details to a Microsoft Excel spreadsheet for a mail-merge with Microsoft Word.
           Additionally, when we receive contact from a person's emergency contact, we can add any Special Considerations or Temporary Change of Address.

           Please refer to my proposed Personnel Details table and Emergency Contact Details 1 table.  Each shows the proposed fields for my FileMaker database.

           For each person listed in the Personnel Details table, there are usually corresponding emergency contacts.  There could be none, or up to six emergency contacts for each person.
      For example: Mr Jon Blackmore (person listed in Personnel Details table) has his Mother, Father, Wife, Brother, Sister, and Friend as emergency contacts (a total of six emergency contacts).

           I envisaged that each person listed in the Personnel Details table will have each of their emergency contacts listed in a separate form.

           The only unique identifier that I can think of is a person's employee ID number.

           I was thinking that maybe I could create the following tables and some how establish a relationship between them? (I am open to suggestions).

           Personnel Details table;
           Emergency Contacts Details 1 table;
           Emergency Contacts Details 2 table;
           Emergency Contacts Details 3 table;
           Emergency Contacts Details 4 table;
           Emergency Contacts Details 5 table;
           Emergency Contacts Details 6 table;
           Operation/Exercise table; and
           Deployment Location table.

           My Personnel Details table has the following proposed fields:

           AutoID --- (Auto-enter Serial) --- (user cannot edit this field)
           Deployment Status --- (either Not Deployed, Pending or Deployed) --- (user cannot edit this field)
           Employee ID --- (text) --- (user cannot edit this field)
           Title --- (text) --- (user cannot edit this field)
           Last Name --- (text) --- (user cannot edit this field)
           Given Name(s) --- (text) --- (user cannot edit this field)
           Service --- (Army, Navy, Air Force, Civilian or Contractor) --- (user cannot edit this field)
           DVA Consent --- (yes or no) --- (user can edit this field)
           Operation/Exercise --- (text) --- (user can edit this field)
           Deployment Location --- Text) --- (user can edit this field)
           Deployment Date --- (date) --- (user can edit this field)
           RTA Date --- (date) --- (user can edit this field)
           This form created by --- (text) --- (user cannot edit this field)
           Created on --- (date) --- (user cannot edit this field)
           Last updated by --- (text) --- (user cannot edit this field)
           Last modified on --- (date) --- (user cannot edit this field)

           My Emergency Contact Details 1 table has the following proposed fields:

           AutoID --- (Auto-enter Serial) --- (user cannot edit this field)
           Employee ID --- (text) --- (user cannot edit this field)
           Primary Emergency Contact --- (text) --- (user cannot edit this field)
           Next of Kin --- (text) --- (user cannot edit this field)
           Title --- (text) --- (user cannot edit this field)
           Given Name(s) --- (text) --- (user cannot edit this field)
           Last Name --- (text) --- (user cannot edit this field)
           Relationship --- (text) --- (user cannot edit this field)
           Address 1 --- (text) --- (user cannot edit this field)
           Address 2 --- (text) --- (user cannot edit this field)
           Address 3 --- (text) --- (user cannot edit this field)
           Address 4 --- (text) --- (user cannot edit this field)
           Surburb --- (text) --- (user cannot edit this field)
           State --- (text) --- (user cannot edit this field)
           Postcode --- (text) --- (user cannot edit this field)
           Country --- (text) --- (user cannot edit this field)
           Home Telephone Number --- (text) --- (user cannot edit this field)
           Alternate Telephone Number 1 --- (text) --- (user cannot edit this field)
           Alternale Telephone Number 2 --- (text) --- (user cannot edit this field)
           Alternate Telephone Number 3 --- (text) --- (user cannot edit this field)
           Letter Sent? --- (yes or no) --- (user cannot edit this field)
           Date Letter Sent --- (date) --- (user cannot edit this field)
           Temporary Change of Address --- (text box) --- (user can edit this field)
           Special Considerations --- (text box) --- (user can edit this field)
           Additional Information --- (text box) --- (user can edit this field)

           Any help is very much appreciated.

           Kind regards,

           Chris.

        • 1. Re: Planning my FileMaker Pro 12 Database
          schamblee

               Why do you have 6  different Emergency Contact tables?  You didn't state the information for four of those tables.  The first two tables could be combined into 1 table. (then you wouldn't need a relationship)  You can have several layouts based on one table.  You could even have one layout with a tab control and put each Detail on a different tab.  The parent table (primary id)  would have the auto-enter id.  The child table (foreign id) would not be auto-enter.

          • 2. Re: Planning my FileMaker Pro 12 Database
            ChrisSmythe

                 Hello S Chamblee, thank you for you kind assistance and help - greatly appreciated.

                 In reply, I have listed below all my proposed tables and the proposed fields within those tables.

                 I believe your suggestion of combining  the Personnel Details table and all six Emergency Contact Details tables is a great idea.  As you suggested, I could have one layout with a tab control and put each Detail on a different tab.  As shown in my proposed fields (for the Emergency Contacts Details table) I have annotated a Foreign ID field (not sure if I have done this correctly or how it works)?

                 Could you please advise and how I can set up the appropriate relationships for the following?

                 One new large table containing the fields from my the Personnel Details table and all the fields from the six Emergency Contact Details tables.

                 And, the Operation/Exercise table and the Deployment Location table.  A total of three tables.

                 If I need to change any of my field names to make this work, could you please advise accordingly?

                 You kind assistance is very much appreciated.

                 Kind regards,

                 Chris.

                 List of new proposed tables for my FileMaker database:

            Personnel/Emergency Contact Details table:

                 AutoID --- (Auto-enter Serial) --- (user cannot edit this field)
                 Deployment Status --- (either Not Deployed, Pending or Deployed) --- (user cannot edit this field)
                 Employee ID --- (text) --- (user cannot edit this field)
                 Title --- (text) --- (user cannot edit this field)
                 Last Name --- (text) --- (user cannot edit this field)
                 Given Name(s) --- (text) --- (user cannot edit this field)
                 Service --- (Army, Navy, Air Force, Civilian or Contractor) --- (user cannot edit this field)
                 DVA Consent --- (yes or no) --- (user can edit this field)
                 Operation/Exercise --- (text) --- (user can edit this field)
                 Deployment Location --- Text) --- (user can edit this field)
                 Deployment Date --- (date) --- (user can edit this field)
                 RTA Date --- (date) --- (user can edit this field)
                 This form created by --- (text) --- (user cannot edit this field)
                 Created on --- (date) --- (user cannot edit this field)
                 Last updated by --- (text) --- (user cannot edit this field)
                 Last modified on --- (date) --- (user cannot edit this field)

            Emergency Contact 1

                 Primary Emergency Contact --- (text) --- (user cannot edit this field)
                 Next of Kin --- (text) --- (user cannot edit this field)
                 Title --- (text) --- (user cannot edit this field)
                 Given Name(s) --- (text) --- (user cannot edit this field)
                 Last Name --- (text) --- (user cannot edit this field)
                 Relationship --- (text) --- (user cannot edit this field)
                 Address 1 --- (text) --- (user cannot edit this field)
                 Address 2 --- (text) --- (user cannot edit this field)
                 Address 3 --- (text) --- (user cannot edit this field)
                 Address 4 --- (text) --- (user cannot edit this field)
                 Surburb --- (text) --- (user cannot edit this field)
                 State --- (text) --- (user cannot edit this field)
                 Postcode --- (text) --- (user cannot edit this field)
                 Country --- (text) --- (user cannot edit this field)
                 Home Telephone Number --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 1 --- (text) --- (user cannot edit this field)
                 Alternale Telephone Number 2 --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 3 --- (text) --- (user cannot edit this field)
                 Letter Sent? --- (yes or no) --- (user cannot edit this field)
                 Date Letter Sent --- (date) --- (user cannot edit this field)
                 Temporary Change of Address --- (text box) --- (user can edit this field)
                 Special Considerations --- (text box) --- (user can edit this field)
                 Additional Information --- (text box) --- (user can edit this field)

            Emergency Contact 2

                 Primary Emergency Contact --- (text) --- (user cannot edit this field)
                 Next of Kin --- (text) --- (user cannot edit this field)
                 Title --- (text) --- (user cannot edit this field)
                 Given Name(s) --- (text) --- (user cannot edit this field)
                 Last Name --- (text) --- (user cannot edit this field)
                 Relationship --- (text) --- (user cannot edit this field)
                 Address 1 --- (text) --- (user cannot edit this field)
                 Address 2 --- (text) --- (user cannot edit this field)
                 Address 3 --- (text) --- (user cannot edit this field)
                 Address 4 --- (text) --- (user cannot edit this field)
                 Surburb --- (text) --- (user cannot edit this field)
                 State --- (text) --- (user cannot edit this field)
                 Postcode --- (text) --- (user cannot edit this field)
                 Country --- (text) --- (user cannot edit this field)
                 Home Telephone Number --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 1 --- (text) --- (user cannot edit this field)
                 Alternale Telephone Number 2 --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 3 --- (text) --- (user cannot edit this field)
                 Letter Sent? --- (yes or no) --- (user cannot edit this field)
                 Date Letter Sent --- (date) --- (user cannot edit this field)
                 Temporary Change of Address --- (text box) --- (user can edit this field)
                 Special Considerations --- (text box) --- (user can edit this field)
                 Additional Information --- (text box) --- (user can edit this field)

            Emergency Contact 3

                 Primary Emergency Contact --- (text) --- (user cannot edit this field)
                 Next of Kin --- (text) --- (user cannot edit this field)
                 Title --- (text) --- (user cannot edit this field)
                 Given Name(s) --- (text) --- (user cannot edit this field)
                 Last Name --- (text) --- (user cannot edit this field)
                 Relationship --- (text) --- (user cannot edit this field)
                 Address 1 --- (text) --- (user cannot edit this field)
                 Address 2 --- (text) --- (user cannot edit this field)
                 Address 3 --- (text) --- (user cannot edit this field)
                 Address 4 --- (text) --- (user cannot edit this field)
                 Surburb --- (text) --- (user cannot edit this field)
                 State --- (text) --- (user cannot edit this field)
                 Postcode --- (text) --- (user cannot edit this field)
                 Country --- (text) --- (user cannot edit this field)
                 Home Telephone Number --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 1 --- (text) --- (user cannot edit this field)
                 Alternale Telephone Number 2 --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 3 --- (text) --- (user cannot edit this field)
                 Letter Sent? --- (yes or no) --- (user cannot edit this field)
                 Date Letter Sent --- (date) --- (user cannot edit this field)
                 Temporary Change of Address --- (text box) --- (user can edit this field)
                 Special Considerations --- (text box) --- (user can edit this field)
                 Additional Information --- (text box) --- (user can edit this field)

            Emergency Contact 4

                 Primary Emergency Contact --- (text) --- (user cannot edit this field)
                 Next of Kin --- (text) --- (user cannot edit this field)
                 Title --- (text) --- (user cannot edit this field)
                 Given Name(s) --- (text) --- (user cannot edit this field)
                 Last Name --- (text) --- (user cannot edit this field)
                 Relationship --- (text) --- (user cannot edit this field)
                 Address 1 --- (text) --- (user cannot edit this field)
                 Address 2 --- (text) --- (user cannot edit this field)
                 Address 3 --- (text) --- (user cannot edit this field)
                 Address 4 --- (text) --- (user cannot edit this field)
                 Surburb --- (text) --- (user cannot edit this field)
                 State --- (text) --- (user cannot edit this field)
                 Postcode --- (text) --- (user cannot edit this field)
                 Country --- (text) --- (user cannot edit this field)
                 Home Telephone Number --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 1 --- (text) --- (user cannot edit this field)
                 Alternale Telephone Number 2 --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 3 --- (text) --- (user cannot edit this field)
                 Letter Sent? --- (yes or no) --- (user cannot edit this field)
                 Date Letter Sent --- (date) --- (user cannot edit this field)
                 Temporary Change of Address --- (text box) --- (user can edit this field)
                 Special Considerations --- (text box) --- (user can edit this field)
                 Additional Information --- (text box) --- (user can edit this field)

            Emergency Contact 5

                 Primary Emergency Contact --- (text) --- (user cannot edit this field)
                 Next of Kin --- (text) --- (user cannot edit this field)
                 Title --- (text) --- (user cannot edit this field)
                 Given Name(s) --- (text) --- (user cannot edit this field)
                 Last Name --- (text) --- (user cannot edit this field)
                 Relationship --- (text) --- (user cannot edit this field)
                 Address 1 --- (text) --- (user cannot edit this field)
                 Address 2 --- (text) --- (user cannot edit this field)
                 Address 3 --- (text) --- (user cannot edit this field)
                 Address 4 --- (text) --- (user cannot edit this field)
                 Surburb --- (text) --- (user cannot edit this field)
                 State --- (text) --- (user cannot edit this field)
                 Postcode --- (text) --- (user cannot edit this field)
                 Country --- (text) --- (user cannot edit this field)
                 Home Telephone Number --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 1 --- (text) --- (user cannot edit this field)
                 Alternale Telephone Number 2 --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 3 --- (text) --- (user cannot edit this field)
                 Letter Sent? --- (yes or no) --- (user cannot edit this field)
                 Date Letter Sent --- (date) --- (user cannot edit this field)
                 Temporary Change of Address --- (text box) --- (user can edit this field)
                 Special Considerations --- (text box) --- (user can edit this field)
                 Additional Information --- (text box) --- (user can edit this field)

            Emergency Contact 6

                 Primary Emergency Contact --- (text) --- (user cannot edit this field)
                 Next of Kin --- (text) --- (user cannot edit this field)
                 Title --- (text) --- (user cannot edit this field)
                 Given Name(s) --- (text) --- (user cannot edit this field)
                 Last Name --- (text) --- (user cannot edit this field)
                 Relationship --- (text) --- (user cannot edit this field)
                 Address 1 --- (text) --- (user cannot edit this field)
                 Address 2 --- (text) --- (user cannot edit this field)
                 Address 3 --- (text) --- (user cannot edit this field)
                 Address 4 --- (text) --- (user cannot edit this field)
                 Surburb --- (text) --- (user cannot edit this field)
                 State --- (text) --- (user cannot edit this field)
                 Postcode --- (text) --- (user cannot edit this field)
                 Country --- (text) --- (user cannot edit this field)
                 Home Telephone Number --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 1 --- (text) --- (user cannot edit this field)
                 Alternale Telephone Number 2 --- (text) --- (user cannot edit this field)
                 Alternate Telephone Number 3 --- (text) --- (user cannot edit this field)
                 Letter Sent? --- (yes or no) --- (user cannot edit this field)
                 Date Letter Sent --- (date) --- (user cannot edit this field)
                 Temporary Change of Address --- (text box) --- (user can edit this field)
                 Special Considerations --- (text box) --- (user can edit this field)
                 Additional Information --- (text box) --- (user can edit this field)

            Operation/Exercise table:

                 Foreign ID --- (user cannot edit this field)
                 Operation/Exercise Name --- (text) --- (user can edit this field)

            Deployment Location table:

                 Foreign ID --- (user cannot edit this field)
                 Deployment Location --- (text) --- (user can edit this field)

            • 3. Re: Planning my FileMaker Pro 12 Database
              schamblee

                   I looks like you only need one Emergency Contact Table. 

                   You would Have a Primary id for the Member then a foreign id in the Emergency Contact table.

                   I created a sample app with two tables. It displays the Emergency Contact in a portal.  You can click on a contact and it will take you to their record in the Emergency Contact layout.

              https://www.sugarsync.com/pf/D9559058_790_730280021

                   Maybe this will help you get started.  The layouts can be dressed up more, but again this is just a sample.

              • 4. Re: Planning my FileMaker Pro 12 Database
                ChrisSmythe

                     Hello S Chamblee, thanks again for all your wonderful help - really nice work.  Also, thank you for your database sample - look great.

                     Kind regards,

                     Chris.