Serial # by calculation question

[Christi]

I'm attempting to make a template table with certain "housekeeping" type fields including my primary key field which will always be a serial #.

I would like this serial # to be done with a calculation so it will grab the first three letters of the LayoutName and then I want it to include a number starting with 1 but including six zeros before the 1. As the serial # grows, there will be less and less zeros before the # (ex, INV0000124, INV0001335) This is what I have so far:

SerialIncrement ( Left (Get ( LayoutName );3); 1 )

I don't know how to write this calculation to force the # portion of the serial # to 7-digits. Can anyone help me out?

Thanks!

Christi

Edited June 30, 2006 by Guest

[John Mark Osborne]

You don't need a calculation. Just enter the letters and leading zeros into the Next Value option for the serial number. For example:

INV0000001

FileMaker will increment the numbers but leave the letters alone. The leading zeros will be removed only when more digits are needed. For example:

INV0000010

[Christi]

The reason I want it as a calculation is because I'm trying to set up a template table (or layout) with several housekeeping fields in it like creationdate, modificationdate, and even a serial #. So once I name the new layout (INVOICES, CUSTOMERS, JOBS, ETC), the serial # field will automatically go and grab the first three letters of that layout and I don't have to go in and change that serial # field for every layout. So this template layout will be part of all future solutions I design and it will automatically have the primary key's serial # ready to go and WILL update if I do, God-forbid, change my layout name.

Does that make sense?

Christi

[John Mark Osborne]

It makes sense but I wouldn't recommend it. Don't use a primary key that is created by anything other than a serial number unless it's for a non-structural relationship. If you want to modify your serial, do it in another field. Then, you can use a simple calculation:

Left(Get(LayoutName); 3) & Right("0000000" & SerialNumber; 7)