extend to find families in join table

[peptoulcer]

I have people (persons table). Many may live in same house but belong to different families. Some have no family (sad yes).

From the person table, I need to find many people (some have families and some don’t). I find people by other information not in the sample file (age, six, birthday). I may find only one person in family#1 for example. But if there are other people in family #1 I need to increase the find to include them

I am trying to use extend based on join table kf_family. But when I extend the found set, it jumps to the first record again and I lose where I was. Even sorting doesn’t stop the jump back to the beginning. I tried putting the record number in a variable but when the set extends, the record I am on is no longer that record number.

Can I loop through records and extend as I find new family number? I was hoping not to create another relationship like fast summaries. I tried using the go to related method but when I go back to the Persons table, I lose the people without families. Users will be coming up with original find then without them knowing, script needs to extend? to include members.

I included a file showing what I have so far which doesn’t work. I hope this makes sense. Can a find do this?

ps I did search for long time first.

family.zip

[efen]

It's not clear how you want the find to work. You state that you want to find people by "other information". Do you want to find all the other family members if, say, one particular family member has a birthday in June? Or...?

[peptoulcer]

Hello Efen, thank you for assisting. I guess I was not clear. A find will be performed by User which finds according to various criteria. That is all fine and I need no help there.

Once we have the found set then we need to see which People have family members. This is what I need help with. I want to include all members of the family belonging to the person whom I found. We find the people who fit the rules (for party). All members of their family must also have specific invitation in hand or they cannot get in the door.

If in our found set we have one person who belongs to FamilyID 1 then we need to also extend the found set to include the other family members who belong to FamilyID 1. And same with any other families (but only those who have people in the original found set).

Andy

[efen]

See attached, your file amended for one way of doing it.

Find the people who fit your criteria and use the amended script in your file to create the found set of family members.

family1.zip

[peptoulcer]

I appreciate the idea. However, not all Persons have a join or family record (nor do we want them to). The family record holds Head of Household (accounting) information and the join is actually for other purposes but that is where I access family ID. I left some people without family in the join for that reason.

I was hoping to handle it all from the people table extending to include other people in same family instead of adding three table occurrences just to extend. I suppose I can add the family IDs to a variable and then loop through that variable extending the found set but it seemed the same as looping through the records and I thought I should be able to do it from the records themselves.

Maybe I can open new window to extend then close the new window and I won't lose my place.

[peptoulcer]

Maybe I can open new window to extend then close the new window and I won't lose my place.

Didn't work. But by using two variables (one to hold a string of ones done using patterncount and another variable to hold the one I was using to extend the find, i got er done.

Only problem is it still starts over. Since it checks if it is in $track, it just skips them again (one at time) and keeps going then Whole thing stops when no more related family. I think it will be fast enough. Needing to extend a Found set in Persons according to whether there is a certain related record would be more common need so I would like to know other ideas.

Displaying is only 1 Person in found set who belongs to family1 and it needs to extend to 3 person records (3 people belong to family1). Here is how I did it. For me, its pretty slick but i know it is junk compared to stuff you guys do so no snickers. Can it be polished?

family2.zip

[Vaughan]

I may find only one person in family#1 for example. But if there are other people in family #1 I need to increase the find to include them

It seems to me you need a table of Families that People can be related to (in the rdb sense). If a person can belong to only one family then a FamilyID field in the People table will suffice. Finding the other family members won't then be necessary because they can be displayed in a portal on each person's record.

If you do want to find them then the GTRR script step will do it.

I sounds as though you also need a Houses table, which can have multiple families, and to which the address can be sent.

BTW following this logic, EVERY person has a Family, but they may be the only person in that family.

[comment]

Displaying is only 1 Person in found set who belongs to family1 and it needs to extend to 3 person records (3 people belong to family1).

I am a little confused here, because your file allows a person to belong to more than one family - otherwise why have a join table?

It's also not clear what do you want to happen if the found set has more than one record.

[peptoulcer]

Thanks guys for answering. I responded big long post and then forum ate it. burp.

So again ...

I am a little confused here, because your file allows a person to belong to more than one family - otherwise why have a join table?

The join table I will use isn't the standard join between family and persons although a person can belong to more than one family or have no family at all. It confused things. I realized later that it was causing confusion when I said I was searching for related family. I am using a family lineitem table but I am not searching for family - I am searching for products. Lesson learned there to keep file and example to exactly the situation. I apologize.

It's also not clear what do you want to happen if the found set has more than one record.

There will be more than one most times. I beg your review of revised. I spent 30 hours researching and found custom function and how to use more than one find. So example is this:

One or more clients can be in found set. Maybe find was based on income or area code and then salesman manually excludes some. Now with this found set (who includes persons who have never bought products), we want to find all other people (in this same table) who have purchased the same products that the original set of people purchased (lineitems).

