updating primary table from child

[davidv43]

I have a primary table "demoggraphics" which is linked to a child table "followup". I would like to update a field "period" in the primary table based on a field "wks" in the child table. However, the update is a result of categorizing the data in the child table as follows:


SELECT
case wks > 1 .and. wks < 3
period = 2
case wks > 4 .and. wks < 7
period = 6
...... (several more classifications)

End Select

I am trying to set this up as a record event field rule, so that after the record is saved, the event fires which performs this calculation and posts the result to the primary table. The update is performed on a form which is based on a set with these two tables linked.

What is the syntax for addressing the fields "wks" from the child table and "period" from the primary table when the event fires while my form is open to the child table? I know I have to create table pointers.

Thank you!
dave

[G Gabriel]

Re: updating primary table from child

David:
Several ways you could do this but first, you could fine-tune this script a bit as follows:
SELECT
case wks <4
period = 2
case wks < 7
period = 6
.etc.

End Select

You probably will get a dozen different suggestions varying from table.open() to table.current() to posting to who knows what. But the simplest of them all is to put a script on the OnSave event of the form that says:

wks.value=period

The above makess the assumption that the object name on the form is the same as the field's name, i.e. "wks".

I can't tell what period is? a variable? a field name? but that is not important, only perhaps in terms of getting the proper reference.

I said earlier "the simples" and I lied.
The simples would be:
SELECT
case wks <4
wks.value= 2
case wks < 7
wks.value = 6
.etc.

End Select
Assuming you don't care to set the value of whatever "period" is.

[Stan Mathews]

Re: updating primary table from child

Is this a one to many set or a one to one set?

[davidv43]

Re: updating primary table from child

Thanks, Stan, this is a one-to-many set. The update needs to happen after I update the child table.

The issue is that I don't knwo the syntax for addressing both a child table field and a parent table field in the same expression. Do I use a pointer to each table (but how do I select the correct record within that table) or do I assume that the record cursor remains at the current position in the child and parent table. Or is it easier to simply address the form variable?

Dave

[Stan Mathews]

Re: updating primary table from child

I'm still struggling with what you want to do when.

The update is performed on a form which is based on a set with these two tables linked.

So you have a parent record visible and edit/enter a child record, and want to update a field in the parent based on what happens in the child record? Are you editing the child record in a browse? (seems likely) You want the parent record updated according to the last record in the browse (don't want any changes to earlier records used to update)?

Do I use a pointer to each table

Yes. The parent pointer is obtained with table.current(1), the child with table.current(2)

(but how do I select the correct record within that table)

Thus obtained the current selected row in the browse would be the record used.

[davidv43]

Re: updating primary table from child

precisely! I am using a browse to edit the child record and wish to use a value from the child table to calculate a value to place in the master table.

[Stan Mathews]

Re: updating primary table from child

use a value from the child table

Which record in the child table?

[davidv43]

Re: updating primary table from child

Here's the setup

Parent table
MRN as C
period as N
Child table
MRN as C
daysfu as N

The two tables are linked by MRN

The browse contains the field daysfu

the routine should be:

wks = daysfu / 7

select

[davidv43]

Re: updating primary table from child

Here's the setup

Parent table
MRN as C
period as N
Child table
MRN as C
daysfu as N

The two tables are linked by MRN

The browse contains the field daysfu, the main form has the parent field "period" displayed on another tab.

the routine should be:

wks = daysfu / 7

Select
case wks < 4
period = 2
case wks < 7
period = 6
case wks < 14
period = 12
.....
End Select



Dave

[Stan Mathews]

Re: updating primary table from child

The browse contains the field daysfu,

For each record in the browse.

first record in browse - I know how to update parent table
add a second record in browse - probably want to update parent table with new value
go back and edit first record - ??? what should happen
add a third record in browse - probably want to update parent table with new value
delete third record in browse - ??? what should happen

[G Gabriel]

Re: updating primary table from child

David:
Did you read what I wrote earlier?
I believe you did and probably I was not clear enough or got the names of the fields backwards. I am struggling with my computer, so I will be brief but hopefuly clear enough.

You could put a simple script on the onsave event of the form that says:
select
case browse1:wks<3 'this assumes the column title is "wks". Change if different.
period.value=2
etc
end select
topparent.resynch()

[davidv43]

Re: updating primary table from child

Thank you Gabriel. It looks like you are using the field names to change values.

Do you have to use .value to change data in form fields?

Dave

[davidv43]

Re: updating primary table from child

Stan,

I used table pointers as follows:


dim demotbl as table '(primary table)
dim fu_tbl as table '(child table)
dim t as n 'variable

demotbl = table.current(1)
fu_tbl = table.current(2)

t = fu_tbl.dof - demotbl.doi 'use the dof field from the child table and the

if t > demotbl.daysfu then
demotbl.daysfu = t
end if

[davidv43]

Re: updating primary table from child

Stan,

I used table pointers as follows:


dim demotbl as table '(primary table)
dim fu_tbl as table '(child table)
dim t as n 'variable

demotbl = table.current(1)
fu_tbl = table.current(2)

t = fu_tbl.dof - demotbl.doi 'use the dof field from the child table and the
doi field from the primary table

if t > demotbl.daysfu then ' if the result is less than what is stored in the primary record, do not update

demotbl.daysfu = t
end if

[G Gabriel]

Re: updating primary table from child

No..
The way I described it above is one way but is not in my openion the best way and is not how I would do it. But I mentioned it because some people got
their heads stuck on this ".value" thing. Somehow somewhere either in the help file or someone from alpha got everyone on board with this.

My preferred method is to do it like this:

select
case child_table->child_field<3
parent_table->parent_field=2
....
end select
topparent.resynch()

P.S. I have reached max level of aggravtion! I can't type anymore, the cursor keeps jumping allover the place. I am pretty darn sure it is not my compute. It's happening with several ones. But the above should be self-explanatory.