actionman007 Posted February 26, 2007 Share Posted February 26, 2007 Hi, I currently have a system that stores customer names and associated projects. My client has asked if each customer could have a serial number after their name - which would make up that particular project ID. For example; Customer Bob - Customer Bob-001 Customer Bob - Customer Bob-002 Customer Gill - Customer Gill-001 Customer Gill - Customer Gill-002 Can I automate this so that each customers project ID starts at 001 on creation? And add the functionality of when new customers are added to the system they each have a number assigned to their projects starting from 001. Hope this makes sense... it's driving me a bit insane!!! Cheers, Dan : Link to comment Share on other sites More sharing options...
jamesducker Posted February 28, 2007 Share Posted February 28, 2007 Yes. Assuming the 'customers' and 'projects' files are correctly related both ways, you should: 1) create a field in the 'customers' table called "last project ID". This should be a calculation field defined as: max(projects::project ID) 2) set the "project ID" field in the 'projects' table to be a text field with an auto-enter calculation defined as: customers::last project ID + 1 It would be best to disallow editing of that field at any time. 3) create a field in 'projects' called "unique project ID". This should be a calculation field defined as: customers::name & "-" & project number James Link to comment Share on other sites More sharing options...
actionman007 Posted March 1, 2007 Author Share Posted March 1, 2007 Thanks so much - this has solved a major headache - Have a virtual beer on me!!! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6267 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now