4 Replies Latest reply on May 16, 2011 7:42 AM by IanRycroft

    Relationship problem...I think!

    IanRycroft

      Title

      Relationship problem...I think!

      Post

      Hi there, I am hoping the knowledgable on here are going to be able to help a novice out.

      I created a company database some years ago when Filemaker was version 6, nothing fancy, just an address book, estimate, job sheet and purchase order system. Although I had no knowledge I managed to get to the point that it has worked for us for many years and I'm sure a Filemaker developer would fall about laughing if he took a look at it.

      I'm now trying to recreate a similar system using Filemaker Pro 11. I've used the address book that comes with the application and then added the extra bits we require but this time trying to use key fields, something I didn't on the original, but I cannot for the life of me get it to work in the way I want.

      As far as I can tell it's something to do with how I am linking the the tables to the address book. If I create more than one estimate and click the 'New Job Sheet' button it creates a new Job sheet record and inserts the address info into it (via a lookup I created) but if create any more estimates using the same client address it links to the original one rather than letting me have numerous estimates and job sheets with same address info but different job numbers and Project numbers. Also seems to link the total cost of two estimates into one job sheet with the same client info.

      I realise this may all be too vague to give a direct answer but any help would ad least give me a fighting chance to solve it.

      I have screen grabbed the relationship pages and the tables (client info blurred out)

      Many thanks

      filemaker.jpg

        • 1. Re: Relationship problem...I think!
          philmodjunk

          We need to know more about your Estimates layout. To which "box" (Table occurrence), in Manage | Database | Relationships does it refer to in Show Records From? (I would assume Estimates, but let's be sure.)

          To which Does the portal at the bottom of the layout refer? (Check Show Records From in portal setup.)

          Are all the fields inside this portal from the same table occurrence as the portal or are some from another table occurrence?

          • 2. Re: Relationship problem...I think!
            IanRycroft

            I'll do my best to give a better explanation of the set up.

            1: The Contact management is the one supplied with Filemaker Pro 11 and has not been changed.

            2: The Estimates is a Table that also has a portal that show records from another Table called 'Estimate Details'.

            3: The 'Studio Job Sheet' is a Table that also has a portal that show records from another Table called 'Job Sheet Details'

            Each of the Tables I have created have a primary and a foreign key for the purpose of linking the files. At present the Contact Management is linked from k_ID_Contact to the Estimate Table to _fkContactID.

            The 'Estimate' Table is linked to the 'Job Sheet' Table via relationship from 'Estimate Number' on Estimates Table to 'Estimate Number' on Job Sheet Table.

            I'm not sure if this answers your request but if not please let me know so I can give you more info.

            Thanks for your reply, really appreciate it.

            • 3. Re: Relationship problem...I think!
              philmodjunk

              Layouts and tables are two different objects in a FileMaker database. Layouts have portals. Tables do not.Wink

              If I create more than one estimate and click the 'New Job Sheet' button it creates a new Job sheet record and inserts the address info into it (via a lookup I created) but if create any more estimates using the same client address it links to the original one rather than letting me have numerous estimates and job sheets with same address info but different job numbers and Project numbers.

              Seems like clicking this button performs a script and this script is not creating new records in the correct table with the correct ID numbers to properly link it to the correct client record in contact Management.

              Clicking New Estimate should do something similar to this:

              #For a new estimate record for the same client
              #Performed from Button clicked on the estimates table:
              Set Variable[$ID ; Estimates::_fkContactID ]
              New Record/Request
              Set Field [Estimates::_fkContactID ; $ID ]

              Also, the field: pkEstimateID should be an auto-entered serial number.

              You appear to have two fields in Estimate where only one should be used. I'd like Job Sheets to the same field, _Pk_EstimateID in estimates as you do Estimate details.

              Your script would then function like this:

              #For a new job sheet record for the same client
              #Performed from Button clicked on the estimates table:
              Set Variable[$EstID ; Estimates::_fkContactID]
              Go To Layout [//specify job sheets layout]
              New Record/Request
              Set Field [Job Sheet::_fk_EstimateID ; $EstID ]

              • 4. Re: Relationship problem...I think!
                IanRycroft

                Sorry for delay in my reply. Part of the problem is this is one small part of my studio duties so haven't been back on it for a few days. Once I am I will see if I can work through your suggestions and see if it works.

                Appreciate your help. I feel it's going to be a long drawn out affair but I'm determined to get it working.