6 Replies Latest reply on May 8, 2011 9:48 AM by FlipFlop

    Dynamic List

    FlipFlop

      Title

      Dynamic List

      Post

      Hello Everybody,

      appreciate your help for a solution of the following problem.

      Table 1 has only one text field called "Item". The entries are unique, e.g. A, B, C, D.

      Table 2 has a text field called "MyItem", and I use the entries of Table 1 as a value list to make a selection for an entry in Table 2.

      Now, it might happen that an entry in table 1 is missing, say 'E'. I would like to enter an 'E' in table 2 and ensure that this new entry is also copied into table 1 as a new entry. I made several attempts with relationships and references. Both do not work. 

      Thanks for your help,

      FlipFlop.

        • 1. Re: Dynamic List
          LaRetta_1

          You are using a table as a value list and that is a good way of handling it.  But it means that you must add a record into the Items table to add a value to your value list. 

          This brings up the question on who should have authority to add new items. Please answer the following questions:

          1) How do you determine who can add a value?  Are you using using Security with privilege sets or do all users have the same privileges?
          2) What FM version are you using?
          3) Do you want it so that, if a user enters a value E that it, behind the scenes adds the value?  Or do you want the User to be asked if they want to add the value?

          So a bit more of the context during data entry and user rights will help here.  Smile

          • 2. Re: Dynamic List
            FlipFlop

            Thanks for your questions. Helps me to better understand what is needed to answer my question.

            1) I am not using security with privilege now but want to do it later. As of today, I am in design phase and the only user. Basically, table 1 shall serve as a dynamic repository "behind the scene". 

            2) I got FileMaker Pro Advanced 11 a couple of days ago. I am a new user to FM but have a bit of a background on relational databases.

            3) I want it if the user enters a value E that it, behind the scenes adds the value to table 1. In case he enters an existing value I want to give an indication that the value he want to enter already exists.

            • 3. Re: Dynamic List
              LaRetta_1

              1) I would suggest designing for what you want now.  You will learn as you go and you can modify or even replace this first test solution down the road.

              2)  Your understanding basic relational will help a bit.  But in your Item ( value list) table, do you only have the A, B etc or do you also have a unique, auto-enter, FM-generated serial number?  You need a serial in every table you create.  This serial is how you relate two tables.  So Item should have a field called ItemID (number) with auto-enter serial increment by 1.

              But for now, if all you have is a single field in Item, why aren't you using a custom value list?  Usually tables are used for value lists when the value has a description and by inserting the serial, the description can be changed without having to replace the actual data throughout.  But with only one value A, B etc, this wouldn't be necessary.  Or did you just use these terms for an example?

              3)  If you are using a value list on that field then the User knows that the value already exists because otherwise it wouldn't appear in the pop-up for them to select, right?  And if using a pop-up, they couldn't add a new value anyway (if based upon another table).

              Before I provide a method of creating new values in another table, I would like you to walk through what I've said and give me any input you wish.  Also, I have no idea how large your final solution will be (number of tables, purpose of the solution, type of design) so anything you can say which gives me perspective into what you are creating will help as well.

              • 4. Re: Dynamic List
                FlipFlop

                3) Yes, agree but a user might not always properly read what comes with the drop-down list Laughing

                2) I understand the concept of serial number and will add it to table 1. I used the terms A, B, C just as an example. Table 1 is supposed to hold a list of keywords and I want to make sure that each keyword is definetly unique and existis only once in the entire data base.

                Before starting the development of the data base, I want to test a bottom-up concept which supports the idea of normalization.  Imagine, you want to categorize the keywords as a next step. So, I want to implement a second list holding the categories the same way I did with the keywords. Call these simple lists of categories and keywords "level-0-tables". Level-0 because such a table just holds a serial number and the entry. Next step, I want to create level-1-tables out of these level-0-tables, e.g. build a table with fields "MyCategory" and "MyKeyword" and a serial such that I can assign each keyword to one or more categories and each category to one or more keywords, i.e. to build m:n relations. I can use such a level-1-table for cascading value lists to be used in level-2-tables, and so on. The concept shall help to ensure that each entry is unique and gives the flexibility for higher order m:n relations.

                First trial of the concept is to build a simple information data base, i.e. to store different type of files (PDF, eMail) in a table with a serial, a media-field, a text field for comment, and up to five categories and keywords for a proper "metatagging" of the stored file. Ideally, I would just use a link to the file since the file is stored anywhere in my file system.

                • 5. Re: Dynamic List
                  LaRetta_1

                  In FileMaker, the multiple tier value lists are called conditional value lists and you can search for specifics on how to set them up.

                  I'm not sure what you mean that a user may not properly read what comes with the list.  If you use pop-up instead of drop-down, you can control what they select to only valid entries. Drop-down allows entry of invalid values as well.

                  In general, if the value entered is not valid, you can use field-level validation to tell the user.  They would then click a button which would take them to layout to enter a new value and then return them so they can make the selection.

                  All of this is much easier using vs. 11 because you could use script triggers to 1) test the entry before field-level validation kicks in, 2) grabs the value and, behind the scenes adds the value to your value list table.  I would hesitate letting Users enter any value they wish.  Usually a request is made by user to management to include a value and, if valid, management inserts the value for selection.

                  • 6. Re: Dynamic List
                    FlipFlop

                    Thanks a lot for your proposed solution of a script trigger! I know the concept of conditional value lists, e.g.

                    http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20lists/session/L3RpbWUvMTMwNDg3MjIyMC9zaWQvSUkzODZzdGs%3D

                    but I found no description in this forum on how to populate the fields in the Menu table the way I described it. Anyway, I will try your proposal. Scripts are new to me but let's see.