Multiple Serial Numbers - Maybe??

[jamiebeedy]

What I'm attempting to create a unique number for each record based on another field. That is, I have 2 fields, "type" and "ID". Type has 2 choices "A" & "B" the ID will get a number based the the choice made in "Type". So when a new record is created, if "A" is chosen, ID will be Filled with 1, 2, 3, etc. If "B" is chosen, It will be filled with 1, 2, 3, etc. I've gotten this to work using the count function, but, if an earlier record is deleted, then a number is repeated, due to the fact that it's just counting the number of records containing "A" or "B".

Any suggestions?? Also, I'm using FMP V6 would I be better off upgrading to V8.5 to resolve this??

TIA

Jamie

[Fitch]

My first suggestion would be to not do this. Or at least, don't use it as a primary key.

However I have a policy of always answering the question that was asked, so: try using the Max function rather than the Count. E.g.

Max(all related::A) + 1

Another option might be a one-record Preference table (a file in FM6) with A and B number fields that you increment when new records are created, keeping track of the next numbers to use. This would require controlling creation of new records with a script.

[jamiebeedy]

Fitch,

I've followed your first bit of advice - I'm not using this as my primary key. AS far as everything else goes, I ended up using the "MAX" Function and that doing exactly what I want to do.

Thanks for the advice.

Jamie