2 Replies Latest reply on Apr 20, 2016 11:47 AM by EssexBiker

    Relationships.... !


      Okay,  I think I am being particularly dense here.


      I have been building databases in FM for a few years now, okay, there's been a bit of trial and error... I learn by playing.


      SO, after changes in the workplace something  that I built a while ago wasn't quite suitable. I have  started over from scratch.


      Tables are:

      Employees table I have put a pk_EmployeeID, fk_CompanyID and a OustationID

      Companies with a pk_CompanyID -  auniqueID... ( I have used the new get(UUID) function) There's also a fk_OustationID

      Outstations with a pk_OustationID ( as above) and also a fk_company ID and a fk_employeeID too



      We have Companies, each company could potentially have a few locations (Outstations) One company may have many Outstations - (One to many

      Some companies don't have an outstation, so that an option.


      We have employees, an Employee  table with records would normally be set up to be assigned to Company and if  applicable their outstation that they're are assigned to. It's unlikely but over time their company or company or outstation could possibly change.


      I do have layouts that are in the context of Employees table have merge fields, I use these to produce documents such as contracts of employment.


      I seem to be able to get the employee's company to change and display a merge field, but not  the outstation, ( either display, or change)


      Any help would be, as ever, gratefully received.

        • 1. Re: Relationships.... !

          Howdy Essex,

          If an outstation is only ever with one company, it seems more direct to set up


          Employee >--- Outstation >--- Company


          In this way, changing outstation is all you need...this change will automatically connect to the correct company (the one owning the station).


          In your current setup, it looks possible to have an employee assigned to an outstation of a company that does not own that outstation.  Is this correct?

          • 2. Re: Relationships.... !



            Howdy back at you Eric,


            You're right an outstation(s) will only ever belong to a single company.


            An employee will belong to a single company  (that will always be the case)


            But some of our companies have a one or more  outstations,  conversely some companies do not have an outstation.



            Company   -  Stephensons of Essex

            Outstation.... N/A


            Stephensons Maldon

            Outstations... N/A




            Stephensons Witham

            Outstations  Chelmsford, Clacton & Ipswich


            Stephensons Haverhill

            Outstations Bury St Edmunds & Braintree


            So, when I set an employee record up I will definitely need assign them to a compnay,  and if applicable assign them to an outstation too.


            I think I am just being a bit "Blonde", here....



            Really good to get some support, thanks from, "Across the pond" x