Relating files impossible task? Hopefully interesting for some ....

[dkey]

Hello

I have a series of audio files named as follows:

20141205220512 (the creation date timestamp of the file).

I need to change these file names trying to link them to the corresponding video files filmed at the same time. 

Both files where created within one or more minutes or few seconds  time frames. In any case there are no overlapping timrs between creation timestamps while the supposed range might vary even a lot,

Is there a way to create a relationship between timestamps within a range?

I created two FMP databases with the file names and timestamps with name, creationdate timestamp, but obviously I am unable to relate them as the timestamp are never the same.

I found this suggestion with an FP7 corresponding help file in the internet but I am unable to figure it out. I am not quite capable to adapt it to my own files and relate them.

[http://help.filemaker.com/app/answers/detail/a_id/536/~/findinga-range-or-records-using-a-multi-criteria-relationship]

BTW I am still in FMP ADVANCED 12

I add the fmp7 file I found but even as a starting point I got totally lost as I do not understand how to adapt it to my two files and or one file with two tables and so on.

 

Any help will be highly appreciated and thanks in advance.

 

EG536.fp7.zip

[comment]

Is there a way to create a relationship between timestamps within a range?

 

Yes, but you need to create the range first. For example, in your Audio table define two calculation fields cRangeStart and cRangeEnd that will calculate the start and end strings by subtracting and adding, respectively, a defined duration from/to the given starting point. Then define the relationship in a manner similar to the one shown in the file you have attached.

 

Alternatively, you could round down both starting points to some predetermined stop, e.g. round 5 minutes, then link the tables by these two fields using = as the realtional operator.

[dkey]

Thanks so very much, however I am not skilled and a practical example would help me a great deal .... 

What to do?

1- in the Audio table I add a cRangeStart field and a  cRangeEnd filed but how to populate this range? is there a function in FMP which allows me to do so?

 

Or

 

2-I need to convert the files as numbers and script them as such

setfield cRangeStart as Audio-60

and add to the script a second part

setfield cRangeEnd as Audio+60

which will give me a 2minutes range for each Audio file.

If this is a viable solution how can I create the relationship with the video file as still the relation numbers will not be the same

 

The problem I found (due to lack of experience I am sure) in the example file I sent is as follows

In my db I add about 1007 records each with a different name converted as timestamp firing the script I get no results whatsoever .... I am missing something certainly but I don't know how to fix that template. 

I imported the names in a clone converting them in timestamps but how to fill the global start and end fields is a mystery to me.

 

If using the additional solution,  how do I round up the starting points? I realize timestamp is calculated in seconds.

I am sorry but I am really too old and certaqinly incompetent to follow your kind suggestion as needed

Regards and Season greetings 

 

Dan

[comment]

If you want a range of ±60 seconds around the Audio start point, make cRangeStart =

Let ( [
y = Left ( StartPoint ; 4 ) ;
m = Middle ( StartPoint ; 5 ; 2 ) ;
d = Middle ( StartPoint ; 7 ; 2 ) ;
hh = Middle ( StartPoint ; 9 ; 2 ) ;
mm = Middle ( StartPoint ; 11 ; 2 ) ;
ss = Right ( StartPoint ; 2 ) ;
ts = Timestamp ( Date ( m ; d ; y ) ; Time ( hh ; mm ; ss ) ) ;
ts1 = ts - 60
] ;
10^10 * Year ( ts1 ) + 10^8 * Month ( ts1 ) + 10^6 * Day ( ts1 ) + 10^4 * Hour ( ts1 ) + 10^2 * Minute ( ts1 ) + Seconds ( ts1 )
)

and cRangeEnd the same, except + 60. The result can be either Text or Number - it should be the same type as the field holding the starting point of the video file.

[dkey]

thanks again here what I did
1-added 20 to the startpoint field as I only had 14 rather than 2014 for the year to match your
y = Left ( StartPoint ; 4 )
and keep the follwing sequence in order
The resulting numbers are perfect however I am unable to achieve the result
Might this be becausemy date time system preferences are DAY MONTH YEAR rather than MONTH DAY YEAR as in the USA?
Setting the fields as numbers this shouldn't make any difference as the results from your calculation are totally correct
I created the relationship between the Audio and Video tables as follows
VideoFile ≤cRangeStart AND ≥ cRangeEnd
however I don't get any related records at all.
I am abusing your help and I am sorry

Here the zipped file I have with all data in it

In the FMP7 files I added previously there are two global fields but I cannot figure out how to populate them ...

Well ... thanks gratefully again if you or others might find ways to help