1 2 Previous Next 24 Replies Latest reply on Dec 21, 2015 3:29 PM by Mike_Mitchell

    *main* e-mail field

    jekins

      Hey folks-

       

      I'm new to FM and am trying to figure out how to make a calculated field in which it gets certain data from another table.

       

      Specifically, I have a People table with a "Main Email" field.  I want this to be a stored, calculated field that looks at the Emails table (which consists of a "Person ID" foreign key, "Email", and "Is Main?" field.  I want the calculated field to take from the Email table the email address associated with the person which is marked "Is Main" (number using 1 like boolean).

       

      If it were SQL I'd use a query like:
      SELECT Emails.Email

      FROM Emails

      WHERE 'Emails.Person ID' = 'People.Person ID'

       

      But I have no idea how to get values from other tables using FM calculations.

       

      Thanks for help,

      Justin

        • 1. Re: *main* e-mail field
          Mike_Mitchell

          Justin -

           

          Welcome to FileMaker! In the FileMaker world, data from related tables is shown in the current table through a couple of methods:

           

          1) Simply showing the related field on the current layout.

          2) Copying the data from the related field to the current field in the current table.

           

          In many cases, you should use version 1, since this avoids duplicating data across tables (a violation of basic normalization procedures). For that, create a relationship on the Relationships Graph between, in your case, People and Emails, connecting Person ID to Person ID. Then you would just put the field "Emails::Main Email" on your Person layout. The advantage of doing this is that it will automatically reflect the data across if the email ever changes, and is preferable to using a calculation because of sorting and searching performance.

           

          If for some reason you wanted the data to be frozen in time (such as with a price on a line item in a catalog vs. the same item on an invoice), then you would use either a Lookup or an auto-enter calculation to copy the data across to the current table. (There are some cases where this is also beneficial for performance reasons.) You can do this by selecting File > Manage > Database, choosing the target field for the data, and clicking "Options". (Or, just double-click the field.) On the Auto-Enter tab are options for lookup and calculation.

           

          HTH

           

          Mike

          • 2. Re: *main* e-mail field
            Extensitech

            Create a field in Persons that's just equal to 1 (a "Constant" field).

            Add a table occurrence to the relationship graph for "Person_Email_main"

            Create a relationship between "Persons" and "Person_Email_main", with the following parameters:


            Persons::ID (pk) = Person_Email_main::Person ID (fk)

            Persons::Constant = Person_Email_main::Main?


            (btw, your SQL statement would've needed something like the second parameter, too, to prevent picking up "non-main" email addresses.)


            Create a "Main Email" field in Persons as a calculation = Person_Email_main::Email Address


            Now for the potentially bad news: I'm not sure if you used the word "stored" in the FileMaker sense, but if so, values calculated from other tables cannot be stored, and values derived from SQL should not be stored, since stored values won't automatically reevaluate. They are unstored, meaning they are not indexed, and evaluate as needed.  You can still search "Main Email" in Persons, and it'll always be up-to-date, but it won't be indexed (so searching through many thousands of People might have a performance impact), and you cannot use it as the "far" side of a relationship parameter.


            If you do need "Main Email" to not be unstored (by that definition) you'll need a mechanism for setting, and updating, that field, using the same calculation. FM won't automatically set and update a "Main Email" stored text field for you in Persons. I suspect, though, that you'll find that an unstored calc in Persons is sufficient.


            HTH


            Chris Cain

            Extensitech

            • 3. Re: *main* e-mail field
              Mike_Mitchell

              Chris -

               

              If I understand the OP's question, he has a separate field for "Main Email", rather than a table of emails with one marked "Main". Could be wrong, but I don't think the dual predicate is necessary.

               

              Mike

              • 4. Re: *main* e-mail field
                Extensitech

                You could well be right (you so often are!) but I was going from this:

                 

                ...from the Email table the email address associated with the person which is marked "Is Main" (number using 1 like boolean).

                 

                Chris Cain

                • 5. Re: *main* e-mail field
                  Mike_Mitchell

                  Ah. Missed that. In which case, the dual predicate is the right answer. 

                  • 6. Re: *main* e-mail field
                    jekins

                    Thanks guys for the help --

                    Extensitech,

                    1) It seems silly to me that FileMaker doesn't allow relationships conditional on constant values (so you have to create a constant field), but anyway...

                    2) I was with you up until the end, but I don't get why I can't just use Auto-Enter / Looked-up value, and specify the related table as the table occurrence with the conditional select of the main e-mail.  In the "Copy value from field" I thought I should just use the Email field from that second table.

                     

                    It's just leaving it empty.

                     

                    Another thing I don't get is why creating the Constant field in People and assigning it a global value locks up records navigation so you can't move around to different records.

                    • 7. Re: *main* e-mail field
                      jekins

                      Cancel that last thing... I was stuck in Find mode.

                      • 8. Re: *main* e-mail field
                        jekins

                        I guess I should also explain my purpose...

                         

                        The only reason I even bother having the Main Email field is not for display in the People layout, but rather I thought it would be convenient for other purposes including scripts later on so I could quickly and easily grab the main e-mail address for a person and send them some communication or something.

                        • 9. Re: *main* e-mail field
                          jekins

                          One more thing...

                          In tutorials on the web I see screenshots that show the option "Do not store calculation results" in storage options, but mine (I'm using FM 14 is missing.  I only get the option to turn on or off indexing.  Is there something I'm missing?

                           

                          I tried both Lookup as well as Calculation and the result is the same... It does store the info, and the only way the field is updated is if I type something (anything) in the field to update it... then, if I leave the record and go back, it's been updated according to the lookup/calculation.

                          • 10. Re: *main* e-mail field
                            Mike_Mitchell

                            jekins wrote:

                             

                            One more thing...

                            In tutorials on the web I see screenshots that show the option "Do not store calculation results" in storage options, but mine (I'm using FM 14 is missing.  I only get the option to turn on or off indexing.  Is there something I'm missing?

                             

                             

                            Very likely. Where are you looking? Can you post a screen shot?

                             

                            I tried both Lookup as well as Calculation and the result is the same... It does store the info, and the only way the field is updated is if I type something (anything) in the field to update it... then, if I leave the record and go back, it's been updated according to the lookup/calculation.

                             

                            What happens if you just click outside a field (or press Enter on the numeric keypad)?

                            • 11. Re: *main* e-mail field
                              jekins

                              The first image I pulled from Google - the second is my screenshot -- I have no option for unstored/recalculate when needed...

                               

                              httpatomoreillycomsourceoreillyimages45289.pngScreen Shot 2015-12-21 at 8.51.58 AM.png

                              • 12. Re: *main* e-mail field
                                Mike_Mitchell

                                The first image is a calculation field. The second is a non-calculation field. If you're using an auto-enter calc or a lookup, you don't have the option to set it to unstored.

                                • 13. Re: *main* e-mail field
                                  jekins

                                  Ahhhhhh!

                                  • 14. Re: *main* e-mail field
                                    Mike_Mitchell

                                    Actually, slight amendment on that. To set a field as unstored, you set the storage to Global on the Storage tab. So it is possible to set a field as unstored when it's not a calculation; it just works differently.

                                    1 2 Previous Next