8 Replies Latest reply on Nov 8, 2011 11:21 PM by mattb

    Table relationship not returning correct results in portal

    mattb

      Title

      Table relationship not returning correct results in portal

      Post

      Using FMP Advanced 11.0v2 for Mac

      1) Table A  and Table B have text fields with the same name and are related to each other using the "less than or equal" operator (<=)

      2) Layout (based on Table A) has an edit box where the user types in the value for this field in Table A 

      3) Immediately below this is a portal based on Table B that shows records from Table B that supposedly meet the relationship condition

      Specifically, table B has 2 records.  The value of the related fields are as follows:

      Record 1:  "2060-701335-005"

      Record 2:  "2060-701477-000"

      In the layout, I type in "2060-701335-005" in the edit box above the portal, expecting only one result, the record with "2060-701335-005", as the other record clearly should not meet the "less than or equal" relationship that has been set up.  

      However, the portal shows both records.  I even went to the Data Viewer and typed the following phrase in:

      "2060-701477-000" <= "2060-701335-005" and it returns 0, False, as it should.

      Nevertheless, it is showing up in the portal.  Why?  And how can I make this work as expected?

       

       

      Matt Bloomfield

       

       

       

      Screen_Shot_2011-11-07_at_6.24.20_PM.png

        • 1. Re: Table relationship not returning correct results in portal
          philmodjunk

          Which relationship do you have for this portal?

          ParentTable::PCB > PortalTable::PCB

          Or do you have:

          ParentTable::PCB < PortalTable::PCB

          Your screen shot looks consistent for this one:

          ParentTable::PCB < PortalTable::PCB

          So I am asking you to double check this and report exactly which relationship you have here.

          • 2. Re: Table relationship not returning correct results in portal
            mattb

            Hi Phil -

            >Which relationship do you have for this portal?

            >ParentTable::PCB > PortalTable::PCB

            No.

            >Or do you have:

            >ParentTable::PCB < PortalTable::PCB

            Yes.

            >Your screen shot looks consistent for this one:

            >ParentTable::PCB < PortalTable::PCB

            That's correct.  So why does the portal return a record whose value is clearly greater than the value typed in the edit box of the ParentTable?

            So I am asking you to double check this and report exactly which relationship you have here.

            It's been double- and triple-checked.  I actually changed the relationship to ParentTable::PCB >= PortalTable::PCB just to see what happens and the results are much worse.  I also tried it with ParentTable::PCB = PortalTable::PCB and that works but only returns an exact match and I'm hoping to return either an exact match or a substring of the exact match into the portal.  Any and all suggestions to achieve this result are welcome!

             

             

            Matt Bloomfield

            • 3. Re: Table relationship not returning correct results in portal
              philmodjunk

              Deleted my last message.

              If you have this relationship as you confirmed:

              ParentTable::PCB < PortalTable::PCB

              Then everything is working correctly.

              Youve entered2060-701335-005 into ParentTable::PCB.

              2060-701335-005 is less than or equal to

              2060-701335-005

              and also

              2060-701477-000

              Therefore both appear in the portal...

              • 4. Re: Table relationship not returning correct results in portal
                mattb

                Phil:

                If you have this relationship as you confirmed:

                ParentTable::PCB < PortalTable::PCB

                Then everything is working correctly.

                Youve entered2060-701335-005 into ParentTable::PCB.

                2060-701335-005 is less than or equal to

                2060-701335-005

                and also

                2060-701477-000

                Therefore both appear in the portal...

                -------------------------------------------------------------------------

                OK, I guess I just misunderstood how the relationship is working. What I really want to do is a "partial match", i.e. type in "2060-701335-005" into the parent's edit box and have the portal return records that would records that show numbers like "2060-701335-100" and "2060-701335-200" etc., but nothing else, i.e. I don't want it to return records where the first 10 digits are different than what I typed in the edit box.  I suppose I could experiment by changing the relationship to be >= and add a second condition to the relationship as well?  I'm getting a little fuzzy here...if you understand what I'm after here, maybe you could make a suggestion on how to accomplish that, or at least inform me that it's simply impossible!  Thanks!

                 

                Matt Bloomfield

                • 5. Re: Table relationship not returning correct results in portal
                  philmodjunk

                  Why doesn't simply reversing the inequality do what you want?

                  ParentTable::PCB>PortalTable::PCB

                  works in my test file....

                  It's also possible to set up a portal filter to control partial matches. You can find two different approaches at the bottom of the layout used in this demo file: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

                  • 6. Re: Table relationship not returning correct results in portal
                    mattb

                    Phil:

                    Why doesn't simply reversing the inequality do what you want?

                    ParentTable::PCB>PortalTable::PCB

                    works in my test file....

                    -------------------------------------------------------------------------------------------------------------------------------------------------------------

                    Well...reversing the relationship from <= to >= does solve the specific instance discussed earlier, but results in the same issue when I type

                    the higher of the 2 numbers into the Parent PCB field.  See screenshot below.  In this case, why does the lower number now show up on the portal, especially

                    seeing as how Parent::PCB >= Portal::PCB is the relationship now set up between the tables.  I feel like I just squeezed a tube of toothpaste and the defect 

                    showed up somewhere else!

                     

                     

                    Matt Bloomfield

                    • 7. Re: Table relationship not returning correct results in portal
                      mattb

                      Phil,

                      In looking at my last post, we can both agree that the portal results are consistent with the new >= logic.  Both records' PCB # are 

                      greater than or equal to the number typed in.  What I'm really trying to do here is show results where the portal returns only 

                      results where the first 10 digits of the number are always the same as the first 10 digits in the Parent box, but the last 3 digits

                      can be greater than the last 3 typed in the Parent box.  So, for the screenshot here, I would like to see "2060-701477-000",

                      "2060-701477-100", "2060-701477-300" etc. but NOT see "2060-701335-005".  Sounds like I need to use the portal filter to

                      accomplish this, yes?  

                       

                      MB

                      • 8. Re: Table relationship not returning correct results in portal
                        mattb

                        Phil:

                        > It's also possible to set up a portal filter to control partial matches. You can find two different approaches at the bottom of the layout used in this demo file: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

                        ------------------------------------------------------------------------------------------------------------------------------------------------------------------

                        I downloaded your sample from the above link.  The portal and search box at the bottom right of the layout was exactly what I was looking for, so thanks!

                        In hindsight, the table relationship was fine as it was, I just needed to get the right conditions into the filter portal to get the results I was seeking.

                         

                        MB