I'm creating my first DB, I have 2 tables one called Clients and the other one called Invoices.

I made a relation from Clients.ClientID to Invoices.ClientID.

Then in the layout I made a portal to show all invoices for each client and being able to add more and edit them. It works fine.

The problem is I want to have a serial number in each invoice made of 3 parts ClientID-InvoiceNumber-Year (being Invoice number the number of invoices for the current client and not the total number of invoices)

I also would like to be able to show a number next to every invoice showing the invoice number for the current client. Just like this:

Client 1

Client Data....

Invoices:

1: Invoice1

2: Invoice2

...

Client 2

Client Data....

Invoices:

1: Invoice1

2: Invoice2

(Invoice1 and 2 in Client1 are not the same as Invoice1 and 2 in Client2)

Does all this make sense?

You'll need a self-relationship in Invoices based on ClientID and an auto-enter calculation of Last(rel::InvoiceNumber) + 1 for the InvoiceNumber. It may be more complicated, depending on what your invoice numbers really look like and whether they are text or numbers. Then you can use an auto-enter calculation like ClientID & "-" & InvoiceNumber & "-" & Year(Get(CurrentDate)).

GetAsNumber(InvoiceNumber) will give you the numbers only.

It may help for you to zip and attach a sample file with a few records demonstrating how they're supposed to look. What I've provided may not be specific enough for your needs.