6 Replies Latest reply on Sep 29, 2014 7:54 PM by BrianChase

    Value Lists

    BrianChase

      Title

      Value Lists

      Post

      Is it possible to create a value list based on several fields in a given record if and only if another field in that same record equals a certain value?

        • 1. Re: Value Lists
          philmodjunk

          What you describe sounds like a type of conditional value list. Can you describe in more detail what you mean by "based on several fields"?

          It's possible to use a calculation field that combines the data from several fields in order to produce a single value for a value list. This is often used, for example, to combine first and last names or even the names and an address or some other third field as a way to produce a unique second field.

          It's also possible to combine several fields such that each field's value is a separate value in the value list also.

          I've created two instructional files that cover these concepts.

          You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

          Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
          Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Value Lists
            BrianChase

            Sure.  But maybe it's better to describe what I'm trying to accomplish though it brings up all new issues too.

            I have a Contacts table on which people are listed.  I have an Assets table on which mobile assets are listed.  I have a relationship setup via pk and fk ID that is an auto generated serial number to show that a Contact owns an Asset.  However, I also want Contacts that do NOT own assets to be able to be put here that don't own an Asset but want to own one of the ones we have in our Asset table.  So many Contacts are "Interested In" (thats the name of the field I'm trying to create a value list for) buying one of the Assets.  But I want that value list not to be populated by ALL Asset records, just specific ones that have a field that indicates one out of six possible values which describes the availability of the Asset.

            If you're following anything I'm saying here I'd be AMAZED... I hardly know what I wrote.  

            Ideally this gets accomplished via portal but because the relationship already exists, if I say a Contact wants to buy an Asset, the program just thinks I'm trying to say that the Contact owns the asset.  So that doesn't work.  At least not in the relationship I setup.

            Bonus points: Then the "Interested In" field, since it's now populated by the value of several Assets should be able to be buttoned to go to that Asset on the Assets page.

            Oh, and to make matters worse, the Interested In field needs to be populated by a calculation of several fields on the assets table, not just one.  The calculation field being an easy one to make, but it's not editable from the other side of the portal.

            yes   <---Way up.

              

            • 3. Re: Value Lists
              philmodjunk

               But I want that value list not to be populated by ALL Asset records, just specific ones that have a field that indicates one out of six possible values which describes the availability of the Asset.

              That sounds like what I call a "hardwired conditional value list". Unlike a typical conditional value list, you don't have the user select a category and then show values that are a member of that category's group. Instead, you select the category for them. There are two different ways to produce that value list demonstrated in Adventures In FileMaking #1.

              Then the "Interested In" field, since it's now populated by the value of several Assets should be able to be buttoned to go to that Asset on the Assets page.

              While you could list multiple Asset ID's in the same field to list those for which the contact has an interest, it would be much more flexible to set up a related table and a portal to list all such assets so that a field in this related table is an _fk to just one asset in the assets table. Go to Related Records can be used to take you to just one of those related assets or a set of all those asset records.

              the Interested In field needs to be populated by a calculation of several fields on the assets table, not just one.  The calculation field being an easy one to make, but it's not editable from the other side of the portal.

              But you can edit the individual fields that the calculation references as a way to change the value returned by this calculation...

              • 4. Re: Value Lists
                BrianChase

                Okay, had a chance to look at your file.  Awesome stuff, very in-depth, very helpful... problem is, I'm having a hard time interpolating it for my own uses.  (No surprises there) It did give me a chance to come up with a potential alternative.  But I still don't know how to enact that alternative either.

                So there is field A; this is the field that contains 6 values from a good-old boring drop-down, one of which is the "special condition."  This "special condition," once selected should then use most (if not all) of the values in that same table to create an all new table of identical fields but which only has the records that meet the "special condition."  A summary field on that table is then used as the field which populates the value list that I'm so desperately seeking.  Maybe?

                 

                • 5. Re: Value Lists
                  philmodjunk

                  There seems to be some misconceptions in what you post or maybe just not using quite the right terms:

                  So there is field A; this is the field that contains 6 values from a good-old boring drop-down

                  But the field would not contain 6 values, your value list would list 6 values.

                  This "special condition," once selected should then use most (if not all) of the values in that same table to create an all new table of identical fields

                  Creating a new table dynamically in FileMaker is barely possible and not at all useful for what you want to do. The new table would not be linked to any existing tables in relationships nor would you be able to create a layout for the new table. So this isn't really a practical option for what you want to do. BUT you can create a set of records in an existing table that all have a common value so that they can be treated as a group of records. My guess is that this is the approach you might consider for what you want to do.

                  • 6. Re: Value Lists
                    BrianChase

                    Yeah, I'm pulling the plug on this one.  This may be the saving grace but burnt out on it for now.

                    BUT you can create a set of records in an existing table that all have a common value so that they can be treated as a group of records. My guess is that this is the approach you might consider for what you want to do.

                    Many thanks regardless.  New topic coming soon!