8 Replies Latest reply on Jan 8, 2013 11:48 AM by ChrisNixon

    Calculating Date + Max Record Number

    ChrisNixon

      Title

      Calculating Date + Max Record Number

      Post

           Hey guys, this is my first post in this forum so I apologize for my lack of knowledge when it comes to FM. Currently we are running FMServer Advanced 11 using single-machine setup, and FMPro Advanced 11 for developing/editing existing databases.

           My field is called "Case Number:" and currently it displays a hard-coded Year, and the current record count plus 1 (max_serial_no + 1). The field (zd_CaseNumber) is a Calculated Value and the calculation is as follows...

           zd_CaseNumber = "2013-" & If(IsEmpty(max_serial_no); 1; max_serial_no + 1)

           My problem lies in when a new year occurs, we need the database to auto update, and reset the max_serial_no to 0. For instance, when the New Year rolled over to 2013, the first record then created would be "2013-1", then "2013-2", etc.

           Can anyone assist in making this possible?

        • 1. Re: Calculating Date + Max Record Number
          philmodjunk

               Please do not use such a field as your primary key in relationships.

               how do you produce the serial number now?

               If you are using an auto-entered serial number, you can use a script to reset the next serial value to 0. You can run this script manually every January, or you can set up a script that runs either daily or every time that the file is opened on a client machine that checks to see if it's a new year and if so resets the serial number.

          • 2. Re: Calculating Date + Max Record Number
            ChrisNixon

                 PhilModJunk: Thanks for your quick response...

                 I was able to get the Year portion of this resolved by using the following calculation...

                 Year ( Get ( CurrentDate ) ) & "-" & If(IsEmpty(max_serial_no); 1; max_serial_no + 1)

                 Now to answer your question, and proceed with the Script idea. Here are the two related calculations...

                 max_serial_no = Max(Entry | Entry_Constant::zd_SerialNumber)

                 zd_SerialNumber = max_serial_no + 1

                 Thanks again for your help as this is my first day ever using FMP

            • 3. Re: Calculating Date + Max Record Number
              philmodjunk

                   I recommend that you set up a field as an auto-entered serial number instead of Max_Serial_no. This is simpler, and if you have two or more users creating new case numbers at the same time, the scripted method that you are using can produce two records with the same case number.

              • 4. Re: Calculating Date + Max Record Number
                ChrisNixon

                     PhilModJunk: I think that is a great idea, had I been the one to original design this, it is what I would have done. But the customer prefers the way it is currently and requires that each year on the first it change to "Year-1", "Year-2", etc. So now my issue is getting the max_serial_no to reset to 1 on Jan 1 of each new year.

                • 5. Re: Calculating Date + Max Record Number
                  philmodjunk

                       What I am suggesting still does what the customer wants. You use an auto-entered serial number to generate the numeric portion of this value and then your calculation combines the year with the value from this field.

                       You just reset the "next serial value" for this field once a year.

                       And you can provide such a value to the customer without using it as a primary key for use in relationships. You can use a separate field set up as an auto-entered serial number that the user never even sees for that purpose.

                  • 6. Re: Calculating Date + Max Record Number
                    ChrisNixon

                         PhilModJunk: now that you say it like that, that is exactly what I am already doing. i am attaching a year to the beginning of the auto-entered serial number. The calculation then combines them for the displayed result. It is not the primary key.

                         My question is, how can i make that value reset once a year? Remember, I am a beginner with FMP.

                    • 7. Re: Calculating Date + Max Record Number
                      philmodjunk

                           This calculation is not correct for using with an auto-entered serial number:

                           Year ( Get ( CurrentDate ) ) & "-" & If(IsEmpty(max_serial_no); 1; max_serial_no + 1)

                           Auto-entered serial numbers increment automatically with each new record. There is no need to add 1 nor will the field ever be empty.

                           Add a number field, year to a table set up for this purpose. This can be a single record table just used for this purpose, or you may eventually find that you want to add more fields for other "preferences" type settings. I'll name table::Field as Settings::Year and we'll call your auto-entered serial number Cases::SerialNumber.

                           Then this script will reset the serial number if it is a new year:

                           Freeze Window
                           Go To Layout ["Settings" (Settings)]
                           Show All Records
                           Go to Record/Request/Page [First]
                           IF [Year ( Get ( CurrenDate )) > Settings::Year //It's a new year, reset the serial number]
                               Set Next Serial Value [Cases::SerialNumber ; 1 ]
                               Set Field [Settings::Year ; Year ( Get ( CurrentDate ) ) ]
                           End IF
                           Go To Layout [original layout]

                           Since you are hosting this from FileMaker Server, you can set up a server schedule to run this script once a day.

                           Note: When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                      • 8. Re: Calculating Date + Max Record Number
                        ChrisNixon

                             this worked well, when i tested, running the Script manually, BUT it screwed up the sorting of my records, instead of all the 2013 records being after the 2012 ones, it now goes from 2012-9 to 2013-1, 2013-2, etc before it ever goes back to 2012-10, the last valid 2012 record is 2012-2102, and after that should begin 2013-1, etc. it might be something i can resolve just by sorting differently, but i havn't a clue, any ideas?

                             thanks again for your time

                        UPDATE: Once I changed Sort Order from the OLD zd_CaseNumber to the new SerialNumber field, it worked perfectly. THANKS A TON for your help, I really appreciate it.