3 Replies Latest reply on Jul 6, 2009 4:52 PM by troyecol

    Calculations based on adjacent records



      Calculations based on adjacent records


      No specific problem yet, this is a proactive inquiry before I stumble down too many cul de sacs.


      I have a series of records including geographic locations and I want to calculate a series of paramters including distance, time, velocity based the current and prior record (Record-1). Some databases I've worked with have a hard time referencing information outside the current record, some are more graceful.  Is there a straightforward way to reference relative record IDs in Filemaker (I'm still using FM9) or would I need to do something like run a script and cycle through records and hold prior information in variables?



        • 1. Re: Calculations based on adjacent records

          A lot depends on how you determine two records are "adjacent". If each record has a serial number ID and adjacent records would always be "current record's serial number + 1" or "current record's serial number -1" then a relationship could be created to refer to an adjacent record. Please keep in mind that this technique breaks down the instant you delete a record and thus create a gap in your sequence.


          Alternatively, you could store the primary key value for an "adjacent" record in a field and use it, though you would need to design carefully in order to make sure that this link is kept current at all times and this can be difficult to do in many cases.


          I think that explains why most database systems have trouble with this type of referencing scheme and this is why we often end up with a script that cycles through records in order to manage this type of issue.


          Perhaps you could give an example of what you want to do?

          • 2. Re: Calculations based on adjacent records

            The term "previous record" is somewhat problematic in a relational database. Conceptually, records are an unordered set. Which record is previous depends on the current found set and the current sort order. If you are sure you have these the way you want them, you can use the GetNthRecord() function to refer to data from another record.


            Alternatively, you can refer to another record by using a self-join relationship; this will ignore any found set/sort order and follow the sort order as set for the relationship to determine which record is "previous".



            Note also that these calculations are by necessity unstored, and refreshing a layout may be rather slow if you are showing many records at once.

            • 3. Re: Calculations based on adjacent records

              The GetNthRecord() function looks like just the ticket I need. How did I miss that? Probably worth a solution check but I'm not yet set up to verify.


              The dependence of calaculations on found set/sort order is a feature for me (for now) as one of the purposes I have is for data screening so I will want a recalculation when records are flagged for exclusion thus creating a new previous/next combination. The speed hit may be an issue but I'll have to experiment and see. No real issue if I do the calculations in say Excel so hopefully FM will perform OK.


              Thank you PhilModJunk & comment for the thoughtful responses.