Would love anybody's two cents: I'm attempting to create a serial number on record creation contingent on a self-referrential relationship.

I want the serial to be the numbered occurrence of records with a common match field. For example:

In the db 'Referrals', I have 83 records. In the field 'Agency', 10 of the records have "HRC" and 73 have "JM".

I want the _next_ record (depending on 'HRC' or 'JM' being entered in 'Agency') to auto-enter a serial number of 11 or 74. Or 1, if different letters are used.

I have a self-referential relationship using 'Agency' as the match-field, but I can't seem to get the function.

Thoughts? Many thanks, /c.

This should be pretty straight forward:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000121

--sd

C'mon, Søren - you know better than this:

http://www.fmforums.com/forum/showtopic.php?tid/191695/post/273995/#273995

Indeed, but I couldn't really tell if was a behind the scenes relational match, or? ... this approach serves only display purposes!

--sd

Hmmm. Thanks to you both. I'm investigating now.

+ The concern about non-unique serialization is valid (I was going to append the Agency Initials (HRC2, JM7), but would have "Under-the-hood" non-unique serials. But your solution may solve that ...

+ As for "Display Purposes" only, I do need more than just Summary viewability. Best, /c.

I don't get this approach. If the numbers are important, they should be unique - even if they are not used for relationships. If they're not important, why bother with them at all?

I was going to append the Agency Initials (HRC2, JM7)

And if two users are creating a new record for the same agency at the same time?