1 field to hold data from a field in a table linked many

[Tim Kiebert]

Re: 1 field to hold data from a field in a table linked many

Brad,

The script in my example also results in the LOB_active field being updated for all records. It results in a spare table which you can delete at the end of the process if you want. It would be real interesting if you have the time or inclination to see what the speed difference is between my approach and Mike's.

Mike,

As far as speed goes I think you could incorporate the batch_begin()... batch_end() functions as they are suppose to optimise this type of process. I think they lock the table and then only update the index after the Batch_end() rather than updating the index after each record.
I would also place the opening and closing of table test_policy outside of tblA's while loop so you open and close it once.

You can get rid of the leading ',' in the LOB_active field by changing the concanetation line slightly. A trick I learned from Stan Mathews

 vcPolCat =alltrim(vcPolcat)+If(vcPolCat+"","",", ")+alltrim(tblB.lob_code)

[Tim Kiebert]

Re: 1 field to hold data from a field in a table linked many

Oops

 vcPolCat =alltrim(vcPolcat)+If(vcPolCat[B][COLOR=Red]+[/COLOR][/B]"","",", ")+alltrim(tblB.lob_code)

should be

 vcPolCat =alltrim(vcPolcat)+If(vcPolCat[B][COLOR=Red]=[/COLOR][/B]"","",", ")+alltrim(tblB.lob_code)

[Brad Chesnut]

Re: 1 field to hold data from a field in a table linked many

I'm just so dang excited about this I can't hardly sit still!!!!!!

Tim, I'll test both. I haven't tried yours yet simply because Mike's was so easy to add to my actual application, and almost exactly like I need it. I'll get to that in a moment. First, I just want to thank you fine folks for the incredible results and solutions you've come up with. I've got the exact the solution I need. The added dialog boxes were a really nice touch too!

There's one additional adjustment I need to make this work exactly as needed.

In the table Policy there's a field "Active". It determines whether the policy is active or not with a simple "Yes" or "No" placed in the field. I ONLY want to pull in the LOB_Code for the Active policies (active="yes").

In the LOB_Update code, I assume that additional line would be in this section ...

if a5_get_records_in_query("policy","P_Code_Instar = "+s_quote(vcLink))=0 then
GOTO NEXTCLASS
else
tblB = table.open("policy")
query.filter = "P_Code_Instar = "+s_quote(vcLink)
query.order=""
query.options=""
xiB = tblB.query_create()

Not being an xbasic programmer, I can't even add a simple thing like that. I'm truly embarrassed and very humbled to all of your great work!

Could I bother you for this last fix?

Gentlemen, again, I can't begin to thank you enough!!!! What you've done for these programs I'm writing is beyond words! Thank you doesn't cover it!

[Tim Kiebert]

Re: 1 field to hold data from a field in a table linked many

Well, your very welcome.

Yes, you assume correctly.

if a5_get_records_in_query("policy","P_Code_Instar = "[COLOR=Red][COLOR=Black]+s_quote(vcLink)[/COLOR]+".AND. alltrim(Active) = \"Yes\""[/COLOR]) =0 then
   GOTO NEXTCLASS
else
   tblB = table.open("policy")
   query.filter = "P_Code_Instar = "+s_quote(vcLink)[COLOR=Red]+".AND. alltrim(Active) = \"Yes\""[/COLOR]
   query.order=""
   query.options=""
   xiB = tblB.query_create()

I am not at a place where I can test this right now but think I got the syntax right.

[Brad Chesnut]

Re: 1 field to hold data from a field in a table linked many

Tim,
I added the filter exactly as you entered it. Works perfectly. Damn, I'm so happy I can't begin to tell you! I've been fighting this problem for months! Months ago I should've just came here to the forum and asked for help.

As I said early, thank you don't begin to cover it!

[Tim Kiebert]

Re: 1 field to hold data from a field in a table linked many

As I said early, thank you don't begin to cover it!

Is that an insurance pun?;)

[Mike Wilson]

Re: 1 field to hold data from a field in a table linked many

Brad, Tim:

I implemented Tim's suggestions in the function of taking the table B open outside the loop and also use tbl.batch_begin/end() function. I loaded the example with 1000 records in the contacts table and for each contact gave them 12 policies each for a total of about 12,000 child records. Running the two versions of the function were as follows:

ver1: Table B open inside loop & NO tbl.batch_begin/end():..............123 seconds.
ver2: Table B open outside the loop & USING tbl.batch_begin/end():....83 seconds.

Excellent work, Tim. Thanks for the fun exercise, Brad.

Mike W