I'm not sure where to post this but it does involve complex calculations but also scripting.

There is an example file to do this at FileMaker Magazine

I incorporated this into an FM6 database for myself a few years ago and haven't looked at it since. I've just come out from under a rock and converted to FM 8.5 but the calculations seem to have broken, e.g.

There is a calculation called "Power Path" which maps the file's file path and a script then launches the file. In FM6 the field definition is:

Case(



Left(FilePath, 7) = "http://",



FilePath,

Left(FilePath,2) = "./", 



Case(

Status(CurrentMultiUserStatus) = 2, "Shared File - No Path",



Substitute(

Substitute(

Substitute(



Status(CurrentFilePath) & Substitute(FilePath, "./", "")



, Status(CurrentFileName), "")

, "file://", "")

, "/", Case(Abs(Status(CurrentPlatform)) = 2, "", ":")

)

),





Left(FilePath,3) = "../", 



Case(



Status(CurrentMultiUserStatus) = 2, "Shared File - No Path",



Left(



Substitute(

Substitute(

Substitute(

Status(CurrentFilePath)

, Status(CurrentFileName), "")

, "file://", "")

, "/", Case(Abs(Status(CurrentPlatform)) = 2, "", ":")),



Position(



Substitute(

Substitute(

Substitute(

Status(CurrentFilePath)

, Status(CurrentFileName), "")

, "file://", "")

, "/", Case(Abs(Status(CurrentPlatform)) = 2, "", ":")), 



If(Abs(Status(CurrentPlatform)) = 2,"",":"), 



1, 



PatternCount(



Substitute(

Substitute(

Substitute(

Status(CurrentFilePath)

, Status(CurrentFileName), "")

, "file://", "")

, "/", Case(Abs(Status(CurrentPlatform)) = 2, "", ":")),



 If(Abs(Status(CurrentPlatform)) = 2,"",":")) - PatternCount(FilePath, "../")



)

) & Substitute(Substitute(FilePath, "../", ""), "/", If(Abs(Status(CurrentPlatform)) = 2,"",":"))

),





Left(FilePath,2) = "*/" and not IsEmpty(_NetworkServer),



Substitute(

Substitute(



FilePath

, "*", "fmp5://" & _NetworkServer)

, " ", "+"),





Substitute(



Case(



Left(FilePath, 1) = "!" and not IsEmpty(_RootPrefix),



_RootPrefix & Substitute(FilePath, "!/", ""),



FilePath

)



, "/", 



Case(

Abs(Status(CurrentPlatform)) = 2, "", ":"

)



)





)

In FM 6 this Action path is returned for the file "LowDye.pdf" in the folder "_Handouts" one level below the Document Manager file which is in "My Folder":

tell application "Finder" to open file "Macintosh HD:My Folder:_Handouts:LowDye.pdf"

After conversion, the field definition is:

Case(



Left(FilePath; 7) = "http://";



FilePath;

Left(FilePath;2) = "./"; 



Case(

Get(MultiUserState) = 2; "Shared File - No Path";



Substitute(

Substitute(

Substitute(



Get(FilePath) & Substitute(FilePath; "./"; "")



; Get(FileName); "")

; "file://"; "")

; "/"; Case(Abs(Get(SystemPlatform)) = 2; ""; ":")

)

);





Left(FilePath;3) = "../"; 



Case(



Get(MultiUserState) = 2; "Shared File - No Path";



Left(



Substitute(

Substitute(

Substitute(

Get(FilePath)

; Get(FileName); "")

; "file://"; "")

; "/"; Case(Abs(Get(SystemPlatform)) = 2; ""; ":"));



Position(



Substitute(

Substitute(

Substitute(

Get(FilePath)

; Get(FileName); "")

; "file://"; "")

; "/"; Case(Abs(Get(SystemPlatform)) = 2; ""; ":")); 



If(Abs(Get(SystemPlatform)) = 2;"";":"); 



1; 



PatternCount(



Substitute(

Substitute(

Substitute(

Get(FilePath)

; Get(FileName); "")

; "file://"; "")

; "/"; Case(Abs(Get(SystemPlatform)) = 2; ""; ":"));



 If(Abs(Get(SystemPlatform)) = 2;"";":")) - PatternCount(FilePath; "../")



)

) & Substitute(Substitute(FilePath; "../"; ""); "/"; If(Abs(Get(SystemPlatform)) = 2;"";":"))

);





Left(FilePath;2) = "*/" and not IsEmpty(_NetworkServer);



Substitute(

Substitute(



FilePath

; "*"; "fmp5://" & _NetworkServer)

; " "; "+");



Substitute(



Case(



Left(FilePath; 1) = "!" and not IsEmpty(_RootPrefix);



_RootPrefix & Substitute(FilePath; "!/"; "");



FilePath

)



; "/"; 



Case(

Abs(Get(SystemPlatform)) = 2; ""; ":"

)



)



)

It no longer opens the files as it has done previously.



In FM 8.5 it returns the following:

tell application "Finder" to open file "file::Macintosh HD:My Folder:.fp7_Handouts:LowDye.pdf"

(note the "[color:red].fp7"inserted before the folder name).

I've been looking through these forums and I've managed to change the file path format into a number of variations and tried AppleScripts, Send Events and Open URL commands but can't get it to work. It looks to me that current solutions may involve first exporting the file, or else using variables which I haven't yet tackled.

If anyone is familiar with this file I'd really appreciate your help with getting my document management file working again. I have a copy of the file but as it is a subscription-only download it's probably not going to be appropriate to post it.

I'm also wondering if it isn't just better to use references to the files instead of storing them in the DB - they are mostly PDFs but a few Word files also.

Cheers, Murray

If the file is stored in the database, can't you use Export Field Contents with auto-open file selected? You could export to the Get (DesktopPath) or in FM9, use the new Get (TemporaryPath).

The calculation has "fmp5". I don't know that this would matter. You might want to give up on the big complex calculation and just build a simpler one for your purpose. It would be easier.