1 2 Previous Next 15 Replies Latest reply on Sep 2, 2011 2:53 PM by AnnetteSteinberg

    Serial Number revert after commit

    AnnetteSteinberg

      Title

      Serial Number revert after commit

      Post

       Hello,

      Is there a way to revert a serial number if a committed record is deleted?

      Sometimes a new record is created by error, so I need a way to have the recorded deleted and the serial number go back to the previous number.

      Thanks for the help.

        • 1. Re: Serial Number revert after commit
          philmodjunk

          But what if you delete a record in the middle of the series?

          In some situations, it makes for simpler auditing if you mark records like this "void" by changing a value in a field instead of deleting them.

          If you use a script to delete your record it is possible to include steps in the script to update the next serial value setting. If you have a copy of FileMaker Advanced, you can also set up a custom menu to run this same script if the user selects Delete record from the records menu or presses its keyboard shortcut. Such a script will need to compare the record's serial number value to the field's next serial value setting (there's a get function for that), before actually changing this setting.

          • 2. Re: Serial Number revert after commit
            AnnetteSteinberg

             

            But what if you delete a record in the middle of the series?   This wouldn't be done - in this db all calls for a potential job are logged (hence the serial no.)  If a job is canceled  - that's noted in a separate field.

            In some situations, it makes for simpler auditing if you mark records like this "void" by changing a value in a field instead of deleting them.  See note above.

            If you use a script to delete your record it is possible to include steps in the script to update the next serial value setting. If you have a copy of FileMaker Advanced, you can also set up a custom menu to run this same script if the user selects Delete record from the records menu or presses its keyboard shortcut. Such a script will need to compare the record's serial number value to the field's next serial value setting (there's a get function for that), before actually changing this setting. 

            I am using FM11/Advanced.  Can you provide me the script for the custom menu script.  Thank you.

            • 3. Re: Serial Number revert after commit
              philmodjunk

              Show Custom Dialog ["Are you sure you want to delete the record for" & YourTable::NameField ]
              If [ Get ( LastMessageChoice ) = 1 //OK was clicked]
                 If [ YourTable::SerialNumberField = ( GetNextSerialValue(fileName;fieldName) + 1 )
                    Set Next Serial Value [YourTable::SerialNumberField ; YourTable::SerialNumberField ]
                 End IF
                 Delete Record [no dialog]
              End If

              • 4. Re: Serial Number revert after commit
                AnnetteSteinberg

                Phil,

                I'm getting an error message in the third step of the script:  "The specified table cannot be found" referring to the JPMSVersion1 italicized below.  The JPMSVersion1 is the name of the file.  Any ideas?

                If [ YourTable::SerialNumberField = ( GetNextSerialValue(fileName;fieldName) + 1 )

                JPMS::kp_pa_number  = (GetNextSerialValue (JPMSVersion1 ; JPMS::kp_pa_number) + 1 )

                • 5. Re: Serial Number revert after commit
                  philmodjunk

                  Use:

                  JPMS::kp_pa_number  = (GetNextSerialValue ("JPMSVersion1" ; JPMS::kp_pa_number) + 1 )

                  Or

                  JPMS::kp_pa_number  = (GetNextSerialValue ( Get (filename) ; JPMS::kp_pa_number) + 1 )

                  The second option is the better way to go as it protects this script from failure should you rename the file at some point in the future.

                  • 6. Re: Serial Number revert after commit
                    AnnetteSteinberg

                     Hi Phil,

                    I used the second option as suggested, and it accepted the change.  I'm runnng the script on layoutexit.

                    The dialog box comes upand if i select "Yes"   the new record is deleted; if I select "Cancel" the new record remains, but if I delete the record, then select a new record, the serial number is still advancing.  Shouldn't the script at the end of the third line be a "-1".

                    thank you,

                     

                    • 7. Re: Serial Number revert after commit
                      philmodjunk

                      Say the record you intend to delete is numbered "2000". If so, the next serial value is 2001. To avoid a gap in the number series, you'd set next serial value setting back to 2000. Using  a -1, would set it back to 1999.

                      Please note the assumption here is that the record is already committed. If you are creating and then immediately deleting the record this may not be the case and adding a commit record step as the first line of this script might not be a bad idea.

                      • 8. Re: Serial Number revert after commit
                        AnnetteSteinberg

                         Hi Phil,

                        Got another issue.  How would I change the script to just check for the most recent serial number, and then complete the script?

                        If I run the original script via any of the layout script triggers, it wants to run the same script, even on committed records.

                        • 9. Re: Serial Number revert after commit
                          philmodjunk

                          Why do you want to delete records from a script performed by a script trigger?

                          • 10. Re: Serial Number revert after commit
                            AnnetteSteinberg

                            That's exactly my point.  I had it on a layout script trigger, because I didn't want someone to forget they had a new record without filling in some information about a job and maybe leaving the layout because they got interrupted.  Then we might have lots of new records with no information, and gaps.  And no one is allowed to delete records except admnistrators.  Maybe I should have a delete new record button?

                            • 11. Re: Serial Number revert after commit
                              philmodjunk

                              Yes, but why delete it?

                              Why not instead, have your trigger perform a script to check to see if the record is incomplete and then use a custom dialog to notify the user that the record is incomplete and ask if it should be deleted?

                              And why are gaps in the serial number sequence a problem?

                              • 12. Re: Serial Number revert after commit
                                AnnetteSteinberg

                                 I see, so I could have the on layout script trigger check to see if the two fields we need to have filled in (caller name and caller telephone number) are indeed filled in.  If they are, a user can go on their merry way.  If not they have the choice to delete the new record.

                                So to check to see if the caller name and caller telephone number are filled in would be a Is Valid, or IsNotEmpty script?

                                Gaps in the serial number are a problem, because it's acting as the Job No. as well.

                                • 13. Re: Serial Number revert after commit
                                  philmodjunk

                                  And gaps in the Job No cause a problem because? (Not trying start an argument, it's just that serial ID's frequently are not perfect sequences and still work just fine in a database as long as there are no duplicate values. In many cases, this field is kept completely hidden from the user...)

                                  Not Isempty (table::field)  is a test you can use to see if a field is or is not empty.

                                  • 14. Re: Serial Number revert after commit
                                    philmodjunk

                                    Using a serial number from a table of messages to generate job numbers seems to do things backwards from my perspective. Couldn't you have many messages for one Job? Seems like a Job Number should be generated in the other table and then a field in your messages table can store a copy of this value to link any number of phone messages to a given record in the other table.

                                    1 2 Previous Next