Dynamic Relationships

[LLCadyJ]

Not sure if this can be done but if it can I know someone in this forum can answer.

Is there a way to have a dynamic relationship. I want my main file to get related information from a different related file for each record. My main file has a record for each day and I have separate file for each day. The day file have 2 to 3 thousand records for each day, they are named by date yyyymmdd.

Thanks

Joe Cady

[Ugo DI LUCA]

I want my main file to get related information from a different related file for each record.

It is surely possible fields could be entered though lookup to different files.

My main file has a record for each day and I have separate file for each day. The day file have 2 to 3 thousand records for each day, they are named by date yyyymmdd.

Not sure I finally clearly understood. Where are the different files here. Main File and Day File ?

Thanks

Joe Cady

[LLCadyJ]

Thanks for the reply Ugo,

Let me try to explain. I made a cycling training log using FM years ago(constant work in progress). Last year I bought a heart rate monitor(hrm) that records my heart rate, altitude, cadence and power output every five seconds.The hrm came with software to download and analyze the data. I wanted to incorporate the data that gets downloaded into my existing FM training log(main file) that I already have setup. I downloaded the hrm data using their software; the hrm software makes a text file for each day. I imported the text file data into a new FM database(daily file) to do some calculations on the data.The database I made has 2-3 thousand records for each day. I made a daily file for each days data. I wanted to relate or lookup some calculated data like averages, min, max and standard dev from each daily file into my main file.

Hope this make sense.

Joe

[keshalyi]

WEll... ugh. That's all I can say, I know exactly what you're saying, but, there is no way to make the target of the relationship dynamic, that I know of anyway. Now, that said...

You have two options. One, since chances are you won't need to know your heart rate at 13:55:05, specifically, etc, you could just, every day, have the database compile the iportant data (standard deviation, average, median, whatever) and export thos to a database of days, then empty out your sampling data.

The other option, might be to script the data extraction, somehow. Like, if you give them standard filenames (say, the file for Feb 14, 2003 is called 02142003.fp5), then you could make a calculation that returns the filename, and feed that out to an applescript that could retrieve information from the remote file?

[CobaltSky]

What you're describing is not dissimular to a lateral separation (L-Sep) file architecture, as used in some systems that are required to accommodate very large volumes of data.

Although relationships themselves cannot be made dynamic as such, there are various techniques that can be used to make them work as though they are.

Before going into that, however, it occurs to me to wonder if there is any reason why you are putting your 2-3 thousand records for each day into a separate file for each day? Unless the quantity of data on each record is large, it would make sense to bring all the records (date stamped in a field set up for the purpose) into a single file of track data. Assuming you're training daily, that will only accumulate around a million records a year, so assuming the data on each record is not pages long, it will be a good few years before you approach the 2Gb file size limit (at which time you'd need to develop an archiving routine).

If all your records are in the one related file, that will make it rather easier to develop a regime of analysis which surveys the associated files in a slice and dice fashion.

Notwithstanding the above suggestion, the typical L-Sep architecture uses multiple relationships (ie one per related file), and then references the apporpriate relationship by calculation to bring across the data appropriate to the current record. Such calculations can be unstored calcs or auto-enter calcs according to the requirements of the solution.

In your case, however, unless you want to manage your related files in manageable sized batches (eg groups of fourteen) summarising the data for one group and archiving them before moving on to the next batch (and naming each group with the same names so that while they reside in the same directory as the main file, they will be referenced by the same pre-established relationships), you will need to script a rotation procedure which remotely manages the naming of the appropriate related files (eg using the Troi File plug-in or equivalent) to match up the required file to the appropriate relationship to ensure its data is accessible to the current record.

In this latter scenario, it will be necessary for you to determine how many records you will want to view, address or summarise at one time, and you will need a corresponding number of relationships. Once they are in place, you will need to script a navigation schema which, when a record is to be displayed, identifies the first unused relationship, renames the required (dated) file to the appropriate name to correspond with that relationship., stores a reference (to indicate which relationship the record should reference) within the record, then displays the record (thus opening the file). The navigation system will also have to identify files no longer being addresed, close them and rename them to their original dated file names by way of housekeeping.

It can be done - and provided it is planned and executed properly, it can be made to operate seamlessly and consistently. However there is no denying that the set-up is complex and a fair stretch of work is involved, such that you may think it overkill for the requirements of your training db.

Which is why - unless there is some other factor that you haven't mentioned - I'd encourage you to consider sequential date-stamped imports into a single file of track data.

[LLCadyJ]

Thanks for the advice Ray, I am going to go with your suggestion and put all the records in one file and date stamp each record. At the end of the year I archive the file and start a new one.

Joe