Selective serial number

[Anuviel]

In my invoice table when new invoice is created a PO number is automatically assigned (PO number creates records in my line items table, if no PO number is created in invoice table, no records can be created in line items table).

I have an auto enter serial number which acts as Invoice number as well. Every time new invoice is created the serial number is increased by 1 - it is just a regular number.

PO number takes that serial number and adds the first 3 letters of the customer company as a prefix.

So when creating a new record the generation goes like this (assume it is the first record created and two customers are in play called x and y)

1st record for customer x

Invoice number = 1

PO number = x1

1st record for customer y

Invoice number = 2

PO number = y2

2nd record for customer x (3rd invoice)

Invoice number = 3

PO number = x3

That is my question - see in the 3rd instance the PO number is x3 but it is only second order for customer x.

I want to keep the invoice number a 3 as it is the 3rd invoice but I want the PO number to be x2 as it is a 2nd order for customer x, same for the customer y so it should look like:

1st record for customer x

Invoice number = 1

PO number = x1

1st record for customer y

Invoice number = 2

PO number = y1

2nd record for customer x (3rd invoice)

Invoice number = 3

PO number = x2

What would be the best way to accomplish that.

Thank you very much.,

P.S. Sorry for the length of the post.

[Osman]

I think you can solve this problem by a relationship . Define a relationship with invoice table within itself by matching customer id fields.

The second step is in PO number calculation. You may change it like:

customer & count(new_relation:field_that_does_not_empty) + 1

[Anuviel]

Thank you - got it., Nice.

[comment]

Beware of conditional serial numbers - they could easily fail in a multi-user scenario:

and others.

[Anuviel]

Wow, thank you - did not know that. I checked all of the links your provided.

Looking at our serial number requirements I believe that the following will work.

Our PO serial numbers consist of Customer serial number (customer ID) - Unique number, auto enter with C1 increments of 1. Then the Count +1 is appended as described in the post above.

I tested the creation of double records with the same number by opening of the new window and hitting create to get the serial but staying in the field and then creating one more in the new window - and there you have it a duplicate serial number - it was not allowed though as I set the field to be unique so the record got reverted. To get past that I added another serial field with auto enter number of 1 increment of 1 (entered on creation) and added it into my PO unique number calculation which should prevent duplicate serial numbers even if two records are created at the same time. I tested this with multiple widows and it was fine.

The PO number is an auto enter calculation, not modifiable, unique.

When I create a new invoice and choose a customer the PO number is created.

It looks like this:

Customer_ID_Number & Serial_For_PO_Number & "-" & Count ( Invoices 2::Invoice_Number) +1

Customer_ID_Number - Indexed, Auto enter serial, Can't modify Auto

Serial_For_PO_Number - Auto enter serial

For us it does not matter if the serial number gets long or how it actually looks like as long as it contains the customer ID plus the actual number of the order (what order it is for that customer like 2nd order or third or 56th and so on)

It looks like this:

Two customers, their ID's C10 & C16 - the PO numbers look like:

C101-1

C162-1

C163-2

C164-3

C105-2

So they will always be unique thanks to the Serial_For_PO_Number field always being unique.

Hope that the above made sense and if I am on the wrong path please let me know.

Thank you.,

[comment]

For us it does not matter if the serial number gets long or how it actually looks like as long as it contains the customer ID plus the actual number of the order

Then why bother at all? You can count the customer's orders (or the customer's previous orders) at any time using an unstored calculation.

[Anuviel]

Ok, did not know that., a lot of things I do not know, but am learning. Thank you., will look it up.