So even found set of one person can have multiple products which can extend the found set to many more people but still keep those people who haven't bought any product at all. I'm learning a lot. This isn't easy all this trying and testing and I am still unsure if it gives the correct results. It does in this small sample because I can manually count but I don't trust it.

Improved table attached. I also read somewhere that I didn't need enter browse mode at top lol.

Also, I quit trying to extend at the record and write them to variable and then extended at the end. I think I'm on the right track.

Find Associated.zip

[comment]

How about something like:

Go to Record/Request/Page [ First ] 

Loop 

Set Variable [ $searchValues; Value:List ( $searchValues ; List ( LineItems::kf_product ) ) ] 

Go to Record/Request/Page [ Next; Exit after last ] 

End Loop 

# 

Set Variable [ $searchValues; Value:FilterValues ( ValueListItems ( Get ( FileName ) ; "Products" ) ; $searchValues ) ] 

# 

If [ not IsEmpty ( $searchValues ) ] 

Enter Find Mode [  ] 

Loop 

Set Field [ LineItems::kf_product; GetValue ( $searchValues ; Get ( RequestCount ) ) ] 

Exit Loop If [ Get ( RequestCount ) ≥ ValueCount ( $searchValues ) ] 

New Record/Request 

End Loop 

Extend Found Set [  ] 

End If 

[Vaughan]

... we want to find all other people (in this same table) who have purchased the same products that the original set of people purchased

At last, a well defined problem.

I'd create a data structure thus:

People -> LineItems -> Products

I'd script a process that uses the GTRR found set step to open a new window with the related Products. Then, use the GTRR found set step to go BACK to the People table.

Strictly speaking, this will find all the people who bought *one or more* of the same products as the people in the original found set, and it will include the original people in the found set. This may or may not meet your needs.

[peptoulcer]

At last, a well defined problem

Hello Vaughan, Sometimes when I finally define the real problem it isn't anything like I started with. Thank you all for your patience.

If the lineitems doesn't have products for a person then GTRRing back to Persons leaves that person out. Either that or I'm misunderstanding. Example is Client 4 or 10 who is in original found set. Which is why I think extend is needed. In sql, this would be easy to grab values not related and also related but I am new to this.

There is already a prime relationship elsewhere going through an invoice to a true Products table. These 'products' are unrelated line items that simply happen to have the ClientID. That is why I didn't attach products. Lineitems is all I need for this to work allow the extend.

Hello Comment, I am working through understanding your script. Our concept seems same except (upon first bare) doesn't require custom calculation and uses Get(RequestCount). I struggle to understand difference between in find using Request Count and Record Count but I am reading up.

I do not see how your script exits without ending in Exit Script. I am going to purchase the Advanced because it says it allows viewing things better, such as debug mode. I will respond again once I get my thinking clear on your script, such as I have no value list. Do I need to make one and wouldn't it include all values? Well, I get ahead of myself and will continue to study this. I am just adding your script into file but have to go feed my wife. She broke her foot and shes hollering (nicely) for soup. :)

[peptoulcer]

It now won't let me edit my post from only minute ago and kicked me out. I meant to add Thank you Comment as well. And your script doesn't require sort and that would be good.

[peptoulcer]

Hello Comment,

I see now why you used ValuelistItems. FilterValues removes the duplicates from the variable and I don’t need a custom function. Slick. Then you checked for empty variable before find and I didn’t understand. Now I think it was so it didn’t bother cleaning up variable and creating find requests if none of the clients had products. At least that is my understanding. Please correct if wrong. My brain is popping out of my head with all this stuff.

Now the find loop. I didn’t think about RequestCount. Strange that it works with Get(RecordNumber) also. I read Help but it doesn’t say Get(RecordNumer) and Get(FoundCount) will work in find. I think it is a bug but maybe because, like New Record, it works differently in different modes but it should say so. I think Get(foundCount) would count of records requests but I could not test it. Even so, I will use what you presented. I’m greatly ignorant but not blatantly stupid in that I would not take my opinion over expert on things where I am not expert.

Hello Vaughan,

I would still like to understand your suggestion using GTRR in new window. I have played with it for quite some time and although I can get the people with products in the new window back in clients, I lose those without products and when I close the window, I lose those I just found with GTRR. I do not know how to merge the windows. I know you are great programmer so I am missing some small piece here.

Hey guys, I learned a gazillion things these past few days. My hat is off to all of you who give your valuable time to help.

[comment]

I didn’t think about RequestCount. Strange that it works with Get(RecordNumber) also.

It's only strange on account of the name. But a request in Find mode is an equivalent of a record in Browse - as in the Go to Record/Request/Page[] step. So Get(RecordNumber) is actually Get(Record/RequestNumber).

Re Vaughan's suggestion: it's a much simpler process, since it can utilize your existing relationships. However, it's difficult to find out beforehand if there's at least one related product among the entire found set. But you could simply try GTRR and see if it worked - either by checking the current layout or by capturing the error.

Note also that GTRR[Match found set] can get quite slow with large found sets.