8 Replies Latest reply on Apr 4, 2011 5:46 PM by webstyr

    Viewing related records

    webstyr

      Title

      Viewing related records

      Post

      Hi,

      I am scratching my head on this one.
      Here is what I have:
      Layout: Three fields - account, staff, contact
      Database: Four tables - account, staff, contact, po
      Relationships: Six relationships 
         account to po using id_account as key 
        po to account using id_account as key
         staff to po using id_staff as key
         po to staff using id_staff as key
         contact to po using id_contact as key
         po to contact using id_contact as key
      The hyperlink to the left of each field triggers a script that looks like this (substitute STAFF and ACCOUNT for CONTACT for the other two):
      The attached script works great for the contact field, but the other two don't work. All scripts are the same except for the table specific parts.
      I'm stumped! Anyone out there have any ideas as to why the contact link works, but the other two don't?
      Thanks!

        • 1. Re: Viewing related records
          philmodjunk

          Shouldn't need two relationships for every pair of tables/fields. They work both ways. Though this can be useful, in some cases, when you set up an Anchor Buoy format to your relationships map.

          Upon what table occurrence is this layout based? (See show records from in layout setup...)

          The table occurrence names in your example (T24b_po....) suggest your relationships may be more complex than you describe at the beginning.

          Account----<PO>---staff
                              |
                              ^
                         Contact

          Appears to be the relationships you have here, but those naming conventions make me wonder if I may be incorrect here...

          • 2. Re: Viewing related records
            webstyr

            Sorry, yes, the layout is PO where the three fields are located. Your diagram is correct.

            Naming convention for table occurrences is as follows:

            T24b_po_CONTACTS||id_contact|

                      ^         ^             ^

                   table      table         key

                    from       to

            So the PO table is related to the CONTACTS table using id_contact key field.

            The T24B is just used to keep relationships organized.

            Hope this helps clarify things.

             

            • 3. Re: Viewing related records
              philmodjunk
              The attached script works great for the contact field, but the other two don't work. All scripts are the same except for the table specific parts.
               
              Can you confirm that related records in the other tables actually exist? If there are no related records, nothing will happen when the script is performed and that would match what you describe here.
              One of the sometimes dangerous aspects of Go To Related Records (but not with the above script) is that the system will not report any error with a dialog, stay on the current layout and continue executing the script if there are no related records to go to. It's often necessary to trap for an error code right after the GTRR step or check for the existence of related records just before the GTRR step so that you script can handle this issue safely.
              • 4. Re: Viewing related records
                webstyr

                I verified that the related records exist.

                In the STAFF table 

                NAME_FULL id_staff

                Andrea Bishop 02

                In the ACCOUNT table

                account id_account

                South Jordan 000047

                The id numbers and names match what is in the PO table. The contact is working so I'll leave that alone. From what I've done it looks like this should work. I am totally confused.

                • 5. Re: Viewing related records
                  philmodjunk

                  Are all ID fields of type number or all of type text? Any chance you have a field with leading zeroes of type text matching to a field without leading zeroes on the other side? (I prefer to keep them all of type number, and then include any leading zeroes in a calculation field that combines the number with the leading zeroes solely for labeling/display purposes.)

                  (one way to check GTRR issues is to temporarily drop a portal to the related records on your starting layout. If you don't see any records in the portal, you have an issue with your relationship.)

                  • 6. Re: Viewing related records
                    webstyr

                    I checked the field types.

                    In the ACCOUNTS table 

                    id_account is a Number type and in the PO table it is a Text type. I changed the type to Number in the PO table. Didn't make any difference.

                    In the STAFF table

                    id_staff is a Text type in the PO table and a Text type in the STAFF table.

                    I dropped three portals onto the PO layout and pointed each portal to the appropriate place, po_CONTACT|id_contact, po_STAFF|id_staff and po_ACCOUNT|id_account. THe portal for CONTACT worked fine, the other two do not. It looks like I have a problem with my relationship then, but I don't know what it would be.

                    I also verified there was no difference on the leading zeros on the main table and related table.

                    • 7. Re: Viewing related records
                      webstyr

                      I copied both of the non-working portals to another layout and changed the portal info and both of them worked. Does this help in narrowing down the problem?

                      • 8. Re: Viewing related records
                        webstyr

                        I found the problem! In the Relationships I was only expanding the table occurrence which had id_account and id_staff at the top. Since it was at the top I assumed the link was made to the other table. When I click the box between the two table I found one of the table did not have the id_account set in the relationship. Aaarrrrgghhh!! Lesson learned the hard way! I'd probably still be looking through code if it hadn't been for Phil's help. Thanks Phil!