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]
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.