serial increment text value

[Crimin]

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

[LaRetta]

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

[Genx]

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

[Crimin]

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.

[Crimin]

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.

[LaRetta]

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.

LaRetta