Value list of a value list of a value list

[sicSRT8]

I am trying to figure out how to get select data based off 2 other drop downs. I get as far as seeing the list but need it more selective.

Here is what happens.

I select a customer from a drop down of current customers. Once that is selected I select from another drop down the types of projects available for that specific customer. In the final drop down I select the material (Serial Number) used for that project. But right now it shows all of the materials (all serial numbers) for that customer in the drop down list. However I only want the specific material (serial numbers)for that job.

Material is listed in Inventory Table (Carries a 3 digit Alpha code specific to the type of jobs Example: CAA, CAF, CAB, CAM, CAI, CAE)

Customers are listed in Customers Table

Customer Jobs are listed in Customer Jobs Table

The drop downs are located on the Job Entry Table. and pull from value lists that have specfic relationships.

My main confusion is how to get a specific inventory item by serial number to show up for the specific job selected. I hope that was specific enough to figure out....

Thanks in advance

Edited August 21, 2009 by Guest

[bcooney]

Seems that you need a join table of CustomersProjects. How do you filter the "projects available for that specific customer"?

So, in Jobs, you'd have _kF_CustomerID. Then, you'd build a conditional value list using a relationship to the CustProj join table to pull ProjectIDs, in order to populate Jobs::_kF_ProjectID.

Once Jobs:_kF_ProjectID is populated, you'd use a conditional value list looking to PrjMaterial which is a join table of Projects and Material (bcs aren't certain Material used in many Projects?).

[sicSRT8]

I think that's what I have. but when I select the specific inventory item (3rd drop down). It shows me all the ones related to the first drop down not the second.

I wish I could show you what im trying to do but don't want it out to the free world...

by the way whats the _kF_ signify?

Edited August 24, 2009 by Guest

[bcooney]

I use _kF_ to start all my foreign keys. Here is a demo of a three level condtl value list.* That is, first choice narrows second choice which in turn narrows third choice.

*I tried Comment's technique to clear the value if the choice changes, but cannot get it to clear the State if the Continent is changed.

condVL3_clear.fp7.zip

[sicSRT8]

WOW.... Thank you that's exactly what I needed. I was way off on my relationships. I think I was confusing myself with my version of naming convention.

Thanks for the sample guide!

[sicSRT8]

I went thru that example and understand to the extent that its different form than what I have.

As of this post I can select a "Continents" (Drop 1) then it narrows down to "States" (Drop 2) then narrows to "Cities" (drop3) However I get all the cities for that continent not state.

In my list of cities I have UPC's instead of serial numbers. They are pulled from Excel and not auto entered in the list like the serial naming each item.

CAF90012

CAB80089

CAM80083

CA represents "Continent" and "f","B","M" represents state and specific item number...

I guess my issue is relating the 3rd letter to "states" and having just that type of item number to show up.

Does that even make sense?

I tried renaming your example to make it more clear but it appears that each state has to be entered to show its related to cont, and country which in my case isnt going to work like that.

Right now I do see the list of items but it seems to show all for the 1st drop down and doesn't narrow down from the 2nd.

I have tried relating this any way I possibly can but still stumped.

[bcooney]

It sounds to me that you are attempting to do condtl value lists without the tables and relationships that are necessary.

[sicSRT8]

tyring to figure this out. My serials are classified as CAF CAB CAM CAA so if I want to limit them in a calc is this possible?

Curently using this

Customer_jobtype = item_id & "A"

But can I add more letters to this calc like below

Customer_jobtype = state_id & "A","B","M"

Im not sure how to make this calc with additional letters.

Update: Im so confused how I can get all the types for the customer but not the job type...

Customer NV has 6 different job types....

They show up and then on 3rd drop down I see material for ALLLL job types.

Inside "Inventory" table I use a calc to grab just the alpha characters from the upc code.

Inside the "Job types" table I manually entered the 3 digit code for each type of job.

So c_alpha (inventory)=3digit_code(jobtypes) in relationships.

In the same relationship chain for the job types I have customer=customer on (Job Entry)

So for the drop down I am using inventory upccodes starting from job entry. That comes out to giving me all upc codes related to customer not job type.

Am I missing something? Sorry to write out like a 3yr old but I cant figure this out and starting to feel like one.

Edited August 26, 2009 by Guest

[sicSRT8]

I did notice something. the field that the drop down is located in should be be a calc or anything other than a text box?

Thanks for you help......Again

[sicSRT8]

Ok I made a sample of what I have and getting the same response...

CondVL3.zip

[bcooney]

Anyone is welcome to offer other ideas here. I'm really not following sicRTR8's job code dilemma.

[sicSRT8]

Ok looking at your example. In the State table the IDs are set by FM using the serial number +1. The state table is the inventory table on my DB.

Instead of typing in states and having it set an auto serial I auto import from excel and is set to date specific material. So the state list looks like this:

5000 NewYork

Mine looks like this

8/28/2009 CAA89993 1000

8/28/2009 = Inventory Date

CAA89993 = Upc Number which is the "State"

1000 = Qty

CA = Customer

A = Customer Job Type

89993 UPC Number for that item.

So when you select customer CA and then select job A all items with CAA show up. But for some reason I am getting every item for that state and cant get it to show just items startign with CAA.

I am using the ABCDEFG filter in inventory since there are literally hundreds of items for each customer job. I am hoping this helps clear up some confusion.

But using your example it seems you have to manually enter in the serials for them to align. The example I posted shows the setup I have and the relationships I am using. Anyways thank you for your help I'm not giving up as I know there is a solution its just a matter of figuring it out.

[sicSRT8]

Yikes.... this cant be a good sign no ideas... Where is the smiley for pulling out your hair?! K well back to drawing board then.

[bcooney]

I might be up to tackling this again. The key difference, as I see it, between what I've done and what you are trying to do, is that I have only one fact per field in my system. You have "CAA89993":

CA = Customer

A = Customer Job Type

89993 UPC Number for that item.

That's three facts in one field.

Perhaps if you parse out this field into 3 fields, then try the condtl value list.

[sicSRT8]

You mean have three fields one filtered to CA the next filtered to A and the third filtered to the numeric digits?

So my Filter "ABCDEFGHI.... to grab the CAA from the UPC code and then relating it to the job codes set in the job types table isn't enough?

I just cant figure out or understand how I am getting all the types of material for the customer no matter why job type I select. I thought if I could figure out how I am getting that response I would figure out how to change it.

[sicSRT8]

Im thinking am having issues with my 2nd drop down not being able to filter the last drop down. Everything I try and do Im getting same results.

[bcooney]

Noodled around a bit. How's this look?

CondVL3.fp7.zip

[sicSRT8]

LOL after a week and a half of spending days playing with it I figured it out. Ill post what I have and look at yours by tomorrow.

I have never yelled YES! in the office until now.. I was determined to figure it out!

Of course its right after you posted the solution... However my layout is different than yours.

One slight issue is on the job entry page you can select a date. The UPC QTY should be for that specific date. Inside Inventory each item is dated for that qty. I do this so I can go back days before to see a specific qty for that day.

But yours may be the best solution as I can relate the date & upc code from "jobentry_inventory"

Edited September 1, 2009 by Guest

[sicSRT8]

One more thing bcooney.... Thank You! I reeeeeeallly appreciate it!

[bcooney]

You're welcome. Revised using Date relationship for Qty.

CondVL3-1.fp7.zip