5 Replies Latest reply on Jun 21, 2011 1:14 PM by philmodjunk

    Numbering Records Without Serial

    MatthewMackay

      Title

      Numbering Records Without Serial

      Post

      I'm trying to write a script that numbers records in the order they are created. I realise auto-enter serial numbers would be the easiest way but I want the first child record created within the current parent record to always be 1.

      I want to allow the user to edit this value as well if required.

      I've attached a screenshot to help explain.

      Thanks

      Screen_shot_2011-06-20_at_23.55.25.png

        • 1. Re: Numbering Records Without Serial
          philmodjunk

          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.

          • 2. Re: Numbering Records Without Serial
            MatthewMackay

            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.

            • 3. Re: Numbering Records Without Serial
              philmodjunk

              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.

              • 4. Re: Numbering Records Without Serial
                MatthewMackay

                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?

                • 5. Re: Numbering Records Without Serial
                  philmodjunk

                  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.