Jump to content
Sign in to follow this  
Crimin

serial increment text value

Recommended Posts

Hello

I have 2 tables. one being CUSTOMERS with a "customer code" field with a string of 4 letters. I then have a INVOICES table that has a "invoice ID" field.

Each time I create a new invoice I would like the "invoice ID" to pull the value from the "customer code" and increment it by 1.

for eample CUST0001,CUST0002,CUST0003 where "CUST" is the value from the "customer code" field.

I have been trying to set the "invoice ID" field as a calculation with the serial increment function would this be correct.

Thanks for the help

Share this post


Link to post
Share on other sites

Please don't approach serial incrementation in this fashion. It will bring nothing but headache. Establish a proper relational structure from the very beginning. Example:

Customer table would have

CustomerID, auto-enter serial, increment by 1

Invoices table would have

InvoiceID, auto-enter serial, increment by 1

CustomerID, (you insert the CustomerID when you create a new invoice)

Each table should have its own primary key (an auto-enter serial, FM generated). Then, when you want to relate that table to another, you insert the primary key into the other table as a foreign key.

The relationship would be:

Customers::CustomerID = Invoices::CustomerID

Again, establish proper relational structure and serializing. We see thousands of attempts of attaching meaning to the IDs, only to have it break. If you can grasp the importance of this concept, it will save you thousands of hours of re-write. Set it up right to begin with. I have never said anything more seriously. Honestly.

LaRetta

Share this post


Link to post
Share on other sites

And then later if you want to shove "Cust" in front of it (so it looks prettier), you can do it with a calc field i.e.: "CUST" & CustomerID

Share this post


Link to post
Share on other sites

I don't want to hard code the "Cust" since there will be many different customer codes. I want it to pull the code from the CUSTOMER table.

Share this post


Link to post
Share on other sites

Thank you I do have a relationship set up between the CUSTOMER table and the INVOICES table with Primary and Foriegn keys. I guess I am looking for help with what the calculation would look like.

Share this post


Link to post
Share on other sites

I'm sorry but we appear to be missing each other entirely. IDs which are used to bind relationships together should be meaningless - not 4-character user-defined as it appears you are doing, ie "customer code field with a string of 4 letters."

You then are also (it appears) attempting to manipulate your InvoiceID, ie, "Each time I create a new invoice I would like the "invoice ID" to pull the value from the "customer code" and increment it by 1."

Both of these concepts go against good structure and will eventually break causing your customers to lose (orphan) their invoices. If you wish to explain more then I will help you all I can. But I cannot help someone hang themselves without serious warning. I don't yet feel you understand my suggestions. Can you post a sample of your exact structure and relationship currently? It might be easier to work through this. :wink2:

LaRetta

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.