5 Replies Latest reply on Oct 22, 2013 10:42 AM by FilipePimentel

    Multiple Conditional List

    FilipePimentel

      Title

      Multiple Conditional List

      Post

           Good day, 

           I am kind of new with FileMaker and I am creating an accounts db. I am stuck on the creation of conditional lists to cascade to a final selection. What I mean: 

             
      1.           User will start program and select one out of 4 companies (Cia 1, Cia 2, Cia 3 and Cia 4)
      2.      
      3.           Depending on the company selection, a list of departments will be made available: I.e. If Cia 1 only Services, Sales and Constructions will be available /  If Cia 2 only Jd2 and Jd3 available / if Cia 3 only CFCA and CFCB available. And if Cia4 only Driving available. 
      4.      
      5.           Then, depending on the selection of each departament (Services, Sales, etc), a list of option will be made available to each of option. Some will be the same (global option - available to any department selection).
      6.      
      7.           then after that the user would be set to start entering the information like, amounts, dates, etc...

           I hope I was clear enough. 

            

           Thanks for all you help

            

           VenkoDesigns

        • 1. Re: Multiple Conditional List
          philmodjunk

               I call this a Hierarchical conditional value list. WHere the value selected in field 1 controls what values are listed for field 2 and the selection in Field 2 then controls what values are listed in field 3, and so forth...

               See the last link in this set of links. You can refer to the other links if you are unfamiliar with using a relationship to control a conditional value list.

               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, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

               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

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

          • 2. Re: Multiple Conditional List
            FilipePimentel

                 Hello Phil, 

                  

                 Thanks for the reply. I did manage to get a 3 level hierarchy working. used one of the examples you posted. thanks. However, I need the 4th level - which is kind of working, but it does not present the list sorted by the previous level. 

                 I added the screenshot of the relationships - sorry it is in portuguese, as this is the company I am creating the db for. 

                 This is how it goes: 

                 level 1 - user selects a company. This company has a set of departments only pertinent to it

                 level 2 - user then select out of these departments which one he/she wants to add data. Each dept has a set of only pertinent to it. 

                 level 3 - user then select if he or she wants income or expense. 

                 Up to here everything is working fine using the attached relationships schema and value lists. 

                 But then on level 4 (the actual expenses or income lines) I see the whole list, not only the ones for expenses or income.

                 Also, I think I am doing something wrong, as in my data table, where all this info is it seems to me that I could reduce the amount of data, but do not know how: let me explain: 

                 From level 4 backwards: I have 1 line which is expense which is present in 3 or 4 departments for one or more companies. But sometimes, I have one expense line which is only present in one department for one company. 

                 For example: Water is an expense that all companies will have to pay and it comes up 8 times in my table:

                 Cia A - depto J - Expense - Water

                 Cia B - Depto D - Expense - Water

                 How can I clear that.

                  

                 How I wasn't too confusing 

                  

                 Thanks 

            • 3. Re: Multiple Conditional List
              FilipePimentel

                   Here is the water case. 1st field: cia, 2nd field dpto, 3rd field transaction (expenses) and 4th field the item line. 

              • 4. Re: Multiple Conditional List
                philmodjunk

                     I can't read the language, but the relationships used do not look like they are correctly set up. Using the same match field on for relationships on each side of a given table occurrence box does not look correct to me.

                     Also, if you look at the tutorial for hierarchical value lists, this "long chain" of table occurrences--which can make intuitive sense, is not what is described in that link.

                     Instead, you end up with table occurrences that directly link to the table occurrence on which your layout is based, each with one of the value list formatted fields specified as a match field in the relationship. A 4 level hierarchical value list might thus produce relationships that look like this:

                                          ValueTable1
                                                    v
                                                    |
                     ValueTable2>---LayoutTable----<ValueTable3
                                                     |
                                                     ^
                                                ValueTable4

                     LayoutTable::ValueLIstField1 = ValueTable1::MatchFIeld
                     LayoutTable::ValueListField2 = ValueTable2::matchFIeld
                     LayoutTable::ValueListField3 = ValueTable3::MatchField
                     LayoutTable::ValueLIstField4 = VlaueTable4::MatchField

                     ValueLIstField1 is usually a non-conditional value list--either custom values are all the values in a table.
                     ValueListField2 then lists values from valueTable1
                     valueLIst Field3 lists values from valueTable2

                     and so forth...

                • 5. Re: Multiple Conditional List
                  FilipePimentel

                       Phil, 

                       Once again thank you for your time. I got at the end. What I did I created two tables one for entering data e the other one for the items list

                       The item list contain the CIA, Dept, Transaction and Item. Also an ItemID.  I also created 3 global fields (CIA, Dept and Transaction). 

                       The Entry Data table contains an entryID, and itemID a data field (not used - it will be deleted or linked to another table). 

                       Then I created the Value Lists for CIA, Dept, Transaction & Item. 

                       Attached a representation of what i achieved. The Value List Selection is merely to make the visual easier. Now it is working like a charm. 

                        

                       Thanks for your support

                        

                       Filipe