8 Replies Latest reply on Jan 25, 2017 11:19 AM by philmodjunk

    Conditional value list and many to many relationship

    LeszekŁuczkanin

      Hi!

       

       

      I have a conditional value list and it it many to many relationship with my table. Is it correct? Can I set up conditional value list wit one to many relation?

      and I am looking for example when I can use many to many relationship or even when I have to use many to many in order to have all correct.

        • 1. Re: Conditional value list and many to many relationship
          philmodjunk

          Can you expand on what you want with quite a bit more detail? People don't even use "many to many relationship" to mean the same thing in all cases.

           

          Do you mean this type of relationship?

           

          Table1----<JoinTable>----Table2

           

          And from table 1, you want to see a conditional value list based on values from Table 2 with the relationship filtering the values that you get in the value list?

           

          Yes, that should be possible.

          • 2. Re: Conditional value list and many to many relationship
            karina

            Hi,

            I just made a sample file Category Select for another post and I added other Samples.

            Hope it helps.

            Greetz,

            Karina

            • 3. Re: Conditional value list and many to many relationship
              LeszekŁuczkanin

              Zrzut ekranu 2017-01-21 o 11.26.29.png

              This is my graph. I learned how to make a screenshot, so it is not translated

              OPERACJE is my main table, when I track every income and outcome.

              KODY PRZYCHODU is my table with categories for incomes and KODY ROZCHODU is table with categories for outcomes. I set it up according to official tutorial (here) but it is many to many relationship. I don't know if it is ok or I did something wrong.

               

              This part of my database I use to track what is real. There is also second part of my database. Babel's name is BUDŻET PRZYCHODY. I use it to plan my budget. There is relationship between OPERACJE and BUDŻET PRZYCHODY where

              OPERATION DATE = BUDGET YEAR

              and

              OPERATION CATEGORY PRIMARY = BUDGET CATEGORY PRIMARY

              and

              OPERATION CATEGORY SECONDARY = BUDGET CATEGORY SECONDARY

              and

              OPERATION_CUSTOMER FK = BUDGET_CUSTOMER FK

               

              And with that I have some problems:

              1. I couldn't use this tables with categories KODY PRZYCHODU and KODY ROZCHODU with my BUDGET table. I made a new Table Occurrence KATEGORIE PRZYCHODU and KATEGORIE ROZCHODU and now it works.

              2. This relation between OPERATIONS (OPERACJE) and BUDGET INCOMES (BUDŻET PRZYCHODY) is many to many. Now everything works but I'm afraid that in the future there can be an error which will make my database useless.

               

              My Apple Pencil is ready, I can repost this with translation

               

              Leszek

              • 4. Re: Conditional value list and many to many relationship
                karina

                Hi,

                 

                Can you upload a copy of your database?

                Did you take a look at my sample files?

                 

                I prefer not to create spiderwebs.

                It will probably not be useless but when you want more tables  and more relationships in your database in the future, I think it will be a big challenge to manage.

                 

                Greetz

                Karina

                • 5. Re: Conditional value list and many to many relationship
                  LeszekŁuczkanin

                  I'm still looking at your files. I love this kind of keeping things in order, but it is something new, so I have to figure out everything

                  • 6. Re: Conditional value list and many to many relationship
                    philmodjunk

                    Technically, these are not many to many relationships unless you are storing a list of values in one of the match fields used in these relationships.

                     

                    What the "many" connectors on each end of the relationship line indicate is that you haven't set up a field option on the match field that would prevent duplicate values. Many solutions work just fine with such as long as users don't/can't enter duplicates on the "one" side of your relationship, but it would be safer to add a unique values validation option to the match fields in your category tables.

                    • 7. Re: Conditional value list and many to many relationship
                      LeszekŁuczkanin

                      This may be stupid question, but I have to ask:

                       

                      I have a TABLE with 3 TABLE OCCURRENCES. If I add a new record in one of them it will be ADDED in all TO but eventually it won't be SEEN in all of TO? Did I understand it properly?

                      • 8. Re: Conditional value list and many to many relationship
                        philmodjunk

                        You don't add records to a table occurrence, you add them to a table. The table occurrence just establishes the context and might affect the auto-entry of data into certain fields due to that context--such as entering a foreign key value because you are adding the record though a relationship from a related occurrence--for example. Think of each table occurrence (box in your relationship graph) as a convenient label that refers to a table on the tables tab of Manage | Relationship.

                         

                        And thus any records added are there no matter which table occurrence. Whether a given record is visible depends on context. If you base a layout on any one of the three occurrences, ALL of the records may be viewed on that record no matter what values are entered into a given field unless you take additional steps in your interface design to exclude them.

                         

                        But if you access the data in one of these occurrences from the context of a layout based on a table occurrence related to one of these three, that relationship could keep some records from being visible. An example would be if you placed a portal to one of these three table occurrences on a layout based on a different table occurrence.