9 Replies Latest reply on Feb 13, 2017 1:35 PM by JulioSandoval

    Continuing Serial Value




      I currently have Two Tables.


      Inspection Table --- Image Table


      Both tables have unique IDs .


      The image table has an ImageNum field that is serialized starting at 0001.

      I have a calculation that renames the image to our liking. the ImageNum is part of that naming scheme.


      When you create a new inspection, the ImageNum Serial is reset to 0001 using the

      Set Next Serial Value [ImageTable::ImageNum ; "0001" ]   <--- Yes I know, it is not best to put quotations on numbers, but FM would NOT let me place any zeros in front.


      What I can't figure out is how to tell that serial number to continue if I need to go back into an existing record and insert more images.


      I have tried using the Set Next Serial Value with Max() function, but that just continues off of the current max Serial number.


      So if I create a new inspection record, I insert 3 pictures, and those are set to 0001, 0002, 0003 respectively.


      I can create a second inspection record and say I insert 4 pictures, the last picture will be 0004. Wonderful right?


      But if I go back to the first inspection record I created, and insert an image, the serial number is 0005 instead of 0004... ...


      So I have hit a wall. Granted, the pictures should/will be inserted before the record is completed, but just in case an image is forgotten and needs to be inserted, how can I continue the serial number properly?


      Thank you all.

        • 1. Re: Continuing Serial Value

          Set the serial number in the schema to only enter "on commit" instead of "on creation".


          It's a radio button in the manage field options, as seen here:


          • 2. Re: Continuing Serial Value

            Hi Mike,


            Thanks for your reply.


            I gave this a try, but it does not seem to place the ImgNum when the picture uploads...

            I set the Commit/Record Request script before the image is named, and it ignores my name settings and places "Photo" Date & Time.jpg

            No Idea why


            Below picture is On Commit option

            Screen Shot 2017-02-10 at 2.37.06 PM.png


            This is what it was before:


            Screen Shot 2017-02-10 at 2.38.49 PM.png



            These pictures are being inserted from my iPad.


            At the moment, my best option is still the On Creation setting.


            Thank you.

            • 3. Re: Continuing Serial Value

              Is this a hosted solution with multiple users adding inspections and images at the same time?


              If so, resetting the next serial value is not a good idea. It affects all users and can result both in duplicate values or completely unexpected values when one user resets the value while another user is adding image records.

              • 4. Re: Continuing Serial Value

                Yes, it is hosted.


                I can't really say if they will add inspections and images exactly at the same time.

                The reset of the serial number happens when an inspector creates a new record for said inspection.


                Phil what you are saying is;

                I start a new inspection, serial value is reset to 0001.

                If I am in the middle of taking picture 0005, and someone else starts a new inspection, my 0005 picture will then become 0001?


                If so, what is the best way to approach this scenario?


                The image name will be unique anyways. I just want the image number to always reset to 00001 and increment up by 1 from there when a new record is created.


                Thanks phil.

                • 5. Re: Continuing Serial Value

                  Don't use an auto-entered serial number. There are ways to count the number of records related to a given project and also ways to get the max number of all records related to project. Either way, that value plus 1 will give you the next value.


                  This works best if there never are more than one person taking pictures for a given inspection.

                  Note that counting the number of related records and getting the max value won't always return the same result if picture records are ever deleted.

                  • 6. Re: Continuing Serial Value

                    What do you suggest phil?


                    I have the Inspection Table, a Cross Reference Table (This has nothing but foreign keys) and the image table linked to the Inspection Table via Cross Reference Table.


                    So what you are saying is, it will be best for me to have a value start at 1, and then if a new image is taken, count the Max related records in the Image table, if that = 4, the next value is 5 and so on?


                    How would you write something like that?

                    • 7. Re: Continuing Serial Value

                      Phil I figured out the way to get it to increment by per Max relating records so it does not interfere with another inspector doing an inspection at the same time.


                      When a new record is created i have it set the ImageTable::ImgNum field to 0

                      Then when an image is taken and uploaded I use the Max() function

                      Max(ImageTable::ImgNum) + 1


                      This always increments properly no mater if i go back and upload new images.


                      Thanks Phil! Appreciate it!


                      Let me know if this is what you meant so I can mark the question as correct.


                      Thanks lots!

                      • 8. Re: Continuing Serial Value

                        That was one of the methods. If two inspectors add pictures for the same inspection record, it is quite possible to get two records with the same serial number as both might get the same result from the max function. If it's always only one inspector to an inspection, this should not be an issue. If it is a possibility, you may need to add a unique values validation to your field.

                        • 9. Re: Continuing Serial Value

                          Yes. One inspector per inspection record. And the ImgNum was just one piece of the whole image name puzzle.

                          So it will be unique.

                          It will have Inspection ID-InspectorInitials-ImgNum


                          So in the case two inspectors work on the same record, the the MAX() function will still increase the ImgNum normally but the images will have each inspectors initial therefore knowing who took what.


                          Thanks again!