3 Replies Latest reply on Feb 24, 2011 10:10 AM by philmodjunk

    Simple Project Database



      Simple Project Database


      Hi Everybody.

      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!


        • 1. Re: Simple Project Database

          Keeping with the "separate fields for each company" approach for a minute, think "add new table occurrences" not "add new tables". In Manage | Database | relationships, you can click your Contacts Table Occurrence (that's what each box on this graph is called), then click the button on bottom left with two green plus signs to make a copy of it. This new table occurrence points to the same Contacts table, but now you can set up a new relationship separate from the one you used for the original Contacts Table occurrence.

          As you continue to explore FileMaker, you may want to research "Many to Many" relationships and "join" tables as yet another approach that avoids having a rigid set of company fields here.

          To learn more about Table Occurrences and how to use them in FileMaker: 

          Tutorial: What are Table Occurrences?

          • 2. Re: Simple Project Database

            Thanks for the reply @PhilModJunk

            I think I managed to do that one time.  I kept all of my contacts in the single table, then made a new occurrence of it.  I (probably wrongly) decided this wasn't what I should be doing as I think I needed to then make another portal to show the information on the contacts form.........

            I am making this with the intention that someone else will use it. I am thinking that Bento might be more suitable all things considered. If it was purely for me I think I would stick with FM... I knocked exactly what I need together in Bento in about 5 mins though, it just looks a bit ugly...



            • 3. Re: Simple Project Database

              Additional portal aren't strictly necessary--going by what you describe here. Setting up a many to many relationship via a join would likely be a much more flexible approach.