I am trying to create a database with three levels of relationship one Sale has many reciepts which in turn can have many payments. This is for a simple sales system. I am having a problem though. I can not see how to get the numbers of records to appear as I would like.

Ideally I would like the sale to have a number Date-number for that day so that each day the sale number would start at e.g.(01/01/02-(sale)1 Literallly 01/01/02-1 and rise by one for each new sale.

I would like this format to then follow through for my reciept(most peapole think of this as an invoice) (date)01/01/02-(sale)1-(Reciept)1 Literally 01/01/02-1-1 rising by one for each aditional reciept.

Finally I then want to get the paymnets to follow the same formathave my payments 01/01/02-(sale)1-Reciept1...-payment1...Liteally (01/01/02-1-1-1...

Oh, and I forgot I want to be able to enter everything from my sale database. Did I say that this is a simple sales system?

Mark

Although this is quite simple to do it would take to long to explain.

If you want to contact me via email I would be more than happy to go through it with you.

Email me on [email protected]

This only addresses a portion of the original question. However, it seems the fundamental challenge is to create serial numbers that start over at 1 on each day (sub-serial numbers). I created a sample file showing one technique for doing this and posted it on my site:

http://www.tgparker.com/filemaker

The file is called SubSerial.zip

The idea is to create a self-relationship based on date.

Serial number is then an auto-entered calculation that first uses IsValid function to test if any previous records exist for that date. If not, field takes value 1, otherwise, field takes value Max(DateRelation::Serial)+1.

You can download the file and play with it. Hopefully, it will lead to a solution for the other parts of your question.