8 Replies Latest reply on Feb 18, 2012 8:17 AM by willrollo

    Relationship Issues!


      Relationship Issues!


      I have a small glitch in my database - I am sure it is very simple to fix but I cannot find where I am going wrong. I have attached my relationship diagram. 

      What I am trying to do is to create a 'Pastel number' to a customer. Layouts are:

      Enquiries (linked to Customers)

      Order Detail Quote (linked to invoice details)

      Order Detail Order (linked to invoice details)

      Order Detail Invoice (linked to invoice details).

      When on an enquiry page, there are buttons to create a quote and to create an order. If it is the customer's first order, then a pastel number is to be created. But only if an order is created, not a quote. Each customer can only have one Pastel number (it is for accounting) and it needs to be sequential so I cannot just use the pk_customer_id serial number.

      I had added a few lines into my 'create order from enquiry' script to use an If function

      If [isempty(pastel::pastel ID)]

         Set Field [Pastel::Pastel pk; Customers::pk_customer_id]

      End if 


      But is doesnt work. as it creates a pastel number each time I create an order for the same customer

      The pastel ID field should be the unique serial number that should stick to a customer/enquiry (same thing really) and not be renewed each time an order is created -but only be created if an order is placed and not a quote. 

        • 1. Re: Relationship Issues!
          /files/8b20e76060/2012-02-16_08.08.28_pm.png 940x419
          • 2. Re: Relationship Issues!

            So the "pastel" number is unique to each customer, sequential and only assigned the first time they place an actual order (not make an enquiry or get a quote).

            Here's how I've been able to generate "on demand" serial numbers in a way that works safely even in mult-user environments where two more users might be issuing such numbers simulatneously"

            In your Pastel table, define Pastel pk as an auto entered serial number. This field will be the actual Pastel number. To keep naming conventions consistent, I'd change the name of Pastel ID to fk_Customers_id, but this is just a name change, it doesn't affect how this works if you keep the field names that you have.

            Enable "allow creation of records via this relationship" for Pastel in the customers to Pastel relationship.

            Now this script step, when run from customers or invoice details, will assign your Pastel numbers:

            Set field [Pastel::fk_Customers_ID ; Customers::pk_Customers_id]

            This script can be set up with a script trigger or button so that any action that creates a record in Orders also performs this script step.

            Here's how it works:

            If there is no record in Pastel with the current customer's ID number, the "allow creation" option kicks in and a new record is created. This new record then generates the new Pastel number in Pastel pk. If a record already exists in Pastel, no change occurrs. IF customer Id is 1234 when the script is performed and there's a related record in Pastel, it's fk_customers_id field already stores the matching 1234 value and the set field simply sets it to 1234, the value that it already has.

            • 3. Re: Relationship Issues!

              Hi Phil

              Thank you. Hvae got that script to work but havent fully tested it out yet on my db. It has come up with an error though on some records. I will fiddle around with it a little more to find out why and get back to you if I cant work it out. I was just wondering, if i wanted the PAstel number to start with the first 3 letters of the customers surname or company name (I have already created a calculated field to show surname, or if there is a company, the company name. Customers::enquiry name) and then the serial number. ie, for Mr Smith it would be SMI1234 or if he had a business, (say ACME) it would be ACM1234

              Thank you and have a good weekend..


              • 4. Re: Relationship Issues!

                I seem to get this message when I run the script. 

                This operation cannot be performed because one or more required related records are not available and cannot be created.


                Kind of odd. It was working but now stopped. I did remove the Pastel PK field as didnt think it was in use anymore - so I just have the 

                fk_Customers_id, field as you suggested in my pastel table...

                Not entirely sure why this error is appearing. I do have the 'allow creation of new records' box ticked in the relationhip between fk_Customers_id and pk_customers_id. 


                • 5. Re: Relationship Issues!

                  Pastel PK IS the pastel number without it, you do not have a pastel number generated on demand by your script. You need both ID fields, the fk field links it to customers for use in the relationship, The pk field generates the pastel number (or at least the numeric part if you want to combine it with text).

                  If you added this step to a longer script and have FileMaker Advanced, enable the script debugger and step through your script to see which step in your script is triggering this error message.

                  If you do not have advanced try putting show custom dialog steps in different parts of your script to narrow down where you are getting this error message.

                  • 6. Re: Relationship Issues!

                    HI Phil

                    I was actually using the basic script that you suggested which was creating the error! I thought I would write down exactly my table/field situtation and the script so you can maybe see what is going on.

                    Anyway, this is what I have..

                    Customers::pk_customers_id  - Auto-enter serial number, on commit, unique, numerical only, Allow overide.

                    Pastel::pastel pk - Auto-enter serial number, can't modify, required value, Allow overide.

                    Pastel::Customers fk

                    Relationship between them: Customers::pk_customers_id TO Pastel::cutomers fk. (create new records from Pastel side) Think this is a one to many relationship, but isnt it supposed to be a one to one relationship seeing as each custome can only have one pastel number?

                    Script is: Set field [Pastel::Pastel pk; Customers::pk_customers_id]

                    If I run the script, both the pastel fields (Pastel pk and customer fk) autofill from the Customers::pk_customer_id field. 

                    I am sure it is something simple - if have doen a few things but nothing seems to work - I jusat get that error message mentioned earlier. 

                    • 7. Re: Relationship Issues!

                      Pastel number is NOT Pastel::Customers fk. It is Pastel::pastel pk.

                      The relationship will be one to one. FileMaker will not "know" that it is one to one unless you add a unique values validation rule to the Pastel::Cusomters fk field and thus it won't show it in Manage | database | relationships like that. You can add that validation if you want, but our current design precludes the the creation of more than one record in Pastel for any given Customer record so you don't have to.

                      Script step should be:

                      Set field [Pastel::Customer fk ; Customers::pk_Customers_id]

                      • 8. Re: Relationship Issues!

                        Well that clears that up! Thank you...