HELP?? I need a FILTERED Value LIST

[siou]

Hello, I am super stuck and I cannot figure this out..,.HELP??

So, I have a system used for fashion designers to create and price their clothing items.

I have a database named trims that details all the trims used to create an item of clothing. I also have an additional dabase that contains all the fabrics used.

These trims are notated by an identifying serial number which starts with a letter that corresponds to the kind of trim. ( B(button) Z ( Zipper).

In my main database, called costing, I need a VALUE LIST that will filter in each POP UP MENU the value list from this TRIMS database BY FILTERING that the trim starts with each letter, and then only showing the corresponding trims.

For example:

For the field named Buttons when the user tries to add a new button, the value list should look up from TRIMS the value list and then filter the choices the user sees by showing only the choices that start with the letter T.

Please help. I am super stuck, and this girl could really use some help!

Thanks for anything you can do to help me!

[Fenton]

I don't know exactly where you're stuck, whether you've got the basics of value lists yet. The basic fact of filtering via a relationship is that a key on one side must match a key on the right side (the matching is actually done on a line-by-line basis).

So, for "zipper" you need "z" on one side (Costing) and "z" on the other (Trim). The left side will be a choice, in either a regular text, or a global field (since you only make 1 choice at a time; globals are separate for each user in a multi-user situation).

For the right side, in the Trim file, a calculation field is needed. Say the field with "zipper" is named "Trim," then the calc would be:

Left(Trim, 1)

Make sure it's a Text result. Let's call it "_cTrim1" (that's just the way I'm naming things these days*).

Then in the Costing file you need a relationship from the _gTrimChoice field to the _cTrim1 field in Trim.

You can now define a value list, based on a field, related values only, using that relationship. You should then be looking at the fields from the Trim file. Pick the Trim field (the one that has "zipper" in it).

In Layout mode, use Field Format (Format menu) to attach the value list to the Trim field in Costing.

You also want to make a value list for the 1-letter _gTrimChoice field. You could either stick the appropriate alphabetic entries in a custom value list; or "use values from a field," "from another file," Trim file, the _cTrim1 field.

*("_gTrimChoice" tells me "this is a global field." The "_g" causes it to sort to bottom, leaving the real data fields at top. Alternatively, you can use the "TrimChoice

TrimCosting.zip

[siou]

Ok, so i have yet to implement this, but i see where you are going. I have done many value lists from other fields, but I do not want to use a pop up menu for the costing side.

The costing looks like this: ( being the fields)

Zipper 1 (ZADJ1)

Zipper 2 (ZLG1)

Button1 (BRD1)

Button 2 (BR2)

Button 3 (BR4

All of the values that occupy these fields are a value list that attaches to a TRIMS ID field from the TRIMS database.

All I want to do is that the Zip1 Value list, only brings up Zippers,

and that the Button value list only brings up buttons, based on the filtering of the fact that they are the B1 or Z1 field.

They need to filter by the first letter of the value.

If this is what you understood, then I will just go ahead and use the above solution. I just thought I would clarify.

Thanks so so so much!!

[siou]

Thank you Thank you THank you...I got it! I was trying to do that for so long, but I did not get that I needed to make a "z" match listing in costings...that matched exactly, I made this and then made it invisible, so that the column was automatically filtered by the z listing...

thanks so much, I really can finish tomorrow.

ami sioux

[Fenton]

What you need then is not to "choose" a value list based on the first letter; you already know what the value list will be. In that case each type (zippers, buttons) should have its own value list.

There are different ways to do this; some of which do the filtering in the Trims file itself. This uses a little more disc space, but it's the most straightforword, and doesn't litter the more important Costing file with extra fields.

In Trims, create a field "Type" (with values either "zipper" or "button" right now).

Create a calculation field, _cZipper, =

Case (Type = "zipper", Trim, "")

Another, _cButton, =

Case (Type = "button", Trim, "")

Then use each of those to create value lists for Buttons and Zippers.

In Costing, choose "value list from another file," then one of the above from Trims.

Repeat for any new Types in Trims. I'm attaching modified files.

(P.S. We often frown upon multiple fields doing the same thing, such as Zipper1, Zipper2, etc..

But if there's only a few, and you know what you're doing, it should be fine.)

TrimCosting.zip

[Fenton]

Oh, I see you went ahead and figured out the alternate way to do this, hard-coding the "z", "b", etc. in the Costing file.

I was going to do it that way, but decided I preferred doing it in the Trims file. I didn't want to add superfluous fields to Costing, which is a more complex file, with more important calculations.