Conditional Value List

[sicSRT8]

Thanks...

Now that that working I have one difficult question related to topic but a spin off. I want to be able to select items from the imported list but limit what can be picked from based on what customer name shows up in another field.

So If I selected Jim Bob as a customer from an existing drop down (Fields from customer table. In the roll selection field drop down I only want the rolls starting with CF to appear in drop down list that was pulled from the excel file.

roll_id qty_avail

CF1001 1000ft

CB1002 1000ft

MF1001 1000ft

MB1002 1000ft

[Fenton]

I split this off to its own Topic, as it no longer has anything to do with importing (other than that the values came from an import). What you want is called a "conditional value list" or a "relational value list". It is produced by creating a relationship (or self-relationship) based on the first field, which produces only values matching that relationship.

If you then use that relationship in a Value List definition, choose the field (also supports a 2nd field), and Start From the context of where you want to filter from, then only values matching the relationship will show in the drop down.

It's kind of a lot to grasp from the above short description. But do a search on the terms, and you will find lots of posts. Unfortunately I don't have any simple examples (though likely it's in every solution I make). The question comes up in some form almost every day :-]

[sicSRT8]

Good info...

I didn't realize this post was moved and re-posted but took it down off the other topic.

Thanks for you help!

Edited February 27, 2009 by Guest

[sicSRT8]

Im trying to get this but its not happening. I have been going thru other posts but nothing relates to the example i need unless im just missing the concept. I feel like im getting more and more confused.

This shouldn't be that difficult. But somehow i managed to make it difficult.

Jim Bob is = to Inventory Items that start with letters JBF and JBB Ex:

Roll_ID Qty_Avail

JBF1000 1000

JBF1001 1001

JBF1003 1002

JBB1000 1003

JBB1002 1001

Thats how the data is populated in inventory lists with two header names.

There are three fields

Customer Selection

Front Laminate Roll Used

Back Laminate Roll Used

All of these fields are based on what customer is selected.

Front Roll Field will only show a popup with related roll_ids to customer that have JBF using the 3rd letter as the guide for front and same goes for back using JBB.

Basically the rolls indicate what type of roll they are by id numbers and are only used by that specific customer so JBF and JBB cant be used by any other customer and JBF is for Jim Bob Front Roll Selection Field and JBB is for Jim Bob Back Roll Selection.

Our inventory is material specific to the customer which is why im trying to limit options once customer is selected. Each Job entry is entered in by customer and the material used. We then take that report and update our on hand inventory and send reports.

Im just hoping that someone is able to show an example or something as to how to set up in FM. I feel I'm making this harder than it needs to be by trying to break down other solutions to see how things are related. Or i am completly lost as to how to do this. I have even watched the lynda videos on 9 and 9 Adv and 10... Im not giving up.

[Fenton]

You must get the "JBF" and "JBB" into a field of its own. Otherwise it cannot be used as the originating field of the relationship you would need to filter a value list to only values for one of them.

You should really have them as separate fields in the original source data. Combining them with some other data is known as a "multi-valued" field, which goes against some basic relational database rules. That's not to say you could not have it combined, it's to say you should also have it separate, and that should be where it starts.

But, you could also also separate it later, using a field with a calculation like: Left (that field; 3), if it is indeed always 3.

[sicSRT8]

I just wish my list was split up that way. I could manually do it in excel but the guys who will be entering in the info wont be able to do it.

Basically we are trying to have the list automized in the morning so when they start FM the script will auto grab the avail list.

I'm curious what if I make specific inventories for each customer and only grab the items belonging to that customer. My only issue is that I have 4 different items per customer EX JBF, JBB JBA, JBC. So now im thinking it wouldn't work unless I made a table for each item inventory which im guessing is bad design...

Unless I can split the list in FM? Then each item field would just pull from the specific field based on customer

So on the layout Front Roll would it just be roll_ids that are related to that customer with the 3rd letter in the id being an (F) same goes for Back Roll being a (:?

[Fenton]

Oops, I didn't notice, you have different length prefixes. Well, that's easy enough, because they are letters, whereas the rest of the id is numerical. So you can use the Filter() function.

[P.S. Look at the Value Lists.]

Rolls_VL.fp7.zip

Edited February 27, 2009 by Guest

[sicSRT8]

I see how that is laid out but back in the example I attached i added the table customers.

So inside your select layout you select a customer from the customers in the customer layout and based on that selection the front_roll_id will only let you select rolls for that customer. So selecting Jim Bob then picking a front roll only starting with JBF.... and then selecting the back roll related to Jim bob that starts with JBB.

so even though all rolls with JB in front of them signify Jim Bob how do you limit the results already for that customer.

I added the boxes so you get the idea but didn't set any relationships to them. Customers has the customer names and trying to figure out how to specify them to the rolls then based on that limited results per field.

Rolls_VL.zip

Edited February 27, 2009 by Guest

[Fenton]

I see. I didn't notice that the "F" and "B" meant Front & Back, and was not part of the customer ID. It's obvious when you look at it. This somewhat illustrates the difference between the way database developers and spreadsheet developers think.

While I can see that you would want this info display together in the roll ID, a database developer would have the data in separate fields in that table, then concatenate them together only for printing, etc. (maybe just put 'em side by side). So we would not have to parse it out. We would still need to do the below however.

In any case, what you need are 2 value lists, one for front and one for back. We will have to tack the "F" and "B" onto the Customer ID, and use that as the basis of each relationship.

Rolls_FB_VL.fp7.zip

[sicSRT8]

One thing when you select the front roll it shows the JBF in the Back roll field also. Same thing when you select the back roll it changes the front roll.

[Fenton]

Oh, right, you need a Roll_ID_B field and a Roll_ID_F field. Then attach the appropriate Value List.

Rolls_FB_fej.fp7.zip

[sicSRT8]

Ok so I took this and thought I converted it to my db correctly. I am having one issue

When I select the "roll 1 id number" after I select customer I get nothing.

When I checked the Value List if I include all fields then I get the drop down of the complete list in inventory. If I set to include only values starting from the page the field is on then it comes back blank.

[sicSRT8]

the calculations are showing up on the fields for which customer i select just not the specific value list of calculated items...

Im also noticing that in my inventory list under the z_cCust_ID is not auto populating. But when i click on each box it shows the correct ID like JBF and JBB. I have to be missing a step for it not to populate into the field. I highlight the box and it shows the abbreviation but when i move cursor out of box it goes away...

Edited February 27, 2009 by Guest

[sicSRT8]

In your example z_cCustID has the fields appear automatically to eliminate the numeric characters. In mine they are blank but when i put the cursor inside the field it shows the calculation but disappears when i click outside the field. Any reason why this would happen?

[Fenton]

I think you're going to have to upload an small version of your file. There are so many small things that can go wrong. It could be that you have wrong result type for the field.

[sicSRT8]

One last thing when I want the feet of the roll selected do I relate the inventory qty "feet" to Front Roll Numbers or to Inventory?

Not shown in this topic was I had number selected and should have been text Fenton found it.

UPDATE: I made a separate instance of inventory for each Roll1, Roll2 Roll3 and have them pulling from invt1 invt2 invt3. Is this how it is supposed to be done? It works but wanted to see if im making a mess of relationships

Edited March 2, 2009 by Guest