Simple Project Database
I am very new to Filemaker so I apologise if this is v basic stuff but I have scoured help files for an answer to this for a whie now.
I work for a small design company and for years we have been using a single excel spreadsheet to log projects as they come in, allocate them unique job numbers and track various things against each project - eg. role on the job - who was the client, architect, engineer etc. This is a v simple spreadsheet: a single page, no macros or xrefs etc. Information is duplicated in some other files, more spreadsheets dedicated to lists of contacts which contain their contact details etc. I think you can see where this is going... I think a proper database might be in order, simply logging jobs in one table, contacts in another and relating them together etc. If I did this then I imagine I might be able to print off contact lists which automatically update with lists of projects they have collaborated on etc. That is the dream. I think Filemaker might make a wise purchase, so am exploring the trial.
So far, I can set up one table - 'Jobs' - with fields 'Jobs::JobID', 'Jobs::Job Name' and 'Jobs::Company' etc. I can then relate this to a second table - 'Contacts' - which has the fields 'Contacts::Company', 'Contacts::Address' etc. I can make a portal to display the jobs which this company has worked on which is v nice.
However, for each record in 'Jobs' I might want 5 different fields, to refer to 5 different companies. These might be 'Jobs::Architect' and 'Jobs::Engineer' and 'Jobs::Client' for instance. My first instinct would be to try and link the 'Contacts::Company' to each of these in the relationship graph, but this creates an AND relationship which obv doesn't work... I can do this by creating separate tables eg. 'Architect Contacts' for each role but it seems a bit cumbersome. Some companies have worked with us in different roles - eg. sometimes as structural engineer, sometimes as architect which might cause duplication in the various contact lists, which we don't want. Sometimes we might also have more than one client, or multiple other engineers on a project. I have struggled to get my head around how to do this with a single contacts table: I think this is attempting a many to many relationship between the jobs and contacts tables, so I tried to make an intermediate table... but wasn't really sure why I had done this.
Am I approaching this the right way - I think this is more complex than I first thought... as I said, I am pretty much a beginner when it comes to Filemaker - should I give up now?!
Thanks in advance for any wisdom!