Pulling Data From Parent DB

[Mike Atkinson]

I am pretty new to forums, and not very experienced with A5, so bare with me...

I have a set with a CLIENT.DBF as the primary, the WO.DBF as a one to many child of the CLIENT.DBF and an EVENT.DBF that is a one to many child of the WO.DBF. I guess you could call that a nested one to many link. The link between the CLIENT.DBF and the WO.DBF is a common CLIENT_NUMBER field, and the link between the WO.DBF and the EVENT.DBF is a common WO_NUMBER field.

I am trying to get the LAST_NAME field in the EVENT.DBF to automatically fill itself with the contents of the corresponding LAST_NAME field from the CLIENT.DBF. I have tried using a default expression and skip expression, I have tried to use a forced lookup table, etc. It seems like I am missing something simple, but I just can't figure out how to retrieve the data from the CLIENT.DBF when entering data into the EVENT.DBF even when working in a region within the set's form.

Any help would be greatly appreciated.

[Tom Cone Jr]

RE: Pulling Data From Parent DB

Mike, I've done this on rare occasions, using code which pulls the desired field value from objects on the current form, and passes it to a second form where the new record is being entered.

However, most avoid storing redundant data in child and grandchild tables, in order not to have to edit multiple tables when field values change later on. A change made to the parent table would require corresponding changes in the grandchild table, for example, if a person's last name changed.

-- tom

[JohnZaleski]

RE: Pulling Data From Parent DB

Tom has a point about last names possible changing.I might think about saving a unique customerID instead.

The default field rule with a lookupc function and a skip rule could do it.Use the option "end of entry".
In this case the lookupc would be almost a nested lookupc function.Here's the sytnax in a situation where a field gets a transaction and a field on the transaction gets a name:

LOOKUPC("F",LOOKUPC("F",Poltrxid,"agent","policyh.dbf","trans_id"),"agent_name","agents.dbf","agent")

[Ken L Herrold]

RE: Pulling Data From Parent DB

To Mike:

I agree with both Tom and John - more particularly with John's sugestion of a client ID for the link, rather than a name.
A good way to look at the lookupc() function is that at the time of data entry into the child/grandchild fields, the parent has already been 'saved' and its indexes updated. That means that any field in that parent record would be available for transfer with the lookupc() function - with the field rule method. Charlie Crimmel has a method to this end using variables, somehow, but I didn't understand it and stayed with John's method.

To John:

John have you noticed any stalls or pauses during data entry [while lookupc() processes] with your method used against a very large parent table? I've only tested the method on a relatively small parent table and have always wondered "How will this function when the parent table has 3k's of records?", for instance.

Thanks to both in advance

Ken

[JohnZaleski]

RE: Pulling Data From Parent DB

Ken,
If I remember, alpha made some improvements in this area when it went to 4.5
It uses indexes, so on a form it should be barely noticeable.On a browse, I'm sure it would slow down paging up or down.
John

[Ken L Herrold]

RE: Pulling Data From Parent DB

To John:

Thanks, and Alpha On!

Ken

[crimmelcp]

RE: Pulling Data From Parent DB

Mike:
I do this a lot in my payroll application.

And yes I keep redundant data in the history files.
A lot of times I may have to go back 2 or 3 years after the job is over and pull data from history for legal purposes.
Employee files change and wage information changes. I need to be able to see data as it was originally keyed in.

The way I do it is:
When I select the name in the parent record,I write the information to global variables.
Then in the data entry field rules of the child or grandchild record I have a formula that checks:
If(first_name="",var->vfirstname,first_name)

also check the box for default on entry.

also the following code goes in the on activate and on fetch sections.
'on activate
dim global vfirstname as c
dim global vlastname as c
var->vfirstname=""
var->vlastname=""
t1-table.current(1)
var->vfirstname=t1.first_name
var->vlastname=t1.last_name

the same code would go in the on fetch section




charlie crimmel