3 Replies Latest reply on Nov 8, 2013 11:22 AM by filemakernew

    Custom function or calculation



      Custom function or calculation


           Hello all,

           kindly help me make a custom function or calculation that generates a Product Lot Number base on the last two Digit of a Year for the first two digits, the second two digits the number of the month and the last three digits are sequential of three digits starting with 001 base from the number of materials received from that month. below is an example.


           First two digits (06) - Year

           2nd two digits (09) - Month

           3rd 3 digits (001) - number of material arrived from that particular month

           Thank you.



        • 1. Re: Custom function or calculation

               I strongly recommend that you not use this field as a primary key in relationships in your database. Use a serial number field for that purpose instead.

               If you do use this field, put in in one table only and use it only for searches, reports etc.

               Define a self join relationship:

               YourTable::Year = YourTableByMonthYear::Year AND
               YourTable::Month = YourTableByMonthYear::Month

               Define Month as an auto-entered calculation: Month ( get ( CurrentDate ) ) and Year as Year ( get ( CurrentDate ) )

               Set 3DigSerial as a number field with this auto-entered calculation:

               Max ( YourTableByMonthYear::3DigSerial ) + 1

               Be sure to clear the "do not replace existing values" check box and also clear the "Do not evaluate if all referenced fields are empty" check box.

               Your calculation to produce this value:

               Right ( Year ; 2 ) & Right ( "0" & Month ; 2 ) and Right ( "00" & 3DigSerial ; 3 )

               This should be an auto-entered calculation on a text field with a UniqueValues validation rule to catch the possible occurrence of getting the same value for two different records should two users try to create a record in this table at the same time.

          • 2. Re: Custom function or calculation

                 Hi Phil,

                 Kindly take a look to what I did. I think I'm missing something from your instruction.

                 Below is the link.


                 Thank you so much.

            • 3. Re: Custom function or calculation

                   Hi Phil,

                   Thank you so much. I got it.