Fieldvalue + auto-enter A, B or C

[jamesjames]

Hi.

Having some more thought and need a hint in what direction to go.

I have two fields:

Field1= 1000- (or similar)

Field2= a letter lik A, B, or C and so forth

The result should be

CalcV = 1000-A, 1000-B or 1000-C

I have not decided that it has to be like this, I only need to find a solution.

Question: How do I get fm to check and auto-enter a letter following the alphabet? I only need 5-10 letters so it can be done manually. Basicly if 1000-A exist, next should be 1000-B and so forth.

many thanks for any tips.

[comment]

Perhaps you should explain what this is about - I have a hunch you should be using a related table here instead of trying to manage multiple series in the same table.

[jamesjames]

HI.

Its so difficult to explain things in text. But i will give it a go. Sorry if I was to unclear in my previous post.

So, we have a Building, and in this building we have lifts.

Every house has a uniqe number, ex, 100001

Every lift has a uniqe letter (in the building), A, or B och C and so on.

The house is in one table. The lifts in another table and they have a relationship thou the unique house number. I can use my relationship to check if the letter is already taken, but I would like FM to generate the letter automatically so the users dont need to keep track on what letter it should be.

Basically I want to do is automatically generate a letter in the lift table and make sure its unique in combination with that particular FK building number.

Was I clear enought. Let me know otherwise.

Edited August 19, 2011 by jamesjames

[comment]

I see. You should work this out with ordinal numbers first, then translate them to letters. However, the problem is more complex than it might appear - see:

http://fmforums.com/...a-child-record/

To translate an ordinal number into a letter, use:

Middle ( "ABCDEFG ... " ; number ; 1 )