Conditional Value Lists In A Portal

[Ryszard Gold]

Alright, thanks for the help on the Conditional Value list! I managed to make one that worked with my exsisting database, but for some reason, I cannot duplicate my results in the area I need it.

I tried to duplicate my relationships to the OrderLine table, but the same set-up would not work. Please, if anyone could, download the database file I have here and you'll see what I mean. The example conditional value list is Layout #7, and I need it to function line it does there, but on a line in the Orders, OrderLine portal.

LJCT_1.0.fp7.zip

[comment]

Try it this way?

LJCT_1.0.fp7.zip

[Ryszard Gold]

I only had a quick look, but did not see your changes! I see two drop downs that seem to function on the OrderDetail layout, but it has to go a litle further...needs then to select manufacture, then finally products...

[comment]

I changed the matchfields of the relationship between line items and products. If you want more choices, you will need to add more filtering TO's of the products table - same as you did with the other one.

[Ryszard Gold]

Ok, let me peek again!

[Ryszard Gold]

Ok, definatly getting there! I did what you had done, and it does indeed SEEM to work, though there are some quirks...mind having a look?

If you look on invoice 18, your filtering seems to work flawlessly, but when you get to manufacture and description drop downs, they don't always bring up the proper stuff, or to much stuff.

For instance, line 5, Selecting beverages, then water...you get Pepsi Co and Coca-Cola Co, if you select Pepsi Co, you get all of the pepsi co products, not just the water from them.

Line 8, selected portioned coffee, then Ghost, brings up Club as the only manufacture, but then brings up the entire Club Coffee inventory.

I've looked over the source excel file I used to import and made sure there are no errors there.

Finally..sorry..the Per_Unit_Price doesn't seem to want to pull through so it can calculate extended price properly. The only way I can get it to pull a value is if I have it come from the Description_Filter_OL table instance....and it pops a value in there to soon, when you hit manufacture....and it won't change once you've selected the final description.

I thank you so much for the help you've provided!

LJCT_1.0.fp7.zip

[comment]

Your method would work if all your subclassifications were unique. But when your data is such that both "Coffee" and "Tea" have a "Regular" subcategory, you must compound your choices as you drill down. After selecting a subcategory from a list filtered by category, you must look for the manufacturer in a list filtered by BOTH category and subcategory. And to select further from manufacturer's products in the subcategory, your list must be filtered by category, subcategory and manufacturer.

[Ryszard Gold]

That makes sense, though I am unable to find out where I can do that, I don't seem to find any further options in the Value List windows, or is this something I have to do in the Tables Relatiosnhip?

[comment]

The actual filtering is done by the relationship - the value list just uses the result.

[Ryszard Gold]

Can you give me an idea where to start? :-)

[comment]

Take a look at the Relationships Graph.

LJCT_1.0.fp7.zip

[Ryszard Gold]

I've tried changing the relationship, but I admit, I'm still lost! I have tried linking the manufacture field of one Products table instance to another instance of the same table then to Orderline, but it still does not seem to filter it, getting all manufactures.

I'm still very green at this, so I'm sorry if it's like talking to a brick!

[Ryszard Gold]

DEAR GOD THANK YOU!

For real, thanks!

I see how that all works now, I may be slow on the uptake, but eventually this will all make sense!

[Ryszard Gold]

You know, I really wish I was done this, and I wish I was capable of doing this myself! This should be very simple, but for some reason, the simple wais simply not working!

Because of the hierarchal menus, it will not pull through or display my Order_line_price from the Products table, with that, it is supposed to calculate the extended price based on that times quantity.

For some reason, the only time I ever managed to get a value in there, it was bring generated before you selected the description drop down...it was taking the first price from the list of Categories, Subcategory then Manufacture.

Play with the invoices a bit, and check the OrderLine_Price for the same item in products, you'll see what I mean.

This should be a simple Auto Value look-up no??

LJCT_1-1.0.fp7.zip

[comment]

AFAICT, you are selecting the category, the subcategory, the manufacturer, the description - but you never select the actual product (by selecting the ProductID_pk and entering into the ProductID_fk field).

BTW, you should lookup the price into a local field in OrderLine, instead of using the related price from Products. Prices change from time time, and you want to "freeze" the current price at the time of the sale.

[Ryszard Gold]

That I know, you may have been seeing my "try bloody anything, just make it show up!" thing.

How do I go about populating the ProductID-fk then? Do I have to add another instance of the table? Please keep in mind I'm still green at this, so I need a little meat with my potatoes of instruction.

Can you describe how I do this?

[comment]

It's just more of the same. It doesn't matter how many selections you make to narrow down the choice - eventually, it all must lead to the final selection of the actual product.

Note also that the actual product is selected by its unique ID, so all the previous choices become irrelevant and are not needed for the final relationship.

[Ryszard Gold]

I can understand what you mean, but now how you do it. Please describe what fields I would use, or what relationship I would alter. I'm sorry, I understand the problem, but not the method.

[comment]

Come now, surely you will not let us fail at the very end? You see that at every stage you restrict the related set further and further, and use that to define the value list for the next selection.

So now just take the relationship that combines all of your narrowing selections, and use it to define a value list of ProductID_pk values from that TO. Add the ProductID_fk field to the portal, and attach this last value list to it.

[Ryszard Gold]

I'm pretty sure I did what you asked, made another instance of the table, and linked them as before but with one more relationship to product ID.

I didn't seem to work.

I checked that I had the order correct, seemed to be. I noticed it was forcing a one to many relationship because of the auto-serial trait of ProductID.

Have a look and give me another hint?

LJCT_1.4.fp7.zip

[comment]

Have a look at the notes in the graph, and notice the changes in some field definitions.

LJCT_1.4a.fp7.zip

[Ryszard Gold]

Hey, that is exactly what I had going on at the very frigging beginning! When I didn't have need of thinning out the selection, back to basics and I didn't even see it! Forest through the trees kinda thing!

Thanks again, and thanks for explaining, that helps!

I'm sure I'll have more in the future, but thanks so much!

[Ocean West]

Now the next thing to add to this technique is to clear fields to the right when its parent item changes so that you don't end up with miss matched elements.

If you change the category the sub-cat, mfg, and product all go null.

-

here is the technique: http://www.filemakermagazine.com/videos/hierarchical-value-lists-dependent-value-lists.html

And then if you want to limit how many of any one item in the portal you could adapt dwindling lists http://kevinfrank.com/demo-files.html

[comment]

Tsk, tsk. Both of these techniques are available on these forums, Stephen.