7 Replies Latest reply on Sep 13, 2013 12:40 PM by philmodjunk

    How do I calculate based on different fields in two different related records?

    JodiGronborg

      Title

      How do I calculate based on different fields in two different related records?

      Post

           Greetings! So I have a table with measurements on frog calls, and a related table of notes measurements. Each call has two or more notes. I need to calculate internote intervals. So in the call layout I want a calculation fieldd that will look at notes for that call and...subtract the value in the 'end time' field for the second to last note (note for which 'unit category' field contains "2l") from the value in the 'begin time' field for the last note (unit category = "ln").  Help!

        • 1. Re: How do I calculate based on different fields in two different related records?
          philmodjunk

               You'll need to explain what

               

                    'unit category'

               Has to do with all of this.

               Also please describe how the two tables are related. It's possible to extract the needed data via some GetNthRecord calls, but need to know whether that category field will change my understanding of how this would work.

          • 2. Re: How do I calculate based on different fields in two different related records?
            JodiGronborg

                 In the notes table, the field 'unit category' designates its position in a call as last note ("ln"), 2nd to last note ("2l"), 3rd to last note ("3l"), etcetera. The calls and notes tables are related by matching 'file' fields (are from the same recording) and by 'selection' field in the call table matching the 'call selection #' field in the notes table.

            • 3. Re: How do I calculate based on different fields in two different related records?
              philmodjunk

                   Sorry, but that's still a bit vague. Can you upload a screen shot of the relevant portion of Manage | Database | Relationships?

                   Why do you need to mark the records by whether they are "last", next to last, etc? If you set up an unsorted portal to notes, won't the record with "ln" in category automatically appear as the last entry in the portal? and "2l" as the record just before it?

              • 4. Re: How do I calculate based on different fields in two different related records?
                JodiGronborg

                     The notes are designated as ln, 2l, 3l, etcetera so that I can get statistics (such as average amplitude or duration for the last notes) on them specifically. 

                     If there is a way to subtract the end time of the second to last note from the begin time for the last note of a call without using the field that designate their position in the call, that's fine by me.

                     Help!

                • 5. Re: How do I calculate based on different fields in two different related records?
                  philmodjunk

                       Yes, but I needed to be sure what I am about to suggest would work. And I repeat my question:

                       

                            If you set up an unsorted portal to notes, won't the record with "ln" in category automatically appear as the last entry in the portal? and "2l" as the record just before it?

                       That's a crucial detail that has to be true in order for the method that I have in mind to work.

                       If your answer to my quoted question is true and this is from the context of a specific record in the Calls table, you can compute the value you want with this expression:

                       Let ( cnt = count ( Notes::Begin File ) ; If ( Cnt > 2 ; Last ( Notes::Begin Time _s_ ) - GetNthRecord ( Notes::End time _s_ ; cnt - 1 ) ) )

                       This expression returns null if there are not at least two related records in Notes.

                       Also: is the value in acoustic files::file unique to each record in the acoustic files table? If so, you should set a unique values validation field option on that field.

                        

                  • 6. Re: How do I calculate based on different fields in two different related records?
                    JodiGronborg

                         You are brilliant! I set up an unsorted portal and the notes did appear in chronological order. I used your calculation and it worked! Initially it only worked for calls with three or more notes, but changing it to Cnt >1 fixed that. And I was able to modify your calculation to get the second to last internote interval as well.  :)  Immediate issue resolved! 

                         Any advice for learning the calculation-lingo better? I could never have come up with that calculation on my own.

                         Many, many thanks!

                    • 7. Re: How do I calculate based on different fields in two different related records?
                      philmodjunk

                           Good catch on the >2 mistake. It should indeed be >1 not >2.

                           I'm not sure which training resource is best suited for learning more about calculations in FileMaker. My own personal history: BS in computer science followed by years of work with Filemaker Pro--started with FileMaker Pro 2.5, isn't a practical one to suggest for others. wink

                           I can point you to the fact that if you look up "function" in FileMaker help, you can get to two useful function lists: An alphabetical list of all functions and another list where they are grouped by "category". Either list is a list of hyperlinks so that if you see a function with an interesting name, you can click it to get to the help entry for that function.

                           But admittedly, that just touches on part of what you need to know as understanding both the syntax of FileMaker calculation expressions and how the Tutorial: What are Table Occurrences? context and defined relationships affect your results is also important.