2 Replies Latest reply on Oct 29, 2012 3:16 PM by philmodjunk

    Field calculated based on similar results...



      Field calculated based on similar results...




           I have a database that houses information on an award recognition system we have set up.  One person can have multiple records in the database because it's entered in as a brand new record every time they get recognized.  I want a field that calculates on the record how many times that specific person is in the database (i.e. how many times they've gotten an award).  I want it to count the number of records in the database in which the first name field, last name field, and site field all match those on the current record.


           Is there a way to easily calculate this?  I'm wrapping my head around how I think it should be set up, but am not able to fully grasp the code I need for it.




        • 1. Re: Field calculated based on similar results...

               Update:   So, I created a script that now finds the results of what I'm looking for.  I'd now like a field to be calculated based on the amount of records found in that script (and do so and update automatically, of course).  Any thoughts on accomplishing that?


          • 2. Re: Field calculated based on similar results...

                 You don't need a script, but I recommend against matching by name to count the number of awards that a person receives. What if the person gets married and changes their name?

                 Instead, match by a serial number ID unique to each person and then name changes and duplicate names will not be an issue.

                 Let's assume that you have these tables and relaitonships:


                 Employees::__pkEmployeeID = Awards::_fkEmployeeID

                 For explanation of the above notation, see: Common Forum Relationship and Field Notations Explained

                 Modify this to create:


                 Awards::_fkEmployeeID = AwardsSameEmpID::_fkEmployeeID

                 To create AwardsSameEmpID:

                 In Manage | Database | relationships, make a new table occurrence of Awards 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 AwardsSameEmpID.

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

                 Now, you can use Count ( AwardsSameEmpID::_fkEmployeeID ) to count the number of awards that a given employee as received. Also, you can define a summary field, sAwardCount, in Awards as the "count of" _fkEmployeeID and then you can refer to AwardsSameEmpID::sAwardCount to get the same count of how many awards that a given emplyee has received.