Query Child Records

[TonyMurray]

Hi,

This is probably quite simple but I cannot figure it out! Any help would be greatly appreciated.

I have a parent table with a logical field which I would like to set as true if a field on the child table contains a specific string... Can anyone help with how I would query the child records and if the child field contains a string it would set the logical field to true?

Thanks in advance for any help.

Tony

[Stan Mathews]

Re: Query Child Records

What if there are two child records, one containing the string and the other not?

[TonyMurray]

Re: Query Child Records

Hi Stan,

In many instances there will be 3 or 4 child records containing a few different company names. What I want to achieve is if any of the child records contain the specified company name text, I would then want it to change a logical value if a field on the parent.

A parent table record is the details of a subcontracted company. The child contains a list of our internal companies who they are cleared to work for. My aim is to have a number of little smilies (in conditional objects) and when any child record field contains the specified text i.e. "Company 1" it will change the value of a logical field thus enabling the smilie...

I hope this is enough information? but please feel free to ask me to clarify if required.

Thanks again for your help - you helped me about 7 years ago when I first done this little DB :-)

Tony

[TonyMurray]

Re: Query Child Records

Hi Stan,

In many instances there will be 3 or 4 child records containing a few different company names. What I want to achieve is if any of the child records contain the specified company name text, I would then want it to change a logical value if a field on the parent.

A parent table record is the details of a subcontracted company. The child contains a list of our internal companies who they are cleared to work for. My aim is to have a number of little smilies (in conditional objects) and when any child record field contains the specified text i.e. "Company 1" it will change the value of a logical field thus enabling the smilie...

I hope this is enough information? but please feel free to ask me to clarify if required.

Thanks again for your help - you helped me about 7 years ago when I first done this little DB :-)

Tony

[Stan Mathews]

Re: Query Child Records

I don't know if you are looking for a real time solution or just a way to update the parent record.

If you want a process to run and then view the reaults,

create an update operation to set all the parent record logical fields to False
create a posting operation to post True to the logical fields if the child record contains the desired value

Run them in sequence.

[TonyMurray]

Re: Query Child Records

What I can't seem to get my head around is what method to use to search the child table for the text? If it is a matter of running a line of code each time I fetch a parent record - I just cant find what code to use to evaluate the contents of the child table and store the multiple values somewhere to evaluate. I am also quite sure this is quite simple but I haven't written much code for a few years :-(

The little bit of code below lets me open a table, query the record I want (from a one to one relationship) and pops the value of the field from the single record into a variable. I guess what I want to do is run that code for all child records and store the values somewhere that I can evaluate tem.

tbl = table.open("self employment assessment")
tbl.query_create("", "subconname = " + s_quote(VAR->vsubnameforfilt))
vfilttrade = tbl.record_content_get("tradingas")

Again. thanks for your help

Tony

[Stan Mathews]

Re: Query Child Records

tbl = table.open("self employment assessment")
tbl.query_create("", "subconname = " + s_quote(VAR->vsubnameforfilt))
tbl.fetch_first()
vfilttrade = ""
while .not. tbl.fetch_eof()
vfilttrade =vfilttrade+ tbl.record_content_get("tradingas") +crlf()
tbl.fetch_next()
end while
tbl.close(()

Should create a list of the child table values. If tradingas is the field name in question

tbl = table.open("self employment assessment")
tbl.query_create("", "subconname = " + s_quote(VAR->vsubnameforfilt))
tbl.fetch_first()
vfilttrade = ""
while .not. tbl.fetch_eof()
vfilttrade =vfilttrade+ tbl.tradingas +crlf()
tbl.fetch_next()
end while
tbl.close(()

should do the same thing

[TonyMurray]

Re: Query Child Records

Thank You Stan!