Associate Information from Text Document to Existing Records

[bithurler]

Good Morning,

I'm trying to develop a Filemaker solution and have reached a bit of a road block and I'm hoping I can get some help from this great community.

Ihave a filemaker database that is taking information on a film set. It records information from the camera. Now that most cameras are digital I need to associate file names to the records that I am creating. Since I can't access the files until after the shoot, also the camera generates random characters to prevent over writing of files I need to do this as a multi-step process. Here is the process I have in mind.

The film shoot is broken into days. Each day has a hard drive associated with it. I have an Applescript that searches for only the files that I need and drops them into a Text (txt) document. This is what I need to happen:

Using information contained in a calculation field that builds the approximate filename minus the random characters, I need to search the contents of the txt file, find the full file name and insert that into a field in the record.

Any thoughts how I get this solved?

[comment]

An example or two would be useful here, I think.

[bithurler]

An example or two would be useful here, I think.

So as an example:

Then generated filename which is in a field called tempfilestorage would be B148_C003_1119xx.r3d

The record of the real file in the text document would be:

/Volumes/Bithurler Test Data//Ramville day 15b/B148_11192X/B148_11192X.RDM/B148_C003_1119UN.RDC/B148_C003_1119UN.R3D

I would need that file path to be copied into a field in the record with the tempfilestorage. It would be a new field called File Path.

Make it a little clearer?

[comment]

Make it a little clearer?

I am not sure. Is it always the last two characters in the filename that are unknown? If yes, you could simply import the "real" files (as reference) into a Media table, extract the "clean" filename and define a relationship to your temp table, matching on the result.

Of course, you could do the same with your text file - provided your values are return-separated. Or extend the Applescript to write the results to records in a Filemaker table instead of a text document.

[brian rich]

So as an example:

Then generated filename which is in a field called tempfilestorage would be B148_C003_1119xx.r3d

The record of the real file in the text document would be:

/Volumes/Bithurler Test Data//Ramville day 15b/B148_11192X/B148_11192X.RDM/B148_C003_1119UN.RDC/B148_C003_1119UN.R3D

I would need that file path to be copied into a field in the record with the tempfilestorage. It would be a new field called File Path.

Make it a little clearer?

Pull the whole of your text file into a local variable in Filemaker - lets call this $fileList

You extract the part of the filename before the two random characters e.g. B148_C003_1119 from the tempfilestorage B148_C003_1119xx.r3d into a local variable - say $fileStart

You now loop through $fileList a line at a time and test the result of patterncount(getValue($fileList;$loop);$fileStart). When this is greater than 0, you have the full filepath in getValue($fileList;$loop) including the actual file name.

Rather than using an Applescript, you might want to look at the Scriptmaster plug-in from 360Works:

http://www.360works.com/scriptmaster/#modules

This includes a List Files in Directory(Path) function that would allow you to build the list directly from the file system without the need to fire the Applescript off (which is sometimes unreliable in my experience). This would only work if the files are on a local volume with your Filemaker application.

HTH

Brian

[bithurler]

Pull the whole of your text file into a local variable in Filemaker - lets call this $fileList

You extract the part of the filename before the two random characters e.g. B148_C003_1119 from the tempfilestorage B148_C003_1119xx.r3d into a local variable - say $fileStart

You now loop through $fileList a line at a time and test the result of patterncount(getValue($fileList;$loop);$fileStart). When this is greater than 0, you have the full filepath in getValue($fileList;$loop) including the actual file name.

Rather than using an Applescript, you might want to look at the Scriptmaster plug-in from 360Works:

http://www.360works.com/scriptmaster/#modules

This includes a List Files in Directory(Path) function that would allow you to build the list directly from the file system without the need to fire the Applescript off (which is sometimes unreliable in my experience). This would only work if the files are on a local volume with your Filemaker application.

HTH

Brian

The volume would always be local. I'll have a look at it. Thanks for the info!

I am not sure. Is it always the last two characters in the filename that are unknown? If yes, you could simply import the "real" files (as reference) into a Media table, extract the "clean" filename and define a relationship to your temp table, matching on the result.

Of course, you could do the same with your text file - provided your values are return-separated. Or extend the Applescript to write the results to records in a Filemaker table instead of a text document.

I like this idea, but my concern is, how do I delete the table when I import and perform the find. This would happen over multiple drives so the table would need to be cleared then re-imported with each file list.

[comment]

the table would need to be cleared then re-imported with each file list.

Why? Perhaps I am missing something, but I don't see why you wouldn't just add records to the table as more footage becomes available.

Also, there is no find to perform: your temp records will be automatically linked to the media records with matching (partial) filename.