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)?
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?
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
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!
will a lookup work if all child records are created BEFORE their mother record?
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?