Since this is a portal, the expression: Max ( portalTable::Order ) would return 5 in the above example and Max ( PortalTable::Order ) + 1 would return 6, the next record in the sequence. (Be careful though if this is a database that is shared over a network and it's possible to have two people editing this exact same list of rooms at the same time. In those cases, it's possible for different users to get a "room # 6" at the same time and thus get two related records with duplicate order values.)
If you just need the number for display purposes, it get's even simpler as you can dispense with any field at all and just add the layout symbol for record number: @@ to your portal in place of this order field.
The reason for the numbers is to order the way the rooms show up in the report. If the number field is left blank, the rooms don't show at all.
Given the above example, ideally the user would select 'Hallway' from the drop down list of rooms, and a number '1' would automatically be entered in to the number field.
If required the user should be able to changed this number depending on the order.
You can use a normal auto-entered serial number to make sure that the rooms show up in the correct order, but then use other techniques to display the number as the sequence you show here. For a just the records shown in your screen shot, the @@ symbol will suffice. If this report is a summary report where you have more than one such group of rooms numbered sequentially, you can use the following summary field to report a "sequence number":
Define a summary field to compute the "count of" some never empty field such as the serial number field I'm suggesting here.
Set this up as a running count set to restart the count when the records are "grouped by" whatever field you sort on to group these room records in your report.
The number is never required to be shown on the report. It's only there to allow the user to manupilate the order in which the rooms appear on the report.
In most cases they'll just create the rooms in the correct order but on some occasions they'll need re ordered. I guess what I'm looking for is the auto enter serial option but always to start from 1 in every new parent record. Is that possible?
Ok, That's the approach I was trying to avoid here as it can be problematic in cases where the database is hosted over a network and two different users try to add a new room record at the same time. FileMaker's built in auto-entered serial number can assign unique serial numbers where our own, "home grown" version may generate duplicate numbers in such a case if we are not careful.
One way is to define a relationship to a second table occurrence of your rooms table matching by the common foreignkey value used in your portal relationship.
Rooms::ForeignKey = RoomsSameParent::ForeignKey
RoomsSameParent is a new table occurrence of Rooms created by selecting Rooms and clicking the button with two green plus signs.
Now enter this auto enter calculation for the sequence field: Max ( RoomsSameParent::sequence ) + 1
and clear the Do not replace existing values... option
Then you need a script trigger to make sure this calculation evaluates correctly:
Use this one line script: Commit Record with the Portal's OnObjectExit event.
If you want to avoid the possibility of getting two Room records with the same sequence number, add another field to the rooms table with this auto-enter calculation: ForeignKey & " " & Sequence and set a unique values validation rule on it.