4 Replies Latest reply on Jan 10, 2014 8:17 PM by njem

    Relationship sorting confusion


      Confused on relationship sorting.



      If I have three tbls connected by a chain of relationship shouldn't the results I get from the last table match the record found in the 2nd table?



      Tbl1 is types of widgets. Some current record is selected.



      Relationship to tbl2 which is brand of battery that went into that type of widget. May be several matching records as different brands are used over time.



      Relationship from tbl2 to tbl3 which is address of battery factory.



      All tables have auto-serial numbers which is what they link on.



      So if in the first relationship I have it sort descending, then a field on a layout showing battery brand should show me the last brand of battery ever used for the current widget. That works. I would then expect an address field to show me the address of that battery factory, but it doesn't. Just seems to show the first address it comes upon, or maybe it's the last one it finds, not sure, part of my confusion. So just dropping a field from each of the three tables on a layout, and wanting to show info on the last battery brand used, I should have WidgetA, LastBrandForA, and Address of LastBrandForA. Instead Address may not be the right one. How can that be?

        • 1. Re: Relationship sorting confusion

          You will need a global field and a separate relationship/join/tableoccurence in the relationship graph or single row portal with a portal filter applied to achieve the desired result.


          Instead of a single related field on the layout, troubleshoot by using a portal with multiple rows (tick the show scroll bar option). Keep the fields in the portal simple - only include fields from the joined table occurence (exclude related fields that are 2 or more joins distant from the layout table occurence). The portal will show you all (scroll bar may be required) of the related records.


          The order of the related records displayed in the portal can differ. By default they display in record creation order. If the join in the relationship graph has a sort order applied it overrides the original data entry creation order. Finally, the portal can also have a sort order applied which overides any sort specified in the relationship join which was overriding the original creation order.


          Bye for now.




          Michael Richards

          Brisbane (Australia)

          • 2. Re: Relationship sorting confusion

            Thanks for the response.


            I've attached a demo. In the portal it shows correctly but if I just wanted a field on a layout showing the last brand used and its address it has a mismatch between brand and address. Or if by script I just wanted to grab the value of last brand and its address it's wrong. I can force the Address tbl to sort descending which would happen to work in this case but it's brute force and not really linking data the way it should. I would prefer to understand what FM is doing.


            It's as if...the value it grabs from the Brand table is the first one it comes upon. Expected and okay, and I can control that by sorting up or down. But then it's as if there's a direct relationship from Widget to Address and it's just grabbing the first match it comes upon, regardless that the tbl in the middle is on a different record. Just ignoring the middle tbl. ?




            • 3. Re: Relationship sorting confusion

              There are multiple ways to accomplish this. Modifying your example fiel I used the Last () function to calculate the last brand name and its corresponding last address. Both of the sort orders in the relationship graph were removed so the Last() function grabs the most recent related records in each table. A sort order in the relationship or on the portal will change the result returned by the Last() function.


              How do I attach a zip archive to my reply for you to see?


              Hope this helps.



              Michael Richards

              Brisbane (Australia)

              • 4. Re: Relationship sorting confusion

                Michael, thanks for the response again. You've made me think through this. Last() is another brute force way that happens to work in this scenario but still not a real connection between data. What FM is doing is as long as I'm on a layout based on tbl1 everything happens from that perspective. It in effect has a direct relationship to tbl3. To get data from tbl3 that is limited to what is associated with a given record in tbl2 I have to move to a layout based on tbl2. So from the layout for tbl1 I could get the last record in tbl2, then go to a layout based on tbl2, find the record in question, then tbl3 would give me only correct answers. Or I could use Go To Related Records or relationship sort order or Last() as other ways to do it but ends up the same. That's a hard one to wrap my head around and I'm sure I'm going to make this mistake again because looking at the relationship graph it seems so obvious that only records in tbl3 that match tbl2 could possibly show up, but that's not what's happening because when on a layout for tbl1 it in effect has a direct relationship with tbl3. I could see where sometimes that would be handy. Not in this case. I thought I could just drop a field on the layout or load a variable from a field but instead I have to jump through steps to first isolate the right records between tbl2 & 3. Sheesh.


                Seems like a good function to add. If you have relationships 5 deep and you're on a layout for tbl1 to be able to specify that you only want data from tbl5 that is successively filtered by each of the relationships. That way you could get the first record in tbl2 and data from each successive tbl that all go together as a set, without having to go each layout to narrow each level of scope.


                Apparently you can only attach files when you start a reply then choose "Use advanced editor".