Sequence Check

Krisraychris used Ask the Experts™

I would like ot have a script that will check the sequence of events in a portal.
The numbers must always be consequtive Starting from 1 and followed by 2 , 3 ,4 etc .I would prefer not to set a limit to the number of sequence steps.
The portal is sorted by Event sequence and the user will need to change the order frequently so the script must reset and and alert if they have set as in the attached example..Number 4 , twice.Or better still that the system will not allow it.(The User assigns the Event sequence Number)
Look forward to any ideas on this.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President, Dedication Technologies, Inc.

Probably the most flexible way to handle this is with a Script and Script Trigger for the Event Sequence field set to trigger OnObjectModify. The script could disallow the change if the Event Sequence is not unique and then use the  "Revert" script step to revert back to the original Event Sequence number. However, I would consider simply having a loop that renumbers the Events each time one of the number is changed.

If you have a secondary sort of the portal that gives the correct sort order based in whatever fields you want to include then the renumbering could simply start at the top and proceed down eliminating the duplicate number. Alternately the Script could look first to see if a duplicate of the number exists and if it does, it could then simply add 0.1 to the number which would 1) keep the records in order and 2) let the user know that they made a duplicate (you can also use conditional formatting to change the color of the field if there is a duplicate).

The key thing would be how you detect a duplicate and there are, as with most things in FileMaker, a couple of ways to do it. The old way might have been to use a Self-Join relationship to detect duplicate values, but the simpler way would be the use a combination of the FilterValues( text to filter ; filter values ) function with the ValueCount() function as follows:

FilterValues ( List( Product::Event Sequence ) ; Product::Event Sequence )

What this calculation does is use FilterValues() to see if the newly entered Entry Sequence exists in the current list of all Entry Sequence values. It should exist, but only once. If it exists more than once, there will be multiple values in the FilterValues result and you can detect that by using ValueCount( )

So here is the script that I setup for the script trigger:

   Exit Script If [  ValueCount( FilterValues ( List( Product::Event Sequence ) ; Product::Event Sequence ) ) = 1 ]
   Set Field [ Product::Event Sequence ; Product::Event Sequence  + 0.1 ]
End Loop

If the new Event Sequence value is unique - only one occurrence, then the loop exits immediately. If it occurs more than once, the script increments the value by 0.1 to keep it unique and in order after the existing original value. If that isn't unique, the loop continues until it finds a number that is unique. if you have large number of Event records, you may want to put an additional limit on the loop such as the Max Value of Event Sequence + 0.1.

Add the above script as an OnObjectModify script trigger to the Event Sequence field and it should work. If you want to then renumber, you can then add a second loop sequence that goes to the first portal row, sets it to the portal row number and loops until it get's to the end.


Thank you for the advice and solution.I have been busy with other apsects of the application and set up the sequencing  of events today.It looks like it is working and may need a few monor adjustments to suit the exsisting data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial