Find Record main record from the child table is not working for me

[MikeData]

Re: Find Record main record from the child table is not working for me

First enjoy dinner, will catch tomorrow.

[Al Buchholz]

Re: Find Record main record from the child table is not working for me

I had a feeling that the number of records is large and you need speed...

You'll need to add indices and work the query to make sure that Lightening Query Optimization is used...

Currently, you have

query.filter = "(( Var->fColorHair $ colorhair ) ) .and. (( Var->fResideCity $ reside ) ) .and. (( Var->fSchoolKid $ kidslst ) )"


why not

query.filter = "(( Var->fColorHair = colorhair ) ) .and. (( Var->fResideCity = reside ) ) .and. (( Var->fSchoolKid $ kidslst ) )"

or
query.filter = "(( Var->fColorHair -"|" + Var->fResideCity = colorhair -"|" + reside) ) .and. (( Var->fSchoolKid $ kidslst ) )"



Also I would add an indices to parents

Hair_City	                Colorhair-"|"+Reside	 	       Ascending	All	
Index_Keywords_for_KIDSLST	KEYWORDSI(KIDSLST,VAR->EXCLUDED_WORDS) Ascending	All

Use the full text search button to help build the last index.

fulltext.PNG

[MoGrace]

Re: Find Record main record from the child table is not working for me

Al - what is the purpose of using the '|' in the query filter? What does it do?

[Al Buchholz]

Re: Find Record main record from the child table is not working for me

Al - what is the purpose of using the '|' in the query filter? What does it do?

It separates the fields so there is no cross over in case of multiple words... Probably not likely here, but is possible..

Inconsistent results lessen the confidence in a system.

Hopefully Mr. Data will fill us in on his results...

[MoGrace]

Re: Find Record main record from the child table is not working for me

So that part of the query filter evaluates to "blonde-|London = colorhair-|reside" ? 'no cross over in case of multiple words' - you mean as in if RESIDE is 'New York' or COLORHAIR is 'light brown'?

[Al Buchholz]

Re: Find Record main record from the child table is not working for me

So that part of the query filter evaluates to "blonde-|London = colorhair-|reside" ? 'no cross over in case of multiple words' - you mean as in if RESIDE is 'New York' or COLORHAIR is 'light brown'?

actually the minus sign won't show - it's used to truncate to the left and the concatenate the next string

"blonde|London = colorhair|reside"


It would also keep from building new words from the combination that don't exist individually

ie "down|town"

without the |

could yield downtown

which really isn't a choice from the individual fields.

[MikeData]

Re: Find Record main record from the child table is not working for me

In this case the choice of color hair is not part of the combination, as it is a fix choice.
The reason for the use of combining text was to overcome the issue of finding records based on data from parent and child tables at the same time.
The fields containing | are updated for every child entry. So the Parent table has the info form the kid table.

[Ted Giles]

Re: Find Record main record from the child table is not working for me

Mike, where are you with this at the moment?
Do you have a resolution?

[MikeData]

Re: Find Record main record from the child table is not working for me

Ted, no, I have not but I have improved it a bit. I am testing further.
I do appreciate, I really do, the help of you guys, but is not fair, to just ask and do nothing on this end.
Later I will post a sample of the improvement and see if I can get it perfect.

[martinwcole]

Re: Find Record main record from the child table is not working for me

Another option, where you want to find "parent" record, based on a search in the "child" is to build a mapped table with the child table as the first table, and the parent table as the second table, and include what fields you need in each to correctly indentify both the child and the parent.

Open a second form based on the mapped table, and when the user finds the record they want, get the parent record record number, and when you close that form fetch the parent to that record number in tjhe original form.

I wrote one like this for a client, that searches dynamically on multiple fields - runs very fast, and the users like it

the search is like a drill down - search in one field, and then add to the search by maintaining the first query, and then adding to it by then searching on a second field, and then a 3rd

like child product first, parent state second, and then parent city

[Tom Cone Jr]

Re: Find Record main record from the child table is not working for me

Martin, that's interesting. Any chance we can prevail upon you to post a sample? Thanks. -- tom

[MikeData]

Re: Find Record main record from the child table is not working for me

Me too!!!!
Attached is the latest, I have been able to make it it bit faster going from one record to the next. But still 7 to 8 seconds between records.
Of course in the sample is not noticeable, but in 100,000 records......

It will be great not to have to deal with combining the data from the child table into the master table.

Button 4 in the main menu is the latest.

[MikeData]

Re: Find Record main record from the child table is not working for me

I did try to use mapped tables and I did not get it to work right, perhaps someone can tell what is wrong.
It looks that it should work and work much faster.
Attached is a new version with mapped table option in the main menu button 5

The mapped table is = newtblmapped
The new mapped form = NewSugMappedForm
the new mapped query = QueryFormappedTBL

[Ted Giles]

Re: Find Record main record from the child table is not working for me

Got it working Mike using the Mapped Table.
You don't have all of the necessary fields in the mapping.
What I did.
Created a mapped table using Parent and Kids.
Linked on Parent ID
Created a query - using the Genie on the default form created when you open the mapped table - run query and the correct options appear.
Next problem is that there are multiple occurrences of matching Parent ID. I suppose you plan to produce some sort of list to work from?

I will try the query in SQL as I'm learning SQL and have SFA to do at the moment.
Will let you know what happens.

[Ted Giles]

Re: Find Record main record from the child table is not working for me

Mike, this SQL test returns the correct record info. It seems to be a bit easier than the hoops you are going through at present.

SELECT parents.Parent_Id,
parents.Name,
parents.ColHair,
parents.Reside,
kids.Parent_ID,
kids.Name,
kids.`Sch City`
FROM test.parents parents
JOIN
test.kids kids
ON (parents.Parent_Id = kids.Parent_ID)
WHERE (parents.ColHair = 'Blond') AND (parents.Reside = 'London')

I have also revisited the Tree Control and it seems that it's the easiest for the user as only the matching records are shown.
So if you are looking for people with Red Hair, the next node shows the only the selectable locations for red haired people.
Within the location node are the possible Schools , and within them, the parents.
The order is easily changed, so if you are looking for Hair Colour within Location, within Schools you just need to turn the tree upside down.