One way to do this is to use a self-joining relationship based on the prefix (in your case, the prefix is the same as the account name). Then, tack the serial number on the end based on the Count function in an auto-enter calculation.
Example: Get ( AccountName ) & Case ( Count ( SelfJoin::keyField ) > 1 ; "-" & Count ( SelfJoin::keyField ) - 1 )
Probably needs to be cleaned up, and I haven't tested it, but it's one idea.
Thanks Mike. It gets me a lot closer, as it handles the first case, but the existing -1, -2 instances are not regarded and so I will have to try a few more pieces to accomodate that. The self join was something I consdered, but I missed how to put it into a calc.
Here are the pieces I went with to implement your suggestion:
Since I did not have a host name only field, I added a Calc field:
Host_c = Left ( Hostname ; Position ( Hostname ; "." ; 1 ; 1 ) -1 )
I added a relationship of AccountID and Host_c
I inserted this in the HostName field a-e calc:
AccountID & Case ( Count (UserHostCheck::AccountID) > 1; "-" & Count (UserHostCheck::AccountID) - 1 ) & HostSuffix
BTW, I meant field with an account name versus the FileMaker entity account name.
One thing I've done in the past is use a Sequential ID field that holds the sequential ID number (1, 2, 3, etc.) for that record, then splicing them together. One prefix field (on which the self-join is based), one for the sequential ID, and then a calc field for the final result. That accounts for previous values.
However, this sometimes can be a little risky if the system has a significant number of users. It can be vulnerable to duplicates. That may not be a problem here, though, since the prefix is the account name.
Not a perfect system, but can work in the right circumstances.
With a few tries, I am seeing your initial suggestion gets what I need. I won't use the sequence ID at this point.
Take a look at the built in function called "SerialIncrement". I don't think it will do exactly what you are looking for, but you never know.
It does help setting an increment, but the relationship part also seems to be necessary. For just changing a record it fits best, but for chainging relative to other values in the table it is not a direct answer. I do appreciate bringing it to my attention as another way to change a field besides in its definition.