AnsweredAssumed Answered

Planning my FileMaker Pro 12 Database

Question asked by ChrisSmythe on Oct 8, 2012
Latest reply on Oct 8, 2012 by 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.

Outcomes