Finding Again

[Mortie]

I note Ray's thread on Finding Child Records. How ironic that is similar to my question of the weekend. Hope someone can help me.

I have a set created around a unique field called hhnbr (household number).

There is a child table (policy_info) with the same hhnbr field as the linking field. There is a field in the policy_info table called policynbr. Hence each hhnbr can have many different related but unique policynbr.

There is a second child table, which has the fields hhnbr and policynbr. There are specific people's names in this table (driver_info) and linked to the table policy_info by policynbr field.

When I have a specific household on screen I display the policy information as well as the driver information on the policies.

My goal with this question is to be able to search the set with a Find By Key process for a specific policy number and display all the details for that policy, including the data from the parent table.

I have tried the Action Script process but cannot seem to make any of them work. I cannot locate an option that allows me to search the set, specifically in the policy_info child table and pull up the entire record and related info from all the tables in the set.

Let me know if there is more info needed.

Bob

[Pat Bremkamp]

RE: Finding Again

Bob,

If I understood you correctly, you have a parent-child-grandchild set that looks like this:

Household==(hhnbr)=="policy_info==(policynbr)=="driver_info

you want to search on a policy number and show all info. So, I suggest you build another "upside down" set as follows:

policy_info==(policynbr)=="driver_info==(hhnbr)=="household

then, you can search on policy number and display all the info.

Otherwise, if you want to show the information on the existing form, you will need a compound key that is something like:

policynbr+hhnbr

If you right click on your set and select properties and look at the driver_info table, the syntex for the key will be shown.

Pat

[Mortie]

RE: Finding Again

Thanks Pat.

Your understanding is correct.

I am performing the find by key from the inside of the main, and only, form. The form is set-up to the set I mentioned.

I will give the compound key a go and see if I can get it to work.

Thanks

[forskare]

RE: Finding Again

Bob,

Here's another way:

Create a form on a set with the parent/child/grandchild tables as you've outlined above. They should be linked one to many since each household will have more than one policy and each policy can have more than one driver.

Link the parent to the child table with the hhnbr field and the child to grandchild with the policy # field.

Show whatever fields you want from the parent table and then add a browse to show all policies that the hh (child table)has and another browse (grandchild table)which will show the drivers.

Here's what happens: whatever household has focus,you will see all policies belonging to the hh in the child browse. Highlight a policy # and in the grandchild browse you will see all drivers (as well as any other fields you desire).

That's a simplified explanation but you can do lots with that setup without much action scripting, etc.

kenn

[Ohlen Cartmell]

RE: Finding Again

I think you could expand on Ken's suggestion by putting the info on a tabbed subform. Tab 1 would contain the parent field; Tab 2, the child browse; Tab 3, the grandchild browse. Pick a parent on Tab 1, child on Tab 2 and see all the drivers on Tab 3.

[Al Buchholz]

RE: Finding Again

Bob

I have a similar situation that might provide a solution for you.

In my case, I own 4 season tickets to the Milwaukee Brewers that are split between 12-18 people that share in the cost. So I have to track each person, an 81 game schedule, and what games each person wants and gets.

To help me with the picking/assigning of each game, I developed a set that shows each person, each request that they have made, who else has also requested that game, and what other games the other people have requested.

The solution is a matter of linkages between the people table, to the request table, to the game table, back to the request table with a different linkage, and again to the people table ( now with the link to another person).

The form that shows this all is based on the set and it's a simple click on each of three browses to see the appropriate information on each level below.

So let the set linkages do the work for you and I think you'll find the solution is relatively easy.

Hope that helps!

[Mortie]

RE: Finding Again

Thanks for all the suggestions.

Ultimately what I am trying to do is find a policy number that is in a child table. Since my main (and only) form is set up on the parent table of the set it does not seem possible to do a Find By Key search on the child and grandchild tables.

Attached is a screen shot of my form. The area at the top is the parent table; middle is child table (with policy #); at bottom is the grandchild table, which utilizes look up fields in other tables based on a specific person's age.

So far my solution via Action Scripting is

1) Set record navigation to record #1
2) Display dialog to get a variable called getpolicynbr
3) Do Find Text Forward based on the variable

This "seemingly" works. However, the search on this particular database takes 3-4 secs over some 9,200 records. Some of the other databases, which are identical in look and function but different records, have as many as 40,000 records. It seems the best process is a Find By Key using an index. But, alas, that cannot be done since the indexes are based on the parent table and the policy number is not one of them since it is not in that specific table.

Additional suggestions on how to? or perhpas speed up the search?

[Peter.Greulich]

RE: Finding Again

Bob,

I don't think a compound index is possible since the index can't "span" the set and the policy number is not in the parent table. As you have discovered, "Find By Text" is fairly slow. However, you can do it w. a little xbasic on a custom find button and a form variable (vPolicy). Something like the following:

tbl = table.open("policy_info")
tbl.index_primary_put("policy_no")
tbl.fetch_find(alltrim(vPolicy))
vHdrNo = tbl.hhnbr
tbl.close()
parentform.index_set("Header_NO")
parentform.fetch_find(vHdrNo)
parentform.resynch()
end

I may have messed up the syntax a little. You will need to provide the proper field names, index naems, etc. But it should work.

Peter

[Al Buchholz]

RE: Finding Again

Bob

Two other suggestions.

1. Build an xdialog form that searches the table with the policy number and then returns the hhnbr and policy number so you can do a find by key of the hhnbr of your parent table and you have the policy number to position the child browse.

2. Build another set that has the table with the policy number as the primary table and link to the other tables as need be.

Either will work and both have their own advantages and disadvantages.

My guess is the second one is the easiest.