10 Replies Latest reply on Jan 4, 2013 10:28 AM by aklobby

    Go To Record in Unrelated Table

    aklobby

      Title

      Go To Record in Unrelated Table

      Post

           I am having a problem getting to a particular record in my database. I have a Record in a table marked Bills and I need to navigate to a record in a table occurrance marked Alerts based on a TO of Bills. Bills are referred to several committees and those referrals are displayed in the Bills Layout via portal. Now, I need to get to the alert for the correct bill for a particular referral. I can successfully navigate to the first referral using set variable and go to layout. But then I have to arrow over to the correct referral. Is there a way to write a script to get me all the way to the appropriate record (referral)? 

           Any thoughts would be appreicated. 

           Allison

        • 1. Re: Go To Record in Unrelated Table
          philmodjunk

               This doesn't sound like it should be an "unrelated table".

               Can you map out your tables and relatioships for us? So far, I can tell that you have a table for BIlls and what appears to be a table named Alerts. I'd guess that you also have a table named Committees.

               But what relationships do you have?

          • 2. Re: Go To Record in Unrelated Table
            aklobby

                 I didn't think it should be an unrelated table, as a matter of fact the same relationship scheme worked in another version of this DB using GTRR, bust still needed a script to go the rest of the way to find the actual committee. The scheme is below: 

                 Group 1 - 

                 Bills ---- Referrals --- Commiittees

                 Group 2

                 Alerts (based on Bills TO) --- Referrals --- committees --- assignments --- people --- contact method

                 I could probably base my alerts layout on Group 1 and add in the TO's forassignments, people and contact method, but when I tried that I got committees with the same person showing up several times in the one committee. Separating the groups kept the committee membership stable. 

                 What I am trying to do here is follow the passage of a bill through a committee process so the current location will change as the bill progresses but the committee referrals will stay the same. My problem could also be somewhere in the current location field. 

                 Thank you so much for helping out with this. I can get by with what I have but it is too clumsy when other people have to use my system. 

                 Allison

            • 3. Re: Go To Record in Unrelated Table
              philmodjunk

                   And what are the match fields used in these relationships? Which relationships are one to many, many to one, many to many....?

                   You can either use the Upload an Image controls located beneath "Post A Answer" to upload a screen shot (possibly after cropping) of Manage | Database | Relationships or you can use the method descirbed in the first post of this thread: Common Forum Relationship and Field Notations Explained

              • 4. Re: Go To Record in Unrelated Table
                aklobby

                     Group 1 - Bills Layout

                     pkBills---fkReferrals / fkCommReferral ---pkCommittee

                     also from the Bills TO are relationships: pkBils --- fkSponsors

                                                                                              pkBills --- fkClientPosition / fkClient ----  pkPeople

                     Group 2 - Alert Layout

                     pkBills ---fkReferrals / fkCommReferral --- pkCommittee ---fkCommAssign / fkPeopleAssign --- pkPeople ---fkContactMethod

                     also there is a relationship from the the Alert TO to a Globals table with a cartesian join to REFRESH x REFRESH and that table also has a global field for Alert Message. I could put that global field directly on the AlertBill table if the global TO is throwing this off. 

                     The assign, people and contact method TOs are there to provide a list of committee members assigned to the committee and contact infor for those members. 

                     I hope this helps.

                     Allison

                • 5. Re: Go To Record in Unrelated Table
                  philmodjunk

                       I'm doing this first part as it helps me understand what you have. Correct any errors that you see....

                       Group 1

                       Bills----<Referrals>----Committees

                       Bills::pkBills = Referrals::fkReferrals  (I'd use _fkBillID for less confusion here)
                       Committees::pkCommittee = Referrals::fkCommReferral

                       Group 2

                       Alerts----<Referrals>---Committees---<Assignments>-----People---<ContactMethod

                       

                            I have a Record in a table marked Bills and I need to navigate to a record in a table occurrance marked Alerts based on a TO of Bills

                       SInce Bills and Alerts are occurrences of the same table and you have the same exact relationship between bills and referrals that you do for Alerts and Referrals, I'm a big puzzled about why there is a problem.

                       These next two parts of what you posted is not clear to me:

                       

                            Bills are referred to several committees and those referrals are displayed in the Bills Layout via portal. Now, I need to get to the alert for the correct bill for a particular referral.

                       Since you are already on a Bill/Alert Record, why is there a problem getting to the correct bill? Aren't you already on the correct record

                       

                            I can successfully navigate to the first referral using set variable and go to layout. But then I have to arrow over to the correct referral. Is there a way to write a script to get me all the way to the appropriate record (referral)?

                       Presumably, the "first referral" is the first referral listed in the portal. But what would indentify the CORRECT referral?

                  • 6. Re: Go To Record in Unrelated Table
                    aklobby

                         I do not have a problem getting to the correct bill using set variable script step (for some reason GTRR does not give me the Alert Layout as an option to navigate to) but getting to the correct referral automatically is the issue. Yes the first referral that shows is the first in the portal.

                         What would determine the correct referral is the Current Location field. I tried performing a find where I tried to match current location with the committee name and that didn't work, probably because I did not script the find correctly. I did notice that the Current Location field was not being populated on each occurrance of the bill referrals. The current location is a pop up menu populated with committee names and the user chooses from the list where it is. On the records for referrals, the current location is showing up on one or two of the occurrances but not all (say if there are four referrals, the current location is showing up in two out of four referral records). So my big problem could be the location. I have thought about possibly having a separate table for the current location so I can poplulate that with a foreign key based on committee ids, but not sure if that is the correct solution. 

                         Allison

                    • 7. Re: Go To Record in Unrelated Table
                      philmodjunk

                           In what table have you defined this Current Location field?

                           If you are on the Bills layout, you can't use GTRR to switch to the same record on the Alerts layout as they are unrelated, but if you set up a self join relationship between Bills and a 3rd occurrence of Bills, You can use GTRR referencing this third table occurrence, but can select the Alerts layout as the layout to display the results. But there's really nothing wrong with using a variable and a scripted find. What puzzles me is why you need to change from the Bills layout to the Alerts layout. Why not just start on the alerts layout?

                      • 8. Re: Go To Record in Unrelated Table
                        aklobby

                             The current location is in the referrals table. And the need to switch to the alert table instead of starting there is the user will be updating the bill record, that is the main record the user will be dealing with daily. The user will have a need to send an alert to their client that the bill has had some action. So I would like the user to be able to update the bill record with the particular action, then click a button that takes them to an alert page where they can update a message to their client and email the alert. 

                             A

                        • 9. Re: Go To Record in Unrelated Table
                          philmodjunk

                               Yes but if the alert and bill occurrences share the same data source table, you should be able to do this from a single layout. You might consider adding a tab control with a tab panel for adding this "alert" information into the bill/alert record.

                               

                                    current location is in the referrals table.

                               Then this should not be the case:

                               

                                    On the records for referrals, the current location is showing up on one or two of the occurrances but not all (say if there are four referrals, the current location is showing up in two out of four referral records).

                               You'll need to figure out why the data is wrong and fix it before you can more towards automatically selecting the correct referral record and consider this:

                               You are describing a case where you have one Bill with multiple referrals--with a location specified for each referral. So you still need somehow to determine which LOCATION (and hence which referral) is the correct referral record.

                                

                          • 10. Re: Go To Record in Unrelated Table
                            aklobby

                                 I like the idea of the tab. I will give that a go and continue to sleuth through the database for why my current location info is not working. Thanks so much for the assistance. 

                                 A