It's easy to encounter glitches with this as you probably do not have a valid link to the related records when the record is first created. In most cases, you have to enter a value that makes the relationship valid before this calculation can find the related value to which you want to add one. It can also result in duplicates if you have several users all doing this at the same time.
Why do you need such a sequence? If you just want the records to sort into the order in which they are created, that's their unsorted order--You can get this order anytime you specify "unsort" in the sort record dialog. You can also just use a serial number field here to maintain their original creation order. I'm basing these last two statements on the names of your field and table occurrence--there may be a less obvious reason for setting up this numbering approach.
i am very confused. the absence of a valid relationship seems like a plausible explanation for why such a calculation would fail to evaluate correctly in general, but that doesn't explain why it works when the "do not evaluate..." option is checked and doesn't work when it is unchecked (for situations that have existing realted records)... does it? and similarly in the reverse when no realted records are present; works fine unchecked, but incorrectly when checked.
what is further confusing, is that there is a separate relationship to obtain the highest current value of SortOder than the relationship which is used for the portal which displays the records so even if the newly created record does not have a valid relationship yet, it is not required to obtain the highest SortOrder. And even if the newly created record was "established" (i don't know the correct term) and included in the relationship, it would not affect the result since it's SortOrder field isempy to start with and would therefore not be the first record returned by the relationship which delivers the highest sort order since the empty field would be the lowest (i think?).
anyway, the reason that i am trying to do this is because this particular portal gives the user the ability to custom sort the phone numbers based on the value in the PhoneNumber::SortOrder. it used to be a straight forward edit box, but now has drag and drop functionality (but i don't think that is related as i have disabled the fields and script triggers required for drag and drop and the same strange behavior persists).
I have also tried evaluating the calculation from quite a few combinations of contexts. i have tried setting up the relationship that returns the highest value of SortORder in various ways but still no luck.
Any other thoughts about fixing this or other ways to have this highest value plus 1 entered? it occurred to me just to auto-enter a very high serial value like 100 (since there would very rarely be more than 100 phone numbers for a given contact... even 10 is getting up there) which would put the new record at the bottom of the list... but i spent so much time trying to get this to work, i would like to learn why exactly it won't. alas...
I haven't tried to parse out all the possible ramifications here as you haven't described how the relationship for the portal is set up nor how the relationship for contacts_PHONENUMBERS_HighestSort is defined. If I knew that, I could try to replicate the issue here.
Without those details, it's difficult to say why it doesn't work for you. Changing the records present in the current found set shouldn't have any effect on this at all as far as I can tell here.
I still don't see the purpose for entering the specific series of numbers here that you've specified. It would appear to me that you could just auto-enter a serial number and get the same results as the records would still sort in the same order--and this will work identically for you regardless of found set, sort order and the number of users doing this on a hosted file...
I also went back and looked at the original expression. Exactly what did you type into the auto-enter calculation box?
SortOrder = contacts_PHONENUMBERS_HighestSort::SortOrder_PhoneNumbers + 1
contacts_PHONENUMBERS_HighestSort::SortOrder_PhoneNumbers + 1
The syntax of the first expression is incorrect as it uses an = operator to compare two values, returning true (1) or false (0). I think you have the second based on your described results but want to be sure here.
of course... i will try to explain more about the relationships.
so for the phone number portal on the contacts layout the relationship is set up such that: Contacts::_kp_Contact_ID "=" PhoneNumbers::_kf_Contact_ID and it is set up to allow creation of new records in the PhoneNumbers table. this relationship is sorted by the field PhoneNumbers::SortOrder_PhoneNumbers in ascending order. this is the relationship i call "contacts_PHONENUMBERS".
There is the second relationship between Contacts and PhoneNumbers such that: Contacts::_kp_Contact_ID "=" PhoneNumbers::_kf_Contact_ID but does not allow for the creation of new records in either table and is sorted by the same field as in the other relationship (PhoneNumbers::SortOrder_PhoneNumbers), but is sort in descending order such that the relationship returns the highest value for PhoneNumbers::SortOrder_PhoneNumbers. this is the relationship i call "contacts_PHONENUMBERS_HighestSort".
in PhoneNumbers i have the field PhoneNumbers::SortOrder_PhoneNumbers with an auto-enter calculation specified like this: contacts_PHONENUMBERS_HighestSort::SortOrder_PhoneNumbers + 1 (i just typed this into the auto-enter calculation box... and not the "SortOrder =" part)
currenty, this is being evaluated from the context of contacts_PHONENUMBERS using the table occurrence contacts_PHONENUMBERS_HighestSort. and, this works with the exception of the conditions previously noted. it also seems to work (with the same eceptions) from a variety of other contexts and tables. for example, i also set up the relationships as one single anchor bouy chain from CONTACTS to PHONENUMBERS (by ID) to PHONENUMBERS_HighestSort (by ID again with descending sort). Which also works but encounters the same problems.
in the end (and at the beginning), you are (were) right... i could use serial numbers and get this to function correctly (which is what i think i will do for now). however, there are 2 reasons (perhaps not all that relevant or sane) that i wanted to use this method. for one, it would keep the sort order field in "order", meanign that if the SortOrder_PhoneNumbers value was 7, for example, i would know that it was the 7th record in a list of at least 7 related phone numbers whereas using serial numbers i would not know where in the list (if there even were more than one related record) a sort order value of 001439, for example, would be. there was an elegance to it that i liked. yes, of course, i can get this information other ways assuming i ever need it. but mainly, i am still very interested in figuring out why this does not work just for the sake of learning more aboput filemaker. to that end i am happy to provide any other information you need but i also understand if it is just too complicated to troubleshoot or too much of a nuisance to try to figure it out. i am grateful for the help you have already provided... thanks!
I've messed with this set up before. I'm not a big fan of this approach due to it's limitations--particularly when you might have several users doing this at the same time--which could generate two records with the same sort number. You can use get (RecordNumber) or @@ as layout text to display the sequence as a strict numerical sequence.
I replicated your relationships and tables and set up a portal where I can create records via the relationship and see what sort order number get's added when the record is created. Once I cleared the "Do not evaluate if all referenced fields are empty" check box, I could duplicate what you are reporting. I don't have access to source code here, so can only speculate from observed behavior.
When you start to enter data, a new record is created, but is not yet committed (saved back to the table). It appears that when "do not replace" is enabled, it evaluates sooner in this process and can't access the related record. It's as though it evaluates before the matching value from _kp_Contact_ID is auto-entered to establish valid link to the HighestSerial table occurrence where clearing this value seems to delay the point at which it evaluates. That's strictly speculation based on what I see. A FileMaker inc. tech might have a different explanation. Even with the option enabled, I'm only getting the correct value if I trigger a commit of the Layout's contact record after each new portal record is added. If I click directly into the next row and start entering data, the commit of the contact record doesn't happen and you get the same sort order number as the previous portal record.
If I write a single line script: Commit Record and use an OnObjectExit script trigger to perfom this script each time the phone number field is exited, then the system consistently computes the next number in the series. As I've said before, this is not guaranteed to work when you have two or more users attempting to do this on a hosted database at the same time. (Though it would seem unusual that two users would be attempting to edit phone numbers for the same contact at the same time and you'd get some record locking error warnings, I can't be sure this will work in all cases.)
thanks for testing that out... i was getting worried that it was only me! and thanks for the explanation. i also observed that behavior where if you didn't commit the record it wouldn't "see" it and therefore would result in a duplicate sort value on the next one. It seems even with the commit record step this method will not function correctly for the first record (which is key to the successful functioning of the rest!). or else, if it is configured to correctly enter the first record, it fails on all the others.
i had reported this as a suspected bug prior to getting any of your responses but i have not heard anything back yet on that front. for now i will resign myself to failure at this attempt and use the serial number method. i modified the drag and drop sorting so that after any drag and drop operation it re-initializes the sort order values in order starting at "1". so in the end... it is doing what i need. thanks for your perseverance!
It worked consistently for me for the first value on through the last--just make sure you clear the "do not evaluate if all referenced fields are empty" check box in the specify calculation dialog.
oh my god... i just discovered something! i was going in to change from suto-enter calculation to serial number and without really paying attention i checked the "data" box acidentally. in doing so, i noticed that it did not uncheck the auto-enter calculation. i put "0" in the data box and i left the auto-enter calcualtion as it was. when i created a new phone record not surprisingly, it entered a "0" in SortOrder. I went back and unchecked "do not replace existing value" and, to my surprise, it entered the correct SortOrder value from the calculation. it appears that the auto-enter calculation overrides the data value. so i tried it with no existing records and unchecking "do not evaluate..." and to my surprise and delight... IT WORKED CORRECTLY and continued to work correctly once there were existing records and 'do not evaluate..." was unchecked. strange but true! and just when i had given up! victory from the jaws of defeat! and without your help, and a fortunate accident, it wouldn't have happened. so thank you again!!
oh no!!! i just saw your post... hmmm. that is strange. i thought from your previous post that you were getting the same unexpected behavior? if i clear the "do not evaluate..." the only value i ever get is a "1" regardless of whether there is already a record present or not...? strange. and just when i thought the mystery was solved. is there a way i can send you a copy of the file i have (i compressed it down as small as i could to about 4-5mb)?
In the grand scheme of things, "Do not replace existing values" is not a major issue once you can get the correct value auto-entered when the record is created. This expression refers to a related record and updates to a related table do not trigger updates of an auto-enter calc, even if this value is cleared.
I didn't need to auto-enter a zero to get this to work on my system--just need to clear the "do not evaluate if all referenced fields are empty" check box. (For the first portal record, only a null value is returned from the highest sort relationship, so you have to clear this option or the field stays blank each time.)
Not sure what is different between your file and mine here.
ahhh... ok. more insight. using the data field had nothing to do with it. it was the change in status of the "do not replace existing values" checkbox. if this is checked, then you get the strange behavior i was getting where every value is a "1". but, like you say, if it is unchecked, and "do not evaluate..." is also unchecked, it works as desired (although not quite as expected). you would expect it to work even if "do not replace..." was checked because there is no value there to begin with? so there still seems to be some unexpected behavior. but, like you say, it is not a big deal to uncheck "do not replace...". i am learning.... slowly!