4 Replies Latest reply on Sep 3, 2014 6:55 AM by philmodjunk

    Create individual Value list from One Record/Field



      Create individual Value list from One Record/Field


      For my invoices I'm looking to create a Value list one I enter the stye number which than looks up my products color choice field and populate a value list to the correct colors.

      We are a special order dress shop and majority of our items have separate individual colors and change most seasons and I am  trying to eliminate ordering the  wrong color for a dress that dose not come in my color choice.

      My Typical color choice record  looks like (red, green, blue, black)

      Some can be only made in one color and some up to 80 color.

        • 1. Re: Create individual Value list from One Record/Field

          OK, someone may come up with a way to do this via an SQL calculation, but I'm going to do it "old school". Basically what you have is that there are colors for any particular dresses (items). ( Is different colors for "item" or is for "style"? ).

          In any case, the basic method is to create a Table for this. The fields would be: ItemID, Color, Active. You would need a new record for each possible color for an Item. [ The "Active" is not required, but would use useful if sometimes a color is just temporarily not active; otherwise ignore it. It should auto-enter as a 1 (number).] 

          It may seem like some work to create these. You would definitely want a plain Value List of all the colors, so you could chose one for each record. It could also be scripted, I suppose; they you'd just fix the ones that are different than "normal" (if there is such).

          Once this is setup, it is pretty simple. Create a Relationship Graph "table occurrence", from Items to the above table ( I'll call it "ItemColors"). The match would be on:
          Item::ItemID = ItemColors::ItemsID

          Create a Value List, which uses this relationship, with the Color field as the 1st field.*
          (•) Include only related values starting from: ItemColors

          The Value List will show only the colors which you create records for that Item.

          * You could also have a Table of "Colors", with an auto-entered ID, with a record for each Color; to use instead of the name everwhere. But likely the colors do not change their names, so this is likely unneeded.

          P.S. If you wanted to use the [ 1 ] Active records, than add that to the relationship also. It would be a 1, but it could be shown as [x]. But how to do that is another post; or maybe you can find a post about this.

          • 2. Re: Create individual Value list from One Record/Field

            I recommend that you take a look at Adventures In FileMaking #1, a free to download file on my FaceBook page. It is a FileMaker file with 8 different examples of conditional value lists--the type of value list that you are describing here and each example comes with detailed and extensive documentation, how they work and how they were set up.

            Caulkins Consulting, Home of Adventures In FileMaking

            • 3. Re: Create individual Value list from One Record/Field

              Thanks for the answers to my questions, Phil your Adventure in filemaking is a very resourceful guide.

              This is what i did and it works perfect.  Tell me what you think.

              What I did was I made a Calculation field with this Proctucts::LIstColor=Substitute ( Products::Color Choices ; ", " ; ¶ )

              which made this in a list format,then went to my related table/field, Invoice Data::Color  and Made a value list using the Proctucts::LIstColor, and check the "Include only related values starting from "Invoice Data Table"

              • 4. Re: Create individual Value list from One Record/Field

                That works. In many cases, however, there are advantages to creating that list in a related table rather than as a list of values in a field.