1 2 Previous Next 27 Replies Latest reply on Mar 12, 2010 6:28 PM by TenaciousG3

    Find results reporting first portal row only - help!

    TenaciousG3

      Title

      Find results reporting first portal row only - help!

      Post

      I posted this question in another lenghty topic before I figured out what it was doing and now that other post is wordy and filled with unnecessary info, so I figured I would post the question more concisely here.  I hope that's ok.

       

      I'm trying to perform a search in one layout, and the results are only returning the information from the first row of a portal in another layout, not the actual data needed.

       

      I have a databse with 3 tables, A, B and C, with Table B being a join table.  I have a layout based on Table A with a portal to Table B where related records are entered through the portal.  Table C contains information linked to both Table A and B. I have a layout based on Table C where there are fields from all 3 tables.  I perform a search in that layout, entering my search criterea into one of the fields that is native to Table B, but used through the portal in Table A to enter the data. The results always only return the information from the first row of the portal, regardless of what I enter into the search field.  For example, say I have 5 records from Table B appearing in the portal.  The field I'm trying to search has a number, 1 through 5, in the field appearing in that order.  I do a search for number "4" in the table C layout.  The search returns other information correctly related to that record, but the number "1" always appears in my found results when it should be "4".  So it appears that the find is able to figure out what record I'm trying to find, but it keeps giving me the information that appears in the first row of the portal only.

       

      Any ideas how to fix this?

       

      Thanks!

       

      -George

      FMP10A, Mac OSX 10.5.8

        • 1. Re: Find results reporting first portal row only - help!
          philmodjunk

          It's really hard to decipher all the alphabet soup in that post. I think this is the issue, but could be wrong...

           

          When you enter criteria in a field from a related table, you are telling filemaker "find all the records in the current layout's table that have at least one related record with this value." You aren't finding records in any of the related tables at all, just finding records in the layout's table and then those records will display whatever records are related to them whether the related records match your criteria or not.

          • 2. Re: Find results reporting first portal row only - help!
            TenaciousG3

            Yeah, sorry for the alphabet soup :-P  Every time I try to explain it in a more detailed fashion nobody wants to read through all of that so nobody ever responds!  I was hoping by simplifying it a bit that people might be more apt to read it. I can certainly provide a more detailed description if you'd like, pictures even!

             

            So assuming your thoughts are correct, how do I fix that?  Do I need a script in order to perform the search correctly?

            • 3. Re: Find results reporting first portal row only - help!
              philmodjunk

              First, let's confirm facts:

               

              TableA:: PrimaryKey = TableB::FK_A

               

              TableC:: PrimaryKey = TableB::FK_B

               

              You are performing the find on a layout that refers to TableC with a portal to TableB on it displaying records from Table B. You enter find mode and specify a value in a portal field. The desired result is to see all the specified records from TableC but only see the portal records with the value you specified.

               

              Correct?

              • 4. Re: Find results reporting first portal row only - help!
                TenaciousG3

                See, here's where it gets more complicated and I start losing people :-P  It's also the part where I start annoying people, so I apologize ahead of time if I do so.  Here's my relationship graph:

                 

                I have a layout based on "A" where we enter new objects as needed.  On this layout there is a portal to "B" where we enter the usage of this object and new records are created in "B" through the portal.  "B" contains a field that looks up the episode number value from a field in "D" (I couldn't find another way of being able to enter that value in usage without it creating a new record in "D"). I have a layout based on "B" that lists all the usages, getting info from "A" "C" and "D" and it works fine when I do a search.  Then I have a layout based on "C" were we need to be able to see all the views along with relavent info, like the name of the object, the serial number, which view it is, which episode it's being used in, etc.  I have fields from all 4 tables on this layout, including the Episode Number Lookup field I mentioned before. The problem is when I search in the "C" layout using that lookup field, it always returns the value from the first line of the portal used in "A", not the relavent record from "B" which is what I want it to return. 

                 

                One other problem on a related note - I can't seem to get other info from "D" to pull in to layouts based on other tables (like episode title).  Is this because I'm using the "lookup" thing and not the actual field?

                 

                Does that make any sense? Am I a giant pain in the ass?  Should I just give up now?  :-P

                 

                Thanks...

                 

                 

                • 5. Re: Find results reporting first portal row only - help!
                  comment_1

                   


                  TenaciousG3 wrote:

                  Then I have a layout based on Views were we need to be able to see ... which episode it's being used in,


                   

                   

                  The problem here is that the information you seek is not there. The way you have it now, the Usage table records the usage of an asset - not of any specific view. Your relationships need to be:

                   

                  Assets -< Views -< Usage >- Episodes


                  • 6. Re: Find results reporting first portal row only - help!
                    TenaciousG3

                    That is true that usage records the asset not the view, but since the assets and its views are related and linked together through their respective tables, shouldn't it pull that information through anyway?  Perhaps I have a misunderstanding of how info from related tabes is pulled though?

                    • 7. Re: Find results reporting first portal row only - help!
                      comment_1

                      It cannot pull information that isn't there. The information that it DOES pull through is as follows:

                       

                      Suppose you have an asset Alpha, with views Red and Blue.

                       

                      Red was used in episodes 1 and 2, Blue in 2 and 3 - but we don't know that: we only know that Alpha was used in episodes 1, 2 (twice) and 3. These are the related episodes from the context of Alpha.

                       

                      From the context of Red (or Blue), the only related record in Assets is Alpha, and the related episodes are 1, 2 and 3.

                      • 8. Re: Find results reporting first portal row only - help!
                        TenaciousG3

                        OK I see what you're saying, but the usages in our case don't work like that.  We're dealing with many views of one single object, but every single view of an asset is used every time the asset it is connected to is used.  That's why I set up the tables that way.  There may be many views of one asset, but they will never be used in one instance and not in another, they're always part of a set.  Now... One asset may have 5 views, one may have 3, one may have 20... but again, they will always be used as a SET, not individually.

                         

                        I thought that since I have the AssetID present in the Asset table, the View table and the Usage table that all the records would relate to each other and would be able to get any related info from any of the tables since the AssetID is the same.  Is this not correct?

                         

                         

                        • 9. Re: Find results reporting first portal row only - help!
                          comment_1

                           


                          TenaciousG3 wrote:

                          every single view of an asset is used every time the asset it is connected to is used. 


                           

                          Well then the result above (Blue is related to episodes 1, 2 and 3) is correct, isn't it?

                           


                          • 10. Re: Find results reporting first portal row only - help!
                            TenaciousG3

                            Well I suppose it is technically correct, but retrieving the correct views is not the problem I'm having. The problem is, if I perform a search using the EpisodeNumberLookup field, though it returns the correct views, the values that appear in the usage related fields after performing the search are always the values from the first row of the portal.  So let's say an asset has records indicating use in episodes 1, 3, 4, and 12, and "1" is the episode number value that appears in the first row of the portal. if I search for views used in episode 12, I do get the correct views but the episode number field says "1" not "12".  Same is true for the other usage fields in that row (like act, scene, etc.  They are all returned as the first row's value, and not the value related specifically to episode 12.)

                            • 11. Re: Find results reporting first portal row only - help!
                              comment_1

                              That is the intended behavior: if you are search the View table, using a portal to Episodes (or Usage), you end up with a found set of Views used in Episode 12. But the found view continues to be related to ALL episodes it's been used in - and that's what portal shows.

                               

                              ---

                              A better example: you search a text field for "cat" and you find two records; in one of these the word "cat" is the last word out of 2000, way out of view.

                              • 12. Re: Find results reporting first portal row only - help!
                                TenaciousG3

                                OK I get that, but the portal is not on the layout based on the "view" table that I am searching from.  The portal is on a different layout based on the Asset table where I'm using the portal to create records in the Usage table from that layout.  The layout I'm doing the search from has no portals, it just has a related field for "EpisodeNumberLookup" which is what I am entering my search data into.

                                 

                                Oh and by the way - THANK YOU for spending the time to help me figure this out... you've been very helpful so far in narrowing things down and I really appreciate it.

                                • 13. Re: Find results reporting first portal row only - help!
                                  comment_1

                                  It makes very little difference:  the related field shows data from the FIRST related record (same as what you would see in the first row of a portal). It doesn't "know" you have searched for 12 in the current table; it's only concern is the related set in the other table.

                                  • 14. Re: Find results reporting first portal row only - help!
                                    TenaciousG3

                                    OK so that being the case, is there a way to correctly ask it give me the data I want - not the "first" related record it sees, but the actual record related to the criterea I asked for?  I have another layout based on the usage table that only searches for the assets used in an episode.  I put the episode number I want into the same field there, and it doesn't only find the first record of the portal, it finds the ones specific to my episode number search.  I realize this is returning assets not views, but still - why does it work in that layout and not the other?

                                    1 2 Previous Next