A newbie question concerning generating serial numbers / data.
Hello all, I am rather new to FMP and have encountered a problem I've inherited from my predecessor who started building the database. Hopefully someone will be kind enough to help, here's the scenario:
The database is currently used to store information on customers who have decided to take on our services (or in the office lingo, have "gone live"). When a customer goes "live" and they're added to the database two serial numbers are automatically generated for them on creation: "CustomerID" and "QTS No". Both of these serial numbers and all the relevant customer information fields are in the same table. This information is added in the "Live Layout".
What I want to do is include in the database information from potential customer's enquiries (e.g Name, Phone Number, Work Site) so that if they were to "go live" a lot of their information would already be on the system. This would be entered in the "Enquiry Layout".
I want to use the generated "CustomerID" field for both enquirying customers and "live" customers as there is another table called "Calls" (that allows user entry of correspondance) which shares the "CustomerID".
On the "Enquiry Layout" I want to have a button named "Go Live" which runs a script that goes to the "Live Layout" and also generates the "QTS No" (QTS0001, QTS0002, QTS0003, etc).
I've been tearing my hair out trying to find out how to do this. Ideally I'd like to do this using just the one table as I want to avoid duplicating information and I want to keep one record per customer. Rather than using the auto-entry serial generator is there a script that would peform the same function and would assign a unique serial number at the push of a button?
Thanks for reading this, any help would be most appreciated. Sorry if it doesn't make much sense, I've been working on this for 15 hours straight today and my brain is turning to mush!
you have spoken about layouts ( Live Layout, Enquiry Layout ) but not about tables.
As you asked to have an unique CustomerID, I suggest you to place both type of customer ( live or potential ) into the same table ( Customers ), having a field ( type ? ) to differentiate them.