8 Replies Latest reply on Apr 17, 2013 11:52 AM by philmodjunk

    Help with Unique Record IDs and the Max Function

    JeffBloxham

      Title

      Help with Unique Record IDs and the Max Function

      Post

           Hello all,

           I am being tasked with updating a piece of project management software running on a old filemaker version to the lates and greatest and incorporatin the new capabilities of the software.  I am really new to FM so I am learning as I go.  I have one common function in the old software that I am having issues recreating.

           Our software uses many tables to track things and most have a relationship with a project number or a specific contact.  Our old software would assigne a unique ID to a record by using the "Project Number" + Text + "Sequencial Numbers".  An example would be if you had a table to track issues on a job, when you created a new issue you would enter the job number and it would cread a uniqe ID with the value "Project Number-Issue-(next number in sequence using the MAX command by job number)".  The programmin interface between the 2 versions of FM is so different now, I am having issues copying over this function.

            

           Any help or ideas?

        • 1. Re: Help with Unique Record IDs and the Max Function
          philmodjunk

               Not sure exactly which function you need to "copy over", but I strongly recommend that you take this opportunity to NOT use this method for linking records in relationships. Use a simple serial number field defined in your Projects table to link to a number field in related tables. This "calculated ID" can easily result in duplicate ID numbers--particularly in the case where you may have more than one person generating new records at the same time.

               You can keep such calculations in place, they work exactly the same as they did in your older version, but don't use them as match fields in a relationship.

          • 2. Re: Help with Unique Record IDs and the Max Function
            JeffBloxham

                 Every post on trying to resolve a numbering issue like this has the same responce as yours.  Thank you but this field is not required for any calculation, it is a contractual requirment to have all items numbered sequentiually by project so I can't use a random number.  The original program has been working for years now.

                 The expression I am trying to copy from the old file to the new file is:

                  

                 Max(RFI to RFI by Job::DOC Number) + 1

                  

                 I get an error when trying to save.  I believe I am renaming everything correctly in the new file, but I need help with the use of this function.  The documentation of this use is lacking online.

            • 3. Re: Help with Unique Record IDs and the Max Function
              philmodjunk

                   The posts have that warning for good reason. You can use this identification system to satisfy user requirements, just don't use it as a match field in a relationship.

                   The expression you are posting works the same in FileMaker 12 as it does in earlier versions.

                   If there is an error message, then something either has not been renamed correctly or you are lacking a relationship or haven't specified the correct context.

                   What exact error message do you get?

              • 4. Re: Help with Unique Record IDs and the Max Function
                JeffBloxham

                     It says"the specified field cannot be found and highlights RFI to RFI by Job::DOC Number.

                      

                How would you set this up.  The desired function is

                - Create a new record in a table (say this table tracks issues on jobs)

                - Enter the Job Number (This field is linked to a Jobs table by Job Number)

                - Have a field auto number the next value in series associated with a paricular job. 1,2,3,4, etc. with the new value being +1 in sequence.

                      

                      

                      

                • 5. Re: Help with Unique Record IDs and the Max Function
                  philmodjunk

                       Try selecting that field by bringing it up in the list of fields in the top left box that lists fields and add it to your calculation by double clicking it. That will add it to your expression with perfect syntax. If you can't find the field there, you'll know why you are getting this error message.

                       For how I would do it, take a look at this demo file: https://www.dropbox.com/s/ggjy88cgkescopy/CategorySerialNos.fp7

                  • 6. Re: Help with Unique Record IDs and the Max Function
                    JeffBloxham

                         That works if I turn off uniqe value.  Thank s mate.  Will study it to figure out its inner workings.

                    • 7. Re: Help with Unique Record IDs and the Max Function
                      JeffBloxham

                           Thank you very much for getting me started and on my way.  I have made a lot of progress.

                           I still can't figure out one thing with this exampe.  You added a table called OrdersSameCustomer that I just can't wrap my head around.  You delete it, and the function does not work, but I can't get in my mind why it is there.  It is just linking to itself.  Any clarity here for me?

                      • 8. Re: Help with Unique Record IDs and the Max Function
                        philmodjunk

                             It's not a table, it's a Tutorial: What are Table Occurrences?. It's used in a self join to pull up all records for a given customer so that the maximum value for that customer can be extracted.