Multi-Selection and Storing of nested selected values

[#1]

TEAM01	CARTERS	DANNYDANNY
TEAM02	JOHNSON	JOHNYJOHNY
TEAM03	HALLS	STEPHENSTEPHEN
TEAM04	WILLIAMS	WALTERWALTER
TEAM05	SMITH	TOMMYTOMMY
TEAM06	HANSON	CAMERONCAMERON
TEAM07		BOBBYBOBBY03
TEAM08		KATHYKATHY03
		DANNYDANNY01
		JOHNYJOHNY45
		STEPHENSTEPHEN05
		WALTERWALTER13
		WALTERWALTER456
		TOMMYTOMMY24

I am trying to do a select of data using the following excel spreadsheet. What I am trying to do is to find all of the children that are linked off of a parent relationship. Using the following chart, the second column represents all of the possible parents last names. The third column represents children. Each of the people in the third column have children if their character name is greater than 10 positions long.

What I am trying to do is to find out for example all members of Team04 which are part of a particular family. Team 4 happens to coincide with the Williams as the parent. The problem that I have is that Mr. and Mrs. Williams had three kids. Then the three kids had eight kids. Problem is that one of the kids had a divorce so some of the Williams grand kids are linked off of a different Team number so I will have to go back and find the kids that are linked off of Team08. I do not know this though until I had clicked on Team 04 which had a child with 03 at the end of his/her name which indicated that they were in a Divorce with offspring.

Question that I have is how do you capture selected data when you loop through like a pivot table for selected information. I need to perform several selects through a pivot table or multiple list boxes but I need to capture the selected data and save it off as I am going through each selection process. These selections will be needed in order to find the end result.

Any help will be appreciated.

[vgutkovsky]

If you have a finite number of teams, you would save yourself a lot of trouble by setting association flags in the script rather than trying to do everything in the front end. Then some simple set analysis would give you the full list in the front end (and performance would be a lot faster too). In your case, there are a limited number of scenarios that would result in someone being associated with a family: birth into that family, or marriage. You should be able to run through those pretty quickly in the script using nested ORs. If you need a more detailed answer, you would need to post some sample data.

Regards,

[#3]

Package Code Group Control Number Part Number Label

AB1 GB7564 IH3241 Intake Hose

AB2 GB756487390 IC9845 Intake Clamp

AB3 MJ9384 RB2413 Rotary Bolt

AB4 MJ938765423 CI9265 C-Clamp Intake

AB5 KM9282 OH2845 Outgoing Hose

AB6 MJ938467 LI1845 Level Indicator

BA1 MJ9384672 WB9127 Wing Bolt

BA2 GB756423 RH4294 Right Hydraulic Line

BA3 GB7564238 IM9824 Intake Manifold

BA4 KM9282654 CH8245 Carburetor Hose

BA5 KM928261 AF4215 Air Filter

Here is a list of the sample data. I need to get all of the parts that are linked with a specific type of vehicle. Package Code is the overall item linked with the vehicle. Each package code has a list of Group Control values that makes up the vehicle as a whole. The user should be able to click on for example AB1 from a List Box and a list of values comes back with a list of Group Control Numbers which make up the vehicle. If you look closely under Group Control Number, you see GB7564, GB756423, GB7564238, and GB756487390. If an item has exactly 6 letters in it, it may be the Head of Family Group Control Number. The only way to verify this is if there are other Group Control Numbers that match on the first 6 characters and their overall length exceeds 6 characters then a head of family exists. We then establish that as a parent/child relationship. GB7564 has 3 children (2 are direct children and 1 is a grandchild) but we refer them as having 3 children. GB756423 is considered a parent also with a child named as GB7564238. The child must have the same preceeding characters as the father. GB756487390 is a brother to GB756423 because they have the same Father as GB7564 and match accordingly. Reason why this gets complicated is because some parts are linked across other vehicles. We have to match all of the package codes to where a Group Control Number may be used at in which case some of the parts used to make up a particular Group Number may be associated with a different Package Code Value. Some of the sub parts within a Group Control Number may have a different Package Code Value associated with it. If it does then I need to get all of the Group control Numbers Associated with the Package Code and check their Parent/Children Relationships and display the data accordingly as one List Box or Pivot Table.

[vgutkovsky]

Try this:

LOAD
 [Package Code],
 [Group Control Number],
 [Part Number],
 Label,
 if(len(trim([Group Control Number]))=6,
 [Group Control Number],
 if(left(trim([Group Control Number]),6)=left(trim(previous([Group Control Number])),6),
 peek([Parent Code]),
 null()
 )
 ) as [Parent Code],
 if(len(trim([Group Control Number]))=6,1,0) as ParentFlag
RESIDENT data
ORDER BY [Group Control Number];

You will then have an association of all parts that have the same parent code. You will also get a flag that will tell you which one is the parent. I wrote this freehand and haven't tested it out, but it should work.

Regards,

[#5]

i attempted to run the suggested load statement. I encountered an error and had to end up removing the Order By statement because of not being able to use Resident statement in the load logic. I did get the load statement to run. The logic did appropriate the linking with the 6 character parent but parents may exist beyond the 6 character mark as long as there is a child that matches on the parents preceeding characters. In the above example GB756423 was the parent to GB7564238. Another way to describe the Package Code is to think about a certain section of the vehicle, Ie. Engine, Transmission, Steering area, Rear Seats, Drive Assembly, etc.. I mentioned earlier about vehicle but it should be specific vehicle section. When the user comes in to this task, they can either search by clicking on Package Code to get all of the associated Group Control numbers, Part Numbers, and Labels or they can click on a part number and see all of the labels, part numbers, and group control numbers linked with the package code and also the package code value(s).

In the above mentioned table, user needs to be able to see all parts linked with package code AB1 as an example. AB1 may group control numbers GB7564, Mj9384, and KM9282654 linked with it and also the Part Number and Labels. After that, user needs to see if any children are associated with the group control numbers (also referred to as children). If yes we need to display all of their data as well and keep on filtering down until there are no more children (and associated part numbers and labels). I need to display all of this data to indicate that for a given selection, these are the components needed to complete a task for the original Package code or Group Control Number value chosen. In the shop that I am in, we cannot use zero foot print for AJAX and we cannot use any VB and also no plug in for internet explorer.

Any further help you can provide will be greatly appreciated.

[vgutkovsky · Accepted solution]

Umm, without the Order By logic, it won't work at all so you should try to get that part fixed. You mentioned in your 2nd post that even though it may be a parent-child or a grandparent-grandchild relationship, we will always treat it as a parent child. So I suggest you use the script logic I gave you above, but modified as so:

table1:
LOAD
 [Package Code],
 [Group Control Number],
 [Part Number],
 Label,
 if(len(trim([Group Control Number]))=6,
 [Group Control Number],
 if(left(trim([Group Control Number]),6)=left(trim(previous([Group Control Number])),6),
 peek([Parent Code]),
 null()
 )
 ) as [Parent Code],
 if(len(trim([Group Control Number]))=6,1,0) as ParentFlag,
 len(trim([Group Control Number])) as [Group Control Length]
RESIDENT original_data
ORDER BY [Group Control Number];
table2:
LOAD
 *,
 if([Parent Code]<>previous([Parent Code]),
 1,
 if([Group Control Length]=previous([Group Control Length],
 peek(Level),
 peek(Level)+1
 )
 ) as Level
RESIDENT table1
ORDER BY [Parent Code],[Group Control Number],[Group Control Length];
DROP TABLES original_data, table1;

That way you have a Parent Code that will tie individual parts together, and you will have a Level that will indicate if it's a grandparent (Level 1), parent (Level 2), child (Level 3), etc.

Regards,

[#7]

Thanks for the logic. I finally had a chance to try it out. It works great. I was away from the computer in training last week. Thanks again for the help and sorry for the delayed reply.