1 2 Previous Next 17 Replies Latest reply on Oct 27, 2014 3:49 PM by deninger

    Relationship Help (conditional relationship)

    deninger

      I am working with a relationship that I am having difficulty modeling within FM and I was hoping someone might be able to push me in the right direction.

       

      The relationship is set to include both matching attributes A and B as illustrated below.

       

      Table 1 Table 2

      Attrib_A _________/ Attrib_A // Attrib_A and _B are both > 0

      Attrib_B /  Attrib_B

       

      This works fine, but I also want the relationship to resolve to just:

       

      Table 1 Table 2

      Attrib_A >---------< Attrib_A // only match on Attrib_A because

      Attrib_B Attrib_B // Attrib_B in Table 2 is set to "00" or is empty

       

      in the case that Attrib_B in Table 2 is either empty or "00" (the default "all" value)

       

      Is this possible in FM? I am trying to think of a calculation trick to enable this, but so far I have come up blank.

       

      Thanks!

        • 1. Re: Relationship Help (conditional relationship)
          beverly

          if you show the values in a portal, you can add filter(s) to further define what "matches" in the display. Perhaps that is what you need?

           

          If not, create two relationships (named differently) Use as needed.

           

          Beverly

          • 2. Re: Relationship Help (conditional relationship)
            PalmDBS

            Create a calc on the left side (table a) that results in a multi-key, such that

             

            Attrib_A|Attrib_B //first value

            Attrib_A|NoValue  //second value with the actual text "NoValue"

             

            Then, on the right side (table b) a calc field that returns the following if Attrib_B is 00 or empty

            Attrib_A|NoValue  //with the actual text "NoValue"

             

            otherwise it returns

            Attrib_A|Attrib_B

             

            You would of course need to strip line breaks in your values to make the multi-keys work properly.

            • 3. Re: Relationship Help (conditional relationship)
              Malcolm

              No, you cannot do it. If attrib_B is empty on either side it will not

              match. If attrib_B is empty on both sides you simply have twice as many

              reasons not to match!

               

              You may want to combine A and B into a third field and use that as the key

               

              attrib_A & char(127) & attrib_B

               

              When you do that, it doesn't matter if attrib_B is empty.

               

              malcolm

              • 4. Re: Relationship Help (conditional relationship)
                PalmDBS

                Malcolm wrote:

                 

                No, you cannot do it. If attrib_B is empty on either side it will not

                match. If attrib_B is empty on both sides you simply have twice as many

                reasons not to match!

                 

                He knows this, hence his note in the original post

                 

                // Attrib_A and _B are both > 0

                 

                And he knows he needs a calculation, he was just trying to figure it out.  Using just attrib_A & char(127) & attrib_B won't cut it either, as he wants to match on JUST attrib_A if attrib_B is 00 in Table 2.

                 

                The only way I see to do that is to have a multi-key on the left side, as I showed in my reply.  Maybe somebody else has a method I'm overlooking.

                • 5. Re: Relationship Help (conditional relationship)
                  stefan_s

                  Hi Deninger,

                   

                  Yes, this can be done. But it needs a bit of overhead and there are some downsides to the technique.

                   

                  I use this kind of relationship to perform certain types of standardized searches.

                   

                  I don't use the built-in portal filtering for this.

                   

                  The method I use, needs one calc field on both sides of the relationship. And I don't use this method for relationships that create new records in the child table.

                   

                  If I understand correctly, Attrib_A will always have a value and this, can always be valid. So the key field calcs are necessary for Attrib_b only.

                  I have up top 20 key fields in my solutions. And still they perform very quickly.

                   

                  Key field for Attrib_B is like this:

                   

                  Attrib_B_Lkey: Case(not isempty(Attrib_B);Attrib_B;1) (if this is the number field, I would use some number that will never be actually used).

                   

                  Attrib_B_Rkey: Attrib_B & "[return]1" (if this is the number field, I would use some number that will never be actually used).

                  ! This calc field need to have the checkbox "Do not evaluate if all referenced fields are empty" UNchecked.

                   

                  Now the relation can be like this:

                   

                  Attrib_A      \

                   

                  • 6. Re: Relationship Help (conditional relationship)
                    filemakercowboy

                    It will help if you conceptualize the problem a bit differently. You don't need to match on attribute A instead of attribute B; you simply need to make sure that attribute B automatically matches when it's empty or "00." So Stefan is essentially correct. Since you are using a double zero, I must assume that attribute B is not a number field, in which case what you need on the right side is a calc that results in something like "<empty>" when attribute B is empty:

                     

                    Case ( IsEmpty ( Attrib_B ; "<empty>" ; Attrib_B ) )

                     

                    Then, on the left side, you need attribute B to always match "00" as well:

                     

                    Attrib_B &amp; Char ( 127 ) &amp; "<empty>" &amp; Char ( 127 ) &amp; "00"

                    • 7. Re: Relationship Help (conditional relationship)
                      deninger

                      Let me make my problem a little more concrete (because as I see it, the concepts of a multi-key would work for Table B relating back to Table A, wheras I need to go the other direction)

                       

                      Consider a bird enthusiast app to screen thru user inputted findings to highlight by color certain items

                       

                      Table A = user reported finds

                      Attribute 1 = general type  01 = bird 02 = egg 03 = nest etc.

                      Attribute 2 = sub-Type of atribute 1

                       

                      Table B = list of attributes that should be color-coded

                      Attribute 1 // manditory

                      Attribute 2 // optional ("00" if not specified)

                      Attribute 3 // the color that the item in Table A should be formatted as

                       

                      Then consider the case below:

                       

                      User Finds (Table A) Records

                      1.

                      Attribute 1 = 02 // Egg

                      Attribute 2 = 12 // Blue

                      ---

                      2.

                      Attribute 1 = 01 // Bird

                      Attribute 2 = 15 // Sparrow

                      ---

                      3.

                      Attribute 1 = 03 // Nest

                      Atribute 2 = 16 // round

                      ---

                      4.

                      Attribute 1 = 02 // Egg

                      Attribute 2 = 13 // Green

                      ---

                      5.

                      Attribute 1 = 03 // Nests

                      Attribute2 = 12 // Oval

                       

                      Screening Table (Table B) (we want to be able to identify if an item in Table A meets any of the following requiremnts)

                      1.

                      Attribute 1 = 02 // We want to identify all eggs

                      Atribute 2 = 00 // we don't care what color they are

                      Attribute 3 = "RED" // Color of text to return (to format the item in Table A)

                      ---

                      2.

                      Attribute 1 = 03 // Nests

                      Attribute 2 = 16 // Round

                      Attribute 3 = "BLUE" // Color of text to return (to format the item in Table A)

                       

                       

                      So...

                      The app wants to be able to highlight if an item in Table A falls into the general categories of interest (By color, Attribute C). In our case above, we are interseted in:

                      -- ALL eggs (without respect to egg color)

                      -- Only Round Nests

                       

                      Records on Table A that match would be

                      1 and 4 returning a color of RED

                      3 returning a color of Blue

                       

                      Maybe a relationship is not the best way to accomplish this, but it would make my life easier not to have to iterate thru the data

                      • 8. Re: Relationship Help (conditional relationship)
                        stefan_s

                        Hi Deninger,

                         

                        deninger <noreply@filemaker.com> kirjoitti 25.10.2014 kello 18.56:

                         

                        Records on Table A that match would be

                         

                        1 and 4 returning a color of RED

                         

                        3 returning a color of Blue

                         

                         

                        Maybe a relationship is not the best way to accomplish this, but it would make my life easier not to have to iterate thru the data

                         

                         

                         

                        I do think, that the method I propose will do just that. This is what I use it for.

                         

                        (And , the bird example is a good one. I'm a biologist by education, and have my own birdwatching Go-solution)

                         

                        __

                        Stefan Schutt, Mouse Up, Finland

                        • 9. Re: Relationship Help (conditional relationship)
                          PalmDBS

                          The calculations as I provided already work for your scenario.  Have you tried them?  Are you not getting the right result?  I have a working example I can send you if you'd like.

                           

                          Screen Shot 2014-10-25 at 9.46.33 AM.png

                          • 10. Re: Relationship Help (conditional relationship)
                            stefan_s

                            PalmDBS <noreply@filemaker.com> kirjoitti 25.10.2014 kello 19.47:

                             

                            Typically when someone puts up an example and gives us names of Table 1 and Table 2, we assume that table 1 is the left side fo the relationship - that doesn't end up being the case here.

                             

                             

                             

                            Yup, this was my thought, too.

                             

                            But my method ( I actually think, that PalmDBS and I are on the same track here) works both ways.

                             

                            You just have to swap they key-calculations around.

                             

                            Many of us "old-school" developers tend to think that the parent is on the left (hence "Lkey"/"Rkey").

                             

                            We often put the Parent TO to the left and the child TO's to the right. It makes it clearer to work with, especially, complex solutions.

                             

                            I hope we are able to help, because what you ask for is possible.

                             

                             

                            __

                            Stefan Schutt, Mouse Up, Finland

                            • 11. Re: Relationship Help (conditional relationship)
                              deninger

                              Okay, I think I am seeing where PalmDBS and stefan_s are going with this. I was / am still a bit confused by the concept of a multi-key (I don't think there is a SQL equivalent, if I am catching the meaning).

                               

                              So am I understanding this correctly:  A multi-key is actually a multiple value list as a key (and this works)? Would the multi-key be created similar to value-lists passed to scripts as multiple parameters (e.g. value1 & (paragraph mark) & value 2)?

                               

                              And will this work with more than two values?

                              • 12. Re: Relationship Help (conditional relationship)
                                filemakercowboy

                                Yes! Exactly right. As many values as you want.

                                • 13. Re: Relationship Help (conditional relationship)
                                  PalmDBS

                                  What he said :0) 

                                   

                                  Sorry to not explain the concept of multi-keys in advance.

                                  • 14. Re: Relationship Help (conditional relationship)
                                    deninger

                                    Okay, I have eplored the multi-key technique and find it quite interesting. My problem is that the attributes are not unique across columns. For example, in the bird example above and adding another screening record (see below). The Multi-key approach still returns a relationship to criteria 3 (and it should not)

                                     

                                     

                                    User Finds (Table A) Records   //these are from above. Notice that Attribute 2 = 12 has different meanings based on the value of Attribute 1

                                    1.

                                    Attribute 1 = 02 // Egg

                                    Attribute 2 = 12 // Blue   <--- 12 = Blue when we are an egg but

                                     

                                    5.

                                    Attribute 1 = 03 // Nests

                                    Attribute2 = 12 // Oval    <--- 12 = Oval in the case of a nest...

                                     

                                    Screening Table (Table B) (we want to be able to identify if an item in Table A meets any of the following requiremnts)

                                    3.

                                    Attribute 1 = 02 // We want to identify all eggs

                                    Atribute 2 = 12 // We want only Blue Eggs

                                    Attribute 3 = "BURNT UMBER" // Color of text to return (to format the item in Table A)

                                    1 2 Previous Next