6 Replies Latest reply on Jun 10, 2011 10:55 AM by LeaMethe

    Insert a record in a serialized number field, and increment the following recors by +1

    LeaMethe

      Title

      Insert a record in a serialized number field, and increment the following recors by +1

      Post

      Is there such a thing as a serialized field that allows to "insert" a record in the numbered order and  and increment the following records by +1?
      I am trying to build a database for slides to be used in presentations. Ideally, the default sorting order would be dictated by a narrative order defined by me as I add new records. Therefore, no chronological or alpha-numerical order is convenient. I am currently attributing numbers by hand to each slide according to their place in the rather organic order, but it is very burdensome. I would like to simply create a new record where it belongs in the order I have deermined. Any ideas?

        • 1. Re: Insert a record in a serialized number field, and increment the following recors by +1
          philmodjunk

          The simplest is to not limit your self to integers while working out the sequence you want. To insert a record between records numbered "1" and "2", simply assign a value such as "1.5". A script can be devised to insert the record for you automatically. Once you have your order established, you can sort your records by this field (must be of type number, not text) and you can use the record number layout symbol @@ to show the "slide number" if you need to display it. You could also "clean up" this field into sequential numbers with no gaps by using Replace Field Contents from the records menu to update the found set of these records with a serial number--so that's another way to clean up the order after the fact.

          • 2. Re: Insert a record in a serialized number field, and increment the following recors by +1
            matthijsbeekman

            I'm using simple ordering and automatic numbering by FMP, but I have one problem:

            Once a record is created and later deleted, the numbering continues causing a gap in the numbers.

            For example, there's one record with the field field_number=1

            After adding a new record this new record gets field_number=2

            Deleting this record an again creating a new record gives a new record with field_number=3 while number there is off course no record with field_number=2 anymore.

            Does anyone know how to resolve such a problem?

            • 3. Re: Insert a record in a serialized number field, and increment the following recors by +1
              philmodjunk

              I don't think most of us would consider this a problem when working with serial number fields as they are used as primary keys and changing values after the fact (to close up such a gap in the sequence) would have catastrophic consequences for any related tables based on this field.

              I wouldn't try this at all if the field is used to link a record to other records via relationship.

              If this is not used for this purpose, you'd have to create your own delete record script that updates the numbers after the deletion to close up any gaps in the series. As I posted earlier, you can use replace field contents with the serial numbers option to clean up such a number sequence.

              Why is it important to eliminate a gap in your series?

              • 4. Re: Insert a record in a serialized number field, and increment the following recors by +1
                matthijsbeekman

                I want to use the serial numbers to access data in the previous record using GetNthRecord( .......; Get(RecordNumber)-1)

                Whenever a gap in the numbering occurs, this Recordnumber - 1 won't function as wanted.

                • 5. Re: Insert a record in a serialized number field, and increment the following recors by +1
                  philmodjunk

                  I think you need to revisit GetNthRecord. It does not rely on the serial number field at all--just the order of the records in the current found set. You do not need any serial number field at all for this function to work, but you do need to make sure your calculation field is unstored and to make sure that your records are correctly sorted to control which record this function accesses.

                  The Function get (RecordNumber) simply returns the location of the current record in the current found set. Sort the records into a different record or use a find to pull up a different group of records and Get ( RecordNumber ) can return a completely different value for the same record.

                  • 6. Re: Insert a record in a serialized number field, and increment the following recors by +1
                    LeaMethe

                    Thanks PhilModJunk.

                    Your suggestion reflects the solution I was already using when I asked for help. But as I said, it is quite burdensome and will quickly lead to a ton of decimals and a pain to assign a value to a given record, should 20 be produced and stored between 1 and 2. (1, 2, 1.5, 1.25, 1.2, 1.1, 1.01, 1.101, get the idea...) There has to be a better way, right?

                    I was thinking of an automation where a new record gets a number assigned by hand (the number of the record it should immediately follow +1) and then searches anything greater than that number and adds 1 to it. Will I create a monster trying that stuff?