4 Replies Latest reply on Jan 27, 2011 10:05 AM by raybaudi

    Auto Fill Calculation - Last Entered Record

    WilliamStone

      Title

      Auto Fill Calculation - Last Entered Record

      Post

      Hi

      One of the fields in my database contains a unique ‘Job Number’ that is entered manually. So that I know what job number to enter in my new record I have created a field that contains the previous job number. My problem is that when I sort the records the previous job number displayed is not the last entered causing confusion when entering the latest job number.

      I have used the following calculation:

      GetNthRecord ( Job Number ; Get ( RecordNumber ) - 1 )

      What calculation would I need to use to populate the field with the last entered job number and not data from the previous sorted record?

      Hope this makes sense.

        • 1. Re: Auto Fill Calculation - Last Entered Record
          Sorbsbuster

          If you are manually managing the list of Job Numbers used, and simply entering the next one every time you create a Job Record, why not let Filemaker look after that for you, and let it auto-enter a serial number?

          • 2. Re: Auto Fill Calculation - Last Entered Record
            WilliamStone

            Thanks Sorbsbuster.

            Unfortunately I need the database to fit in with the way I create job numbers on paper already which is the date backwards followed by a sequential number as in the following example:

            110127-01

            I am a photographer and need to use this method so that the folders containing all of my images are recognised and organised in order by the computer. I would love for FileMaker to create this number automatically but I think because of the changing nature of the job number and the variation in days of the month it would be impossible (or certainly overly complicated) to set this up.

            Do you happen to know of a calculation that might work for my above problem or an easier way of displaying the previous records job number?

            Thanks.

            • 3. Re: Auto Fill Calculation - Last Entered Record
              philmodjunk

              Do you happen to know of a calculation that might work for my above problem or an easier way of displaying the previous records job number?

              Let ( Today = Get ( CurrentDate ) ; Right ( Year ( Today ) ; 2 ) & Right ( "0" & Month ( Today ) ; 2 ) and Right ( "0" & Day ( Today ) ; 2 ) )

              Will return your backwards date. The serial number can be included from a separate serial number field if you don't insist that the sequence restart at 1 for every day. If you do an auto-entered calculation that includes using the Max function plus 1 can be set up to include that as well.

              I'd also recommend that you use an auto-entered serial number as your primary key and use your current system only for identification purposes such as tracking your folder names.

              • 4. Re: Auto Fill Calculation - Last Entered Record
                raybaudi

                "Do you happen to know of a calculation that might work for my above problem or an easier way of displaying the previous records job number?"

                Create a cartesian self joining relationship ( NOT sorted ) and a calculation field:

                Last ( CartesianSelfJoin::Job Number )

                This is all.

                From Help On Line:

                Note: The last related value will depend on the way related records are sorted. If the related records are not sorted, then the Last function returns a value based on the creation order of the records.