6 Replies Latest reply on Sep 27, 2013 4:59 PM by marcg_1

    Calculating a field using find



      Calculating a field using find


           Hello, I'm trying to calculate a field in one table based on a find in another, here's an example.


           I have two tables.  There's a table named Parents of all parents with the fields: parentName, birthdayOfYoungestChild .  There's a table named Children of all children with the fields: parentName, birthday.  I'd like to have Parents::birthdayOfYoungestChild be a calculated field created by

           - using Parents::parentName to find all records in Children with a matching Children::parentName (there might be many)

           - sorting those records in ascending order by Children::birthday

           - inserting the birthday from the first record of the sorted list into Parents::birthdayOfYoungestChild

           I'm a newb and confused.  Scripts appear to be necessary for the find and sort -- they don't seem to be supported in field calculations -- and I don't see how I can use the output of a script in the calculation of a field value ...???

           Any pointer or headstart (or complete answer :) ) would be greatly appreciated.  Thanks in advance

        • 1. Re: Calculating a field using find

               What you need is a relationship, not a find. With a relationship, you can match a given record in Parent to all related children records and then we can use the relationship to get the birthday of the youngest child.

               But you shouldn't use a parent's name in the relationship. It's possible to have two different people with the same name and people also change their names. Use an auto-entered serial number field instead.

               Parent::__pkParentID = Child::_fkParentID

               Then you can specify a sort order for this relationship that sorts the related Child records by age and then Child::Birthday on the Parent layout will return the birthday of the youngest child.

               But that assumes that your child records have only a single parent. With two (and these days, it can be more than two) parents for a given child we need an additional table:


               Parent::_fkFamilyID = Family::__pkFamilyID
               Child::_fkFamilyID = Family::__pkFamilyID

               And in this case, you'd specify that sort order for the Child records in the Family to Child relationship.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Calculating a field using find

                 Thanks for the quick response. 

                 I modified the relationship between the Parent and Child tables as you described: the respective ParentID fields are the matching keys between the tables and the relationship is sorted in ascending order by Child::Birthday.  But I'm still not sure on how to "pick off" the birthday of the youngest child, which should be Child::Birthday of the first record in the sorted list of related Child records, and have it stored it in Parent::birthdayOfYoungestChild.  I tried defining Parent::birthdayOfYoungestChild as Child::Birthday in a calculation, but that didn't work.

                 I should have explained that the objects in the database are not really parents and children; they're something else, each with a unique name and each child only has one parent, so I don't run into the very real issues that you mentioned above with multiple parents and non-unique names.

            • 3. Re: Calculating a field using find

                   SInce I listed two different structures, I am guessing that you implemented the first option based on your latest post.

                   You shouldn't need any calculation field at all for this.

                   But you didn't sort your records correctly. You have to sort your Child records by AGE not birthDAY. A birthDATE on the other hand could be used as the sort field. And this must either be a field of type number (age) or date (birthDATE) in order for the sort to work.

                   One way to check this relationship is to place an unsorted, unfiltered portal to Child on your parent layout. The youngest child record should be listed first.

                   With the relationships as specified, you can simply place the Child::Birthday field on a Parent layout and it will display the youngest child in the first row of the portal if your relationship is working.

                   If that portal works, you can simply drag the fields in the portal outside the portal and remove the portal. The fields will still show data from the youngest child record. And any calculations that you set up to evaluate from the context of Parent can simply refer directly to a field in Child to access data from the youngest child.

                   Thus your calculation field that referred to Child::Birthday would have worked if your relationship were set up correctly, but isn't a field that you actually need.


              • 4. Re: Calculating a field using find

                     Update: the calculation assignment does seem to work in the Parent layout, but, when I display Parent::birthdayOfYoungestChild in the Child layout, it is blank.  How do I get the value to display in (a) the Child layout and (b) if/when I want to export the Parent table, e.g., as an Excel file?  Thanks very much.


                • 5. Re: Calculating a field using find

                       for b) you do not need any calculation field.

                       For a) the field should not be blank. A blank field would suggest that you are on a child record that is not linked to any parent record.

                       And in some contexts, you could also use a summary field on your child layout that returns the minimum of the date field. The summary field would return the minimum if your found set consisted of only the child records for a specific parent record.

                  • 6. Re: Calculating a field using find

                         All is working now; thank you very much.  The blank fields were an updating issue, which I was able to eliminate by changing the field type in the parent record from a Date field that was calculated to a Calculation field that returned a date.  Also, everywhere that I said "day," I meant "date," so I was able to get the effect that I wanted simply by sorting on birthdate the relationship between the two tables.  Again, many thanks.