5 Replies Latest reply on Jun 6, 2012 11:09 AM by comment

    problem with empty lookup field

    liyangao

      I have a 1 to many relationship between mother and children. For each children record, it has a field called kidschoolreportID. I defined a lookup field in mother table to copy the non-empty momschoolreportID from the children into their mother's record. The problem is that I got a lot of mother record with empty momschoolreportID while some of her kids have nonempty kidschoolreportID. Why doesnot it work? Is there any workaround to copy the nonempty kidschoolreportID to mom's record?

      Thanks for your help!

        • 1. Re: problem with empty lookup field
          comment

          A lookup from child into a parent makes little sense. What are you trying to achieve here? Suppose a mother has 5 children, where #1, #3 and #5 are empty, while #2 and #4 have data - what should the mother record be showing (and why)?

          • 2. Re: problem with empty lookup field
            liyangao

            Thanks for your response.  Either #2 or #4 shows in the mother record is fine as long as it is nonempty. This way momschoolreportID can become the key to link to another database of a sudy about mothers. I want to figure out how to lookup the first non-empty kidshooolreportID to fill in to the corresponding momschoolreportID. Does this make sense or is there a better way to do it?

            • 3. Re: problem with empty lookup field
              comment

              First, assuming child records are created AFTER the parent, a lookup will not work here, because there is nothing that will trigger it: creation of a new child is a non-event as far as the parent is concerned.

               

              You can calculate the first* non-empty child value as =

               

              GetValue ( List ( Child::Valuefield ) ; 1 )

               

              The last* non-empty child value can be obtained by using the Last() function.

               

               

              Note that both calculations must be unstored - i.e. they can be used to fetch related records from another table, but not as matchfields on the "supply" side of a relationship. I am not sure what you mean by "link to another database of a sudy about mothers".

               

               

              ---

              (*) "first" and "last" here mean by the sort order of the relationship.

               

              Message was edited by: Michael Horak

              • 4. Re: problem with empty lookup field
                liyangao

                Thanks a lot for your reply. I tried to use the calculation of GetValue(List();1) and it worked!

                 

                One more question, will a lookup work if all child records are created BEFORE their mother record? I am wondering whether that is why I got one part of the lookup field filled in correctly and the other part empty.

                 

                Thanks a ton. This has been very helpful!

                • 5. Re: problem with empty lookup field
                  comment

                  liyangao wrote:

                   

                  will a lookup work if all child records are created BEFORE their mother record?

                   

                  It depends:

                   

                  If there already is a child record with a ParentID, and you create a new parent record, it will perform the lookup from the first related child as soon as you populate the ParentID field in the new parent record.

                   

                  However, how will the child record/s know the ParentID before the parent is created?