Auto-Enter a Sort Field

[spankalee]

Hello FMForumers,

Got another (not-so?)tricky question for you:

Have a simple Category/Sub-category situation. Sub-category has a SortPosition field that I'd like to auto-enter. It seems simple enough, but I've run into some FM gotcha apparently.

The fields are basic:

Category:

ID

Name

SubCategory:

ID

CategoryID

Name

SortPosition

I'm trying to auto-enter the SortPosition with a calc like this:

Max (SubCategories 2::SortPosition) + 1

where SubCategories is a self related table on CategoryID

The user interface is a Category layout with a portal for SubCategories.

The problem that you experts have probably already guessed is that the calc Max() doesn't re-evaluate until you leave the portal, so the first new record has the right number, but all the rest entered get the same number. To have it work correctly you need to click out of the portal after each new SubCategory.

Well, I guess that describes it. Any known workarounds? Does it behave this way in FM8?

Thanks

[Genx]

You could script it i suppose, though i think the reason it may not be working as you expect is that it is in fact a selfjoin and you are currently modifying the record so its not commiting. Whether or not thats the case i dont know but still.. As for the script, you could execute it after leaving the portal using onevent plug or just hitting a button:

Goto Portal Row [First]

SetField [sometable::someglobal ; 1]

Loop

Go to Poratl Row[by calc ; sometable::someglobal]

SetField [Field to be set ; Max (SubCategories 2::SortPosition) + 1 ]

Go to Portal Row[next ; exit after last]

SetField [sometable::someglobal ; get(portalrownumber)]

Commit Record

EndLoop

Now that i think about it though, is your field SubCategories2::SortPostion a number field?

Reviewing my post, its a bit random, but nevertheless

you could also try the serial increment function instead of using + 1 ... serialincrement(max(yourfield);1)

~Genx

[Genx]

oh right, lol, one more obvious way of doing it would be to not allow creation of records through the portal and script it through another relationship specifically for this...

~Genx