10 Replies Latest reply on Mar 9, 2014 11:40 AM by lijnbach

    Restrict value list based on table

    lijnbach

      Title

      Restrict value list based on table

      Post

           Because I want to be sure of the integrity of the database, users are not allowed to delete records. (My main table is connected with 8 other tables. If someone deletes a record of a related table, I have a problem).

           So, users can not delete records, but put them "in Archive". (Just a combo box with "yes or no". In all lay outs I select the appropriate table, with the selection "Archive = No". (Works all fine).

           But, I have value lists (based on related tables) that shows the records from the table. But can I restrict these tables also in value lists with the restriction "Archive = No".  

           I couldn't find a solution on the forum.

           Thanks in advance,

           Hans Lijnbach

        • 1. Re: Restrict value list based on table
          philmodjunk

               Yes. Conditional value lists can be set up that can do this and there are several different approaches that con be used.

               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 next to 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 and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               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.

          • 2. Re: Restrict value list based on table
            lijnbach

                 O.K. Thanks Phil. (Why don't we have that in our Dutch Help?)

                 This will work for me.

                 Hans

            • 3. Re: Restrict value list based on table
              GuyStevens

                   I have about three videos abount Conditional Value lists uploaded on my youtube channel.

                   You can check those out:

              http://www.youtube.com/user/Dasaint1982 

                    

              • 4. Re: Restrict value list based on table
                lijnbach

                     Thanks,

                     I will watch them. 

                     Hans Lijnbach

                • 5. Re: Restrict value list based on table
                  lijnbach

                       Hi Phil,

                       I looked at the conditional value lists and I understand how it is working. I can build it, but I think it is a lot of work for something as simple as what I want. I have to create an extra table, etc. And I do not really want conditions, I only want is a selection on the records in the table.

                       Is there an easier way to make a selection on the table. All I need is the records where "Archive = No". 

                       Hans

                  • 6. Re: Restrict value list based on table
                    philmodjunk

                         You shouldn't need to add a new table for any of the methods described in these links, though you may need to add a new Tutorial: What are Table Occurrences? in the relationships graph in order to define a needed relationship.

                         If you take a closer look at "option 1" in the forum tutorial, you have an example of a method that does not even require creating a relationship between two table occurrences to make it happen.

                         but isn't "Archive = No" a value in fields of your main table, not the related tables from which you want to draw values for this value list? Or am I misunderstanding what you want to accomplish here?

                    • 7. Re: Restrict value list based on table
                      lijnbach

                           Hi Phil,

                           I feel a bit ashamed to border you again, but I can't get it working the way I want. I have been working on it all day yesterday and I became really frustrated about this issue. What am I missing?

                           I studied you example of the conditional value list. And I build one myself and it is working. But I can not get it working with just one selection on the record. I also had a closer look at option 1, but somehow I am missing the point. I even build a very small database as a test, but still not working.

                           Can you help me out?

                           Hans.

                      • 8. Re: Restrict value list based on table
                        philmodjunk

                             but isn't "Archive = No" a value in fields of your main table, not the related tables from which you want to draw values for this value list? Or am I misunderstanding what you want to accomplish here?

                             In otherwords, please describe in more detail what you have and how your conditional value list needs to work.

                        • 9. Re: Restrict value list based on table
                          lijnbach

                               O.K. Phil, This is the case:

                               I don't allow deleting of records because of integrity. If records are not in use anymore they get the value "Archive = Yes". All  tables, as well the main table as the related tables have this field "Archive = No" or "Archive = Yes"

                               If I use the main lay out (Employees) (Only employees with "Archive = No"), I want to add the department they are working. So I have a table "Departments". This table is related to the table Employees.

                               But, if I select the table Department (in a value list - on the main layout with employees) I only want to see the departments with "Archive = No". I don't want users to select a department that is not in use anymore. 

                               It seems easy, but I am missing something. 

                               Hans

                          • 10. Re: Restrict value list based on table
                            lijnbach

                                 Dear Phil,

                                 I had a brainwave last night, and I have the solution. Perhaps I was thinking to complex. In older days (SQL and Access) I was used to have a relation also between the main ID of a related table and the main table. In Filemaker this goes wrong. Filemaker does not need that relation. Just a relation for the value you want to use. (Stupid I).

                                 The solution I am using now is based on your example of conditional value lists.

                                 Sorry I used so much of your time and effort, but your solution did help me out.

                                 Hans