You'll need to explain what
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.
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.
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?
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.
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.
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!
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.
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.