10 Replies Latest reply on Jan 11, 2014 11:20 PM by KevinThompson

    Managing active/inactive records in a pop-up menu

    KevinThompson

      Title

      Managing active/inactive records in a pop-up menu

      Post

           I'm having a heck of a time with this in multiple databases, and obviously missing something!  Here's a simple example - 

           There's a table that lists all the equipment in a plant, with an active/inactive field.  There's another table with a pop-up menu where the user selects an item from the equipment list, and there's a join table in-between.  If an equipment record is inactive, then I do not want it to show up in the list.  

           Right now I create a calculation field in the equipment table that only shows equipment that is active, and then reference that field for the pop-up list, but if that equipment is switched to inactive I lose the reference, and then I add all kinds of scripting to prevent or fix.

           Am I missing something?  Please help!  Thanks!

        • 1. Re: Managing active/inactive records in a pop-up menu
          KevinThompson

               I think I found my problem, but please let me know if this is right or wrong.  When I pull lists from other tables, I use one field on the layout for the list, I use the primary key as the first value, the description (e.g. equipment) as the second value, and check the box to only show the second field.  

               I pulled up a database from the training and noticed that they have one field as a list on the layout that uses the primary key with the description, and a second field that references the description field from the other table.  

               Is this how it's supposed to be done?  I never did this because it seems silly (and confusing) to have two different fields to serve one purpose.  

          • 2. Re: Managing active/inactive records in a pop-up menu
            philmodjunk
                 

                      When I pull lists from other tables, I use one field on the layout for the list, I use the primary key as the first value, the description (e.g. equipment) as the second value, and check the box to only show the second field.  

                 

                      I pulled up a database from the training and noticed that they have one field as a list on the layout that uses the primary key with the description, and a second field that references the description field from the other table.

                 And what do you see different in those two methods? I'm not sure that I see a difference here.

                 It could well also be an issue with your relationships. You'd need to describe your tables and relationships in more detail to be sure.

                 What you are trying to set up is a conditional value list. They can be based on a calculation field similar to what you describe (see the "forum tutorial" link for info on that method) or a relationship can be set up to filter out the undesired values. Here are some links on the subject:

                 There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                 The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

                 Forum Tutorial: Custom Value List?

                 Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                 Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                 Hierarchical Conditional Value lists: Conditional Value List Question

                 Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                 Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

            • 3. Re: Managing active/inactive records in a pop-up menu
              KevinThompson

                   Phil - Thanks for the feedback and information.  I'd like to share a sample database but my dropbox is on the fritz.  Is there another preferred method?  

              • 4. Re: Managing active/inactive records in a pop-up menu
                philmodjunk

                     Must be a problem with the Drop Box server, mine isn't uploading either. I doubt that you need to upload the database to figure this out. You might try uploading a screen shot of Manage | Database | Relationships to show the design choices that you have made, then upload a screen shot of the "specify Fields for Value List" dialog. Finally, describe your layout and what you have tried to set up that doesn't work.

                     But please do read at least the "forum tutorial" link first as it may provide the info you need to figure this out for yourself.

                • 5. Re: Managing active/inactive records in a pop-up menu
                  philmodjunk

                       Note: a quick web search on Drop Box reveals that they are indeed having trouble. Some reports attribute hacker activity, Drop Box claims they are having trouble with routine maintenance processes.

                  • 6. Re: Managing active/inactive records in a pop-up menu
                    KevinThompson

                         I did review all of your suggested posts, and the tutorial was most helpful.  I think I've been doing my list incorrect all along.  I currently use a single field as a drop down list (since a pop-up menu only displays the ID), set the first value list field to the related ID and the second value list to the description (e.g. equipment).  

                         It sounds like I need to have one field as the drop down or pop-up list that's set to the related ID, then add a second field that's related to the description, which automatically enters the information based on the value selected from the first field.  I never liked this setup b/c the user has to click on the ID, so I thought my current method was a good workaround - until I started filtering inactive records!  

                         I've included a sample of a dummy database showing what I think it should look like.  I understand that there's some formatting ways to make the two fields more seamless. 

                    • 7. Re: Managing active/inactive records in a pop-up menu
                      philmodjunk

                           But how does "filtering inactive records" affect this?

                           Here's how to do "option 1" from the tutorial:

                           Define a calculation field as: If ( StatusField = "Active" ; DescriptionField )

                           Be sure to select "Text" as the result type.

                           Use this calculation field in place of the Description field as the second field, the visible field, of your value list. Since this field is empty for inactive records, they will not appear in such a value list.

                           Option 2 can also be used, but you need both a calculation field as a match field and relationship that uses that match field to produce the same result.

                      • 8. Re: Managing active/inactive records in a pop-up menu
                        KevinThompson

                             Got the filtering covered. Just wanted to make sure that I needed to use two fields instead of trying to get it done with one. It definitely solves my problem, but it never seemed right to have the list on the "ID" field and a second field for the description. The user is going to want to click on the description, not the ID. 

                             Thanks for the help!

                        • 9. Re: Managing active/inactive records in a pop-up menu
                          philmodjunk

                               a) You might use a pop up menu instead of a drop down list and then the description field's contents is all that will be visible in the ID field.

                               b) Keep the description field. Take the Description field from the related table, give it an opaque fill color, use Behavior settings in the inspector to deny browse mode access, and place it on top of the drop down list formatted Id field. (Do not select the arrow option for the drop down list.). When the user clicks on the description field, the drop down list field will pop to the front and deploy. After an items has been selected from the drop down list, it will disappear back behind the description field which will update to show the selected item's description.

                               c) If you want to do some scripting, you might also use the method in this demo file:

                               FileMaker 12 and later users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                                

                          • 10. Re: Managing active/inactive records in a pop-up menu
                            KevinThompson

                                 Thanks Phil - appreciate the help!