1 2 3 4 Previous Next 48 Replies Latest reply on Dec 2, 2010 11:58 AM by SwissMac

    Conditional Value List relationship mess



      Conditional Value List relationship mess


      I followed the tutorial that creates the menu.fp7 file and successfully got it to work as a standalone concept, but I cant work out how to integrate it into my slowly building tax admin database (I can't work out which table to link to my existing relationships).

      The tutorial says to create a primary table, menu (which lists all the available foods) and a second, identical table called Order (which lists the specific set of foods in a single order). Can this work if Type and Item are in different tables? If not, which one do I use to link to other tables?

      In my Tax Admin database I would like to conditionally select the Gemeinde (Community) based on which Kanton (City/State) was first selected. Each Kanton has many Gemeinde, and each Kanton also has other features which I don't want to have to type in over and over again for each Gemeinde (eg deadline dates, fees etc), which I would have to do if Kanton and Gemeinde have to be in the same table (one Kanton has 231 Gemeinde in it so I don't want to have to repeat the same Kantonal deadlines for each Gemeinde).

      If somehow Gemeinde can be in a different table to Kanton, but still be part of the Conditional Value list it would be great. At the moment they are in the TaxOffice table which is linked TaxOffice -< TaxReturn to the rest of the database. Is a Conditional Value List method using 2 tables possible?

      If Kanton and Gemeinde have to be in the same table (Tax Office, do I just duplicate this table and somehow link TaxOffice 2 and Tax Office in the same way as Menu and Order are linked (if so, which table is the equivalent of Menu and which the equivalent of Order?

      Sorry if I'm vague, my head's spinning a bit and I'm rushing to get this question up before the weekend... (I must the the only one silly enough to continue working on this when I go home!)

        • 1. Re: Conditional Value List relationship mess

          I'm just as silly. In fact, I'll be finishing up a project all weekend, I expect. Wink

          Here's a tutorial I wrote on conditional value lists. It got lost during the forum software upgrade and I only recently found it again: 

          In your case, let's get the basics clear: You have one table for Kanton and One for Gemeinde. This sounds right. And you have a field in Gemeinde that identifies the Kanton to which it belongs? This is key as this will allow your conditional value list to use a selected Kanton to reduce your list of Gemeinde names to just those that reside in the selected Kanton.

          If I read your post right, you want to select first a Kanton and then a Gemeinde in your Tax Office table.

          You'll need this relationship:

          Tax Office::Kanton = Gemeinde::Kanton 

          And two valuelists.

          1. Value list one, Kantons, will be a value list that lists all Kantons in the Kanton table.
            Format Tax Office::Kanton to use this value list.
          2. Value list two, will be your conditional value list:
            Specify that the Gemeinde names be drawn from Gemeinde and specify "Use only related values starting from Tax Office".
          • 2. Re: Conditional Value List relationship mess

            Wow! That was nice and fast! 

            At the moment I only have one table, TaxOffice because I was wondering which way to go. I like your suggestion because it's 11.30pm at night and I want to go home right now! It sounds pretty much like what I want to achieve. Maybe I'll be less tired after some fresh air on the way home and be able to answer more intelligently after I've had a break.

            Truly, Sir, you are the fount of all Filemaker knowledge! I so much appreciate your help, and I'm really learning so much. It's a great feeling doing the stuff on my own after I've (eventually) got it!

            • 3. Re: Conditional Value List relationship mess

              OK, I'm back and have a question.

              Are you thinking there are three tables involved, or just two? In other words, do you imagine I have TaxOffice and Kanton and Gemeinde? Or are you initially referring to Kanton as TaxOffice?

              What I have right now is TaxOffice with both Kanton and Gemeinde in it. I have created a table called Gemeinde, and another called Kanton but can't work out if I have too many or not, and if TaxOffice is joined to TaxReturn, is that table redundant now if I really should have tables Kanton and Gemeinde?

              Also, should I link on a Field called Kanton, or KantonID?

              • 4. Re: Conditional Value List relationship mess

                Yes three tables. Put tax office info in tax office, Kanton Info in Kanton an Gemeinde info in Gemiende. You select a value for a Kanton field to link a tax office record to a specific Kanton. Likewise for Gemeinde.

                If the only info you need to store about a Kanton, is it's name, you can do this without the Kanton table. It's only purpose for this value list set up is to produce the list of Kantons. (Without this table, you'd specify the list as being drawn from the Kanton field in Gemeinde.)

                • 5. Re: Conditional Value List relationship mess

                  I admit to being a little confused now, not by you, but by what I've done. I think I've got one too many of either Kanton or Gemeinde in both or either of Kanton or Gemeinde tables... am I right in thinking I should only have Kanton in Kanton, and both Kanton and Gemeinde in Gemeinde, with the two tables linked by Kanton (or by Gemeinde)??

                  Here is where I'm at right now:

                  TaxOffice renamed to Kanton and with the following fields

                  Kanton (linked to TaxReturn)

                  • KantonID (Serial No)
                  • Kanton (Text)
                  • GemeindeID or Gemeinde (Serial No or Text - Question?)
                  • Tax Deadline (Date)
                  • Free Extension To (Date)
                  • Paid Extension To (Date)
                  • Extension Cost (Text)
                  • Coat of Arms (Container, image to appear when relevant Kanton is picked)


                  • GemeindeID (Serial No)
                  • Gemeinde (Text)
                  • Kanton (Text)

                  TaxOfficeDetails separated out and linked directly to TaxReturn so as to match the exact Tax Office details that are on the tax return.

                  • 6. Re: Conditional Value List relationship mess

                    If you are wondering why I only have two tables it's because when you said "Put tax office info in tax office, Kanton Info in Kanton an Gemeinde info in Gemiende" I realised I didn't have any Tax Office info that wasn't in either Kanton or Gemeinde. Yes, there used to be, but I found out that the TaxOffice Address info had very little logic associated with it, so I thought a separate table linked directly to TaxReturn would be better able to reflect the fact that the tax office address is always the one on the TaxReturn, where you live has some effect, but not 100%.

                    I know this counteracts what you told me to do, but I didn't know how to use the empty table for TaxInfo...

                    • 7. Re: Conditional Value List relationship mess

                      No Idea how it happened, but by some complete accident I now have a working Conditional Value List. I tried to follow your instructions, but didn't understand the concept fully but the main thing is I think it works...

                      Thanks for your clues, they were obviously a lot more informed than my fiddling about.

                      • 8. Re: Conditional Value List relationship mess

                        Obviously fiddling about and hoping isn't good enough. There's a bug.

                        If I select a field from Kanton, it is also possible to pick the occassional field for Gemeinde from some fields that are not in that Kanton, but are instead from a different Kanton. I have no idea why this is happening.

                        Kanton::Kanton and Gemeinde::Gemeinde are both fields in the layout based on TaxEntity. The relationship goes like this:

                        TaxEntity --< TaxReturn >-- Kanton >--< Gemeinde

                        The Value List arrangement for Kanton::Kanton is based on the Kanton table which provides the list of Kantonal names and characteristics eg deadlines etc and specifies All as the filter criterion.

                        The Value List arrangement for Gemeinde::Gemeinde is based on the Gemeinde table which looks up the Value list based on Gemeinde:Gemeinde including only related fields from Kanton.

                        The tables contain are set up like this:


                        • KantonID
                        • Kanton
                        • Series of info fields, dates etc 



                        • GemeindeID
                        • Gemeinde
                        • Kanton

                        The relationship between Kanton and Gemeinde was based on:

                        Kanton::Kanton = Gemeinde::Kanton

                        both sides could create records in each other.

                        For some reason, after I select a Gemeinde from Kanton A, it adds itself to the list of Gemeindes from Kanton B in the drop down menu. I know I've done something not quite right, but as I said earlier, I haven't really grasped this idea yet and would really value someone's help.

                        • 9. Re: Conditional Value List relationship mess

                          How is TaxReturn Related to Kanton?

                          Is the drop down field, a field that is defined in TaxReturn or somewhere else?

                          By the relationships in your last post, I would think this would work if you are selecting Kanton and Gemeinde values in fields defined in TaxReturn. (since you have a KantonID field, you could use it, rather than the Kanton name in your relationships. That would be the standard "textbook" approach for setting up this relationship, though it's not as critical as some relationships given the assumption that Kanton names are both unique and very unlikely to be changed.)

                          • 10. Re: Conditional Value List relationship mess

                            Probably easier if I give you the file reference:


                            Each Kanton has different tax laws, different forms, and different deadlines. We work on about 6 different Kantons regularly, and need to see at a glance which Kanton we are working with. In the Data Entry layout the Kanton and Gemeinde info needs to be seen from all tabs, although it is connected most strongly with the TaxReturn table. Which one is determined by where someone lives, but doesn't necessarily form part of someone's address (Kanton doesn't, Gemeinde usually does, but not always, which is why they are not in Personal Info but in TaxInfo instead).

                            In the database I have a feeling I need to use a second Table Occurrence somewhere, but can't work out where. I was working on this fruitlessly on Friday night until 5am and tried many different options, but I really don't know what I should be doing here. I read many online tutorials and even watched and copied some YouTube tutorials too - and got them to work first time by copying, but could not work out how to do the same thing in my own database. Frustrating when you think you have it half right, but just don't know which half!

                            The reason I used Kanton and not KantonID was due to being confused about whether these relationships should use ID fields or name fields (some of the tutorials seem to vary their terminology and use them interchangeably - even using "CategoryID" and "ParentID" in some cases, which confused me further. The worst ones were those which said "Kanton" but meant KantonID.

                            I can't work out how some Gemeinde names are being added to the wrong Kanton though.

                            • 11. Re: Conditional Value List relationship mess

                              This is the key issue here as I see it: In which table have you defined the fields where, a) you select a Kanton and then b) select a Gemeinde from the list of those that belong to the selected Kanton? The relationship that filters your value list down depends on that context in  order to do its job.

                              going from this information: TaxEntity --< TaxReturn >-- Kanton >--< Gemeinde

                              If you define these two fields in TaxReturn, it should work. If you define them in another table, they may not work at all or they might list Gemiende names from more than one Kanton.

                              • 12. Re: Conditional Value List relationship mess

                                Sorry, I don't understand... do you mean I should not put Kanton in the Kanton table, and not put Gemeinde in the Gemeinde table? I thought I should group all of the stuff related to Kanton in one table, all the stuff related to Gemeinde in the Gemeinde table, and all the stuff for a specific TaxReturn in the TaxReturn table... or are you saying I should somehow link them through?

                                • 13. Re: Conditional Value List relationship mess

                                  You have two sets of fields here. The Kanton is used to provide a list of possiblie kanton options. The Field for gemeinde in Gemeinde is used to provide a list of possible Gemiendes.

                                  But when you implement your conditional value list, you need fields for each of these in some other table. A Kanton in the Kanton table is used to identify one record with all the info you need specific to that one Kanton. When you select Kanton in another table such as tax entity or tax return, you are saying this tax entity or tax return belongs to the Kanton I've selected from the Kanton table. And when you then select a Gemiende in the conditional value list, you are saying This record belongs to the Gemiende that I select from the Gemiendes that belong to the Kanton I have already specified.

                                  I'm just trying to find out in which table you've defined the two fields where you are making these selections.

                                  • 14. Re: Conditional Value List relationship mess


                                    As you can see, where they are may have changed over time as I tried to make this work. In the file above (the latest version that half way works) the Data Entry layout shows Kanton::Kanton and Gemeinde::Gemeinde. 

                                    1 2 3 4 Previous Next