1 Reply Latest reply on Nov 7, 2013 11:31 AM by philmodjunk

    Calculation

    filemakernew

      Title

      Calculation

      Post

           kindly help me to make a calculation to generate our own Lot No. if I click a new record. In our SOP, if we receive a material we are following this format to generate a certain lot no. for materials we receive. The first two digits is the last two digit of the present year, the second two digits is the  number of the month and the last three digits are sequential of 3 digits base from the number of materials received from that  month.

           below is the example if I received material today my lot no. will be 1311001 if I receive another material tomorrow 1311002.....1311003...

            (13 - last two digit of the current year ; 11 - Number of the current Month ; 001 last three sequential digit based from the materials received from this month

            

           Thank you.

        • 1. Re: Calculation
          philmodjunk

               I strongly recommend that you NOT use such a lot number as a primary key to link tables in relationships.

               You can set up cMonth as a calculation that computes a date for the first day of the month from a date field in your record:

               DateField - Day ( DateField ) + 1 )

               You can then set up this self join to match a record to all other records in the same table from the same month:

               YourTable::month = YourTable|SameMonth::Month

               YourTable|SameMonth would be a new table occurrence of YourTable.

               You can then define a number field for the sequence part of your lot number and then this calculation can be used to assign the next value in the sequence:

               Max ( YourTable|SameMonth::Sequence ) + 1

               Then you can set up a field with an auto-enter calculation:

               Right ( Year ( Datefield ) ; 2 ) & Right ( "0" & Month ( Datefield ) ; 2 ) & Right ( "00" & Sequence ; 2 )

               And you can set up a "unique values" validation option on this field to ensure that you don't accidentally get two lots with the same value.