5 Replies Latest reply on Jan 10, 2014 4:55 PM by philmodjunk

    Resetting a count



      Resetting a count


           I am new to Filemaker and trying to find a way of resetting a count based on a certain field.

           I have a database that allows defects to be added to a contract. I have a table with contract numbers The contract numbers are in the format of CM00000000. I have another table of defects which contain the contract numbers and the two fields are linked in relationships. When the user selects a contract I am trying to get the system to generate the next defect number in that contract.

           i.e       Contract selected CM00001234 system generates defect no CM00001234/1

                      user then inputst CM00001532 system generates defect no CM00001532/1

                      user then inputs CM00001234 this time systme generates CM00001234/2

           I hope this makes sense




        • 1. Re: Resetting a count

               Link Defects to an occurrence of itself by the contract number field:


               Defects::ContractNumber = Defects|SameContract::ContractNumber

               Define Sequence as a number field in Defects.

               Use this calculation to assign a value to it:

               Max ( Defects|SameContract::Sequence ) + 1//do not use this in a field of type calculation

               Define a text field in Defects with this auto-enter calculation:

               ContractNumber & "/" & Sequence

               Select the unique values validation option for it.

          • 2. Re: Resetting a count

                 Thanks for this. Think i am out of my depth.

                 You lost me on the first line.

                 How would I link defect to an occurrence of itself by the contract number field. I only installed the software yesterday :)

            • 3. Re: Resetting a count

                   In Manage | Database | relationships, make a new table occurrence of Defects by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be Defects|SameContract.

                   We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                   To learn more about table occurrences see: Tutorial: What are Table Occurrences?

              • 4. Re: Resetting a count

                     That's great got there in the end :) One small problem is that the first record does not allocate a number 1 I assume that is because the field ids blank first time you input. can this be overcome

                • 5. Re: Resetting a count

                       You may have had a record in your table before you added these features and thus you could just delete this one record. Each new record should not have that problem. Also, if you are using an auto-enter calculation (the other option is to use a script for creating new records and assigning this value), make sure to clear the check box labeled "do not evaluate if all referenced fields are empty".