hi,

can someone help ?

I am trying to figure out a formula for a field!

I have a project number and a band number

they are related in a one to many relationship.

I want the band number to have the project number + 1

i.e. Project number 02.01

and the band number to be 02.01.1

then the database automatically make the next band number for the same project to be 02.01.2

how do I do this what

Use a self relationship based upon Project number to isolate the records for one project. You will need a field with the raw serial part of the band number (1.1.1 isn't a number!), we'll call it BandSerial (a number field). The relationship:

SelfByProject: ProjectNumber <--> ProjectNumber

The next BandSerial is Max(SelfByProject::BandSerial + 1)

This will be set with a scrpt. The Band Number field must be a text field.

BandNumber (calculation, text) = ProjectNumber & "." & Right("00" & NumberToText(BandSerial), 2)

I assume from your format that BandNumbers can't exceed 99.

-bd

Well, the formula is Max(clients::serial) + 1 - but that won't do you much good without the context.

To see how to use the formula in context (ie for a working model to pull apart), download the attached file, which is a password free demo.

BandSerial.zip