1 2 Previous Next 15 Replies Latest reply on Nov 5, 2014 4:32 PM by mikedr

    Referencing linked table in script

    mikedr

      Title

      Referencing linked table in script

      Post

      This is driving me batty.  I'm assuming that there's an easy way to do what I want to do, but I can't figure it out.

      Say I have two tables, clients and matters.  Clients has fields name and email address.  Matters has fields number and client.  The two tables are linked as Clients::name to Matters::client.

      Now, for a current record in matters, I want a script that will send an email to the email address of the linked-to record in clients. 

      As an example, say there is a client record with name acme and email general@acme.com.  There is a matter having a number 6789 with client acme.

      A user is currently in the matter record with number 6789, and selects the script.  The script needs to look up general@acme.com from the client record that has the same value for the field name as the matter record does for the field client.

       

      What am I missing??

        • 1. Re: Referencing linked table in script
          ChadBarnard

          I would strongly encourage you to set up primary and foreign keys in your tables and use them to do the linking.

          On the Matters table, you need to set the Matter::client field as a pop up menu w/ a value list that references Clients::name.  On the Matter table, you'll also need to place a field that displays data from Clients:email.  Once you select a client from the pop up menu, the email field will auto-populate.

           

          • 2. Re: Referencing linked table in script
            mikedr

            I thought about this as a solution.  However, if I change the data in Clients::email, will it automatically change all linked Matter::email?  I.e., say when you create a record in Matter, and select Client Acme, the Matter::email in question will be set to acme@acme.com.  Now, say in the primary record in Clients, you change Client::email from acme@acme.com to new@acme.com -- will Matter::email automatically update?

            • 3. Re: Referencing linked table in script
              mikedr

              And actually, just trying to do this, how to I "place a field that displays data from Clients:email"?  In Matters, if I create a new field email, what would its type be?  Calculation?

              • 4. Re: Referencing linked table in script
                ChadBarnard

                Now, say in the primary record in Clients, you change Client::email from acme@acme.com to new@acme.com -- will Matter::email automatically update?

                Yes, it will automatically update.

                 how to I "place a field that displays data from Clients:email"?

                When placing your email field on the Matters layout, the pop up window will ask you to specify a field.  Just navigate to Clients:Email.  If you look under the Data tab on the Inspector, the first thing will be Display Data From, and since you specified the field when you placed it, it will already be Clients:Email.

                 In Matters, if I create a new field email, what would its type be?  Calculation?

                When you place the field mentioned above, it will be a regular text field, not a calculation.

                • 5. Re: Referencing linked table in script
                  philmodjunk

                  There should not be any email field in Matters, only in client.

                  • 6. Re: Referencing linked table in script
                    ChadBarnard

                    I was talking about layout.  I think you're (Phil) talking about table, right?

                    • 7. Re: Referencing linked table in script
                      mikedr

                      I think I need a real field, though, in order to reference it in the script, right?

                      I did find a solution.  My problem is a bit more complicated than I had originally said.  I have three tables, Matters, Clients, and Locations.  Each matter is associated with a client and a location.  A client is associated with multiple locations.  The email is in the location table.

                      I've linked matters to clients to locations via client name.  I've also linked matters to locations via location name, which resulted in a new "virtual" (?) table in the manage relationships area, Matters 2.

                      Now, in Matters, I created a new field, which is a calculation that is evaluated from the context of "Matters 2" (since that's where the link between locations and matters is), to select the email address from the Locations table.  In the comments under Manage database, this shows as "Unstored, from Matters 2, = Locations: Email address". 

                      I tested this and it appears to work.  If I change an email address at a location, it instantly changes in the matters.  Further, I can reference the email address easily in a script.

                      Is there anything "wrong" with my approach?

                      • 8. Re: Referencing linked table in script
                        philmodjunk

                        You need just the email field in your clients table. From the context of a related record in Matters, you can refer to the Client::Email field without the need of defining an email field in matters table. And you can also add the client::email field directly to your Matters layout.

                        • 9. Re: Referencing linked table in script
                          mikedr

                          But how would I refer to this in a script?  I get that I can create a layout that references another field in another table, but I was unable to see anything when creating a script that permits me to do "from the context" . . .

                          • 10. Re: Referencing linked table in script
                            philmodjunk

                            If the Tutorial: What are Table Occurrences? is named "Client" and the field is named "email", you would refer to it as Client::Email. In the specify calculation dialog that you'd use to refer to this field, there's a drop down list of table(occurrence)s where you can select different table occurrences to see the list of fields defined for each. If you double click a listed field, it's added to the calculation with the correct table Occurrence::Name format set up for you.

                            • 11. Re: Referencing linked table in script
                              mikedr

                              That unfortunately didn't work.  I think this is why.

                              Tables client, matter, and location are all linked to one another by the field client name

                              Tables matter and location are linked to one another in two ways, by client name, and by location name

                              When I do what you're suggesting (Location::Email) it's pulling the first location record that has a matching client name, instead of looking at location name.  That is, in the calculation dialog for a script, there's no way to specify that the context is "Matters 2" (which defines the matter<->location link via location name) as opposed to "Matters" (which defines the matter<->location link via client name).

                               

                              • 12. Re: Referencing linked table in script
                                philmodjunk

                                This is the first mention that i've seen of a table named "location".  All of my answers are from the assumption that you have two tables, not three with one client record linking to possibly many matters records. In that context, a record in Matters never links to more than one record in Client and you can refer to Client::Email from the context of Masters and you'll get the correct result.

                                Change the data model by adding a third table with a different relationship and put the email in that third table, and you will get different results.

                                You are correct that when you turn this around and refer to data on the "many" side of a relationship, you get data from the first related record. What is not clear to me is to how you would determine which record, out of a group of related records, has the data you want to use for your email. There are ways to access the last or the Nth related record. There are ways to use the value in some other field to determine which related record to access. Which of those methods you should use here is not a question I can answer from the information thus described.

                                • 13. Re: Referencing linked table in script
                                  mikedr

                                  Sorry yes -- I mentioned the location table in a comment above:

                                  I think I need a real field, though, in order to reference it in the script, right?

                                  I did find a solution.  My problem is a bit more complicated than I had originally said.  I have three tables, Matters, Clients, and Locations.  Each matter is associated with a client and a location.  A client is associated with multiple locations.  The email is in the location table.

                                  I've linked matters to clients to locations via client name.  I've also linked matters to locations via location name, which resulted in a new "virtual" (?) table in the manage relationships area, Matters 2.

                                  Now, in Matters, I created a new field, which is a calculation that is evaluated from the context of "Matters 2" (since that's where the link between locations and matters is), to select the email address from the Locations table.  In the comments under Manage database, this shows as "Unstored, from Matters 2, = Locations: Email address". 

                                  I tested this and it appears to work.  If I change an email address at a location, it instantly changes in the matters.  Further, I can reference the email address easily in a script.

                                  Is there anything "wrong" with my approach?

                                  Basically, Matters gets an email field via Matters 2 . . . .

                                  • 14. Re: Referencing linked table in script
                                    philmodjunk

                                    There's nothing "wrong" with that approach, but it may be needlessly complex. It depends on the "context" in place when your script executes. In other words, if you are on a particular layout when you execute this script, you may not need to define the calculation field in order to access the correct email address.

                                    1 2 Previous Next