I need help with record number field

[BeckhamTX]

I am new to developing FM db at work, and have a project I am currently working on that I can not figure out how to make something work.

One field will be record number, and be unique to the record.

Another two fields will have the (1) building code or (2) location code entered.

Depending on the building or location code, the record number should contain the code and a sequential number after it.

For example, BLDG0001 then BLD0002 then HOUSE0001 BLDG0003

I know how to concatenate two fields (code and number) but I do not know how to make the number field work so that each code has a 0001, 0002, .... As of now I have the number field set to auto enter by incrementing by 1 each time, but this would mean that each building/location code would not have sequential numbers after it.

--There will probably be about 40+ different building and location codes....

Thank you to whoever can offer me any suggestions.

[BobWeaver]

You need two fields. One that contains the prefix "BLDG", "HOUSE" etc., and one that contains the numeric part of the record number. For this discussion, I will call them Prefix and SerialNo. Your Location code may already be the prefix part, but it wasn't totally clear from your description.

Create a selfjoin relationship on the Prefix field and then you can create independent serial numbers using this kind of calculation field:

Prefix&Right("0000"&Case(IsEmpty(Selfjoin::SerialNo),1,Max(SelfJoin::SerialNo)+1),4)

[BeckhamTX]

I got a little confised in setting it up, hope you dont mine me asking something else.....Ive played around with this for a while and I keep on getting PREFIX0001 the number value never increases. Another time I got it to increment by one for a certain prefix, but when i used a new prefix it did not start back at one.

So how exactly do I set up the self join...I know where to go to set it up but am not sure which field to select on the left column to match with a value (the selected field on the right).

I think that is the part I am messing up on. I put the calculation you gave me in the definition for the record number field (concatanation of prefix and number)

I hope this is not all confusing. Tank for your help if you can figure out what it is im trying to say.

[BobWeaver]

It sounds like you put prefix in quotation marks.

To set up the relationship, select the prefix field on both the right and left hand side in the setup dialog. That's all there is to it.

One thing I didn't mention is that you can't just set up the Record number field to be a regular calculated field. You have to make it a regular text field and then set it with a "Set Field" step in a script. You may also be able to use the auto-enter calculation option to set it, but this tends to be a bit quirky, and you will have to modify the calculation a bit to do it. When you create a new record, you may have to perform an "Exit Record" step before the "Set Field" calculation step to ensure that the relationship is valid and that you get the correct result.

[BobWeaver]

Oops, I didn't give very accurate information. I've attached a sample file that demonstrates how it's done.

SeparateSN.fp5.zip

[BeckhamTX]

OK, Ive got most of it done now, but I am having one last issue. When I set up the calculation for my field equivalent to your MaxSN, I get a prompt that it can not be done because it is a circular definition. I went back to look at your example I downloaded, and When I open up the calculation, make no changes to your file, and then click done I get the same prompt on your file....BUT your file does work in increasing the record number for each prefix....Im just wondering how I get around this circular definition prompt...

thankx for all your help, and im sure this will be the last issue I have with this, since I seem to have everything matching your example and this is the only part I am left with.

[BobWeaver]

The trick is in the order in which you define the fields. Create the auto-enter text field first, but don't put in the auto-enter calculation. Then, create the calculated field with the formula given. Finally, go back to the auto-enter field and put in the auto-enter formula.

You can also get around the situation by using the GetField() function to break the circular definition error, but I don't usually bother, because it just makes the calculation longer, and it isn't available if you're using a version of FM older than 5.5.