Jump to content

serial increment text value


This topic is 6177 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
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

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
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

Link to comment
Share on other sites

This topic is 6177 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

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