6 Replies Latest reply on Oct 30, 2014 10:18 AM by philmodjunk

    Formula to get Record field in related database

    brian.bentley

      Title

      Formula to get Record field in related database

      Post

      I have a field in my Email Messages database that creates a relative formula for each record. I use that  formula to pull the email into the Send Email function of my Student Records database. The formula to create what I need is as follows:

      "GetNthRecord ( Email Messages::Message ; " & Get ( RecordNumber )

      My problem is the GetNthRecord/Get ( RecordNumber ) function. If I don't have ALL the records up in the found set, the record number is incorrect and will pull the wrong email message.

      Is there a function that will Get the Current Record (the one open on the screen) or get the record that corresponds to a pre-set unique serial number or the unique Record ID?

      Thanks in advance for any help you can give.

      Brian

       

        • 1. Re: Formula to get Record field in related database
          philmodjunk

          Is there a function that will Get the Current Record

          There is no need for such a function. If your script refers to fields from the current layout's table, it will reference "the current record open on that layout".

          or get the record that corresponds to a pre-set unique serial number

          In addition to using that value to perform a find on a layout based on that field's table, you can use a relationship to match to a specific record by assigning that unique serial number value to a match field. This is frequently done in relational databases in a wide variety of cases. Note that by adding another occurrence of the same table to your relationship graph, you can set up a special purpose relationship for this that will not affect existing relationships that require a different set of match fields.

          ExecuteSQL (FileMaker 12 or newer) could also be used with such a serial number to refer to a specific record's data.

          • 2. Re: Formula to get Record field in related database
            brian.bentley

            Thank you so much for your quick response.

            The problem is that when I send mail, I am not sending from the Email Messages database. I copy the generated formula in the Email Messages database and paste it into the Send Mail dialogue box of the Student Records database. Because I have created a relationship between the two databases using a global fields of Current Year in both databases, the formula placed in the Student Records database Send Mail dialogue box will pull the message from the Email Messages database. So when the formula is used, it is not referring to the current table.

            I hope I am making sense...

            Brian

            • 3. Re: Formula to get Record field in related database
              philmodjunk

              So when the formula is used, it is not referring to the current table.

              I am afraid that does not make sense. A formula can only refer to one specific table occurrence for any one field that it references. If you want that formula to refer to fields in the current layout's table, you'll need to change the expression in order for it to make that reference.

              Example: Your expression in a script step's calculation might be written as:

              Field1 & " " & TableA::Field2

              the reference to field1 will be to the current layout's current record. Table1::field2 will refer to the first related record in TableA that is linked to the current record on the current layout.

              • 4. Re: Formula to get Record field in related database
                philmodjunk

                Scratch that last example and use this one. I'm confusing calculation field syntax with script steps that contain calculations:

                Say your current layout is based on a Table occurrence named "TableB".

                TableB::Field1 & " " & TableA::Field2

                the reference to TableB::field1 will be to the current layout's current record. TableA::field2 will refer to the first related record in TableA that is linked to the current record on the current layout.

                • 5. Re: Formula to get Record field in related database
                  brian.bentley

                  "Table1::field2 will refer to the first related record in TableA that is linked to the current record on the current layout."

                  This is similar to my calculation I place in the Send Email dialogue box, Email Message Calculation field, in the Student Record table/database, only it is couched in GetNthRecord ( Table1::field2 ; Get (Record Number [referring to the overall number when all records are showing in the found set showing in the related Email Messages table/database (Table1 in your example) ] ) in order to pull that field from the related table--Email Messages-- into the send mail function of the Student Records table.

                  I've been using this formula and relationship for over 6 years. My only problem is that when I search for an email, revise it to send for this year, and forget to "Show all records" in the Email Messages table before copying the formula and switching to the Student Records table, the Record Number is wrong in the formula listed above. Apparently, the Email database figures the record number based on the found set, but the related database, Student Records, pulls the number from the complete set of all records.

                  That's why I was looking for an alternative to GetNthRecord/Get(Record Number), one that would cue the Student Records table to Get a field in the Current, or open, record in Email Messages, or a field in the Email Messages contained in the record based on a unique field, such as a serial number field or the unique RecordID that filemaker assigns.

                  Brian

                  • 6. Re: Formula to get Record field in related database
                    philmodjunk

                    Your use of GetNth record won't work like you think.

                    Using GetNthRecord to refer to a record in a related table refers to the nth record of a set of related records. If your current layout had an unsorted, unfiltered portal to that related table. The "Nth" record GetNthRecord accesses would be the record in the Nth portal row.

                    But Get ( RecordNumber ) refers to the position of your layout's current record in the layout's found set. If you have a found set of 10 records and the 5th record in that set is the current record Get ( RecordNumber ) returns a 5-- which as no relationship at all to the 5th related record.

                    And this is why I have not used it in my examples.

                    You have two basic options:

                    1) Use Set Variable to copy the data from the related table at a time when a layout based on the related table is current and the current record on that layout is the records from which you need this data. Then switch layouts and set up your email, referencing the value of this variable in order to include it in the email.

                    2) Use a relationship to match to the record that you want to supply this data to your email. This sometimes requires setting up an added relationship and you have to figure out how to set a match field in your current record to the correct value so as to be able to match to the correct record.