Path to an inserted file?

[Jhestler]

Hi All,

I'd like to store the path to a file or file reference that has been inserted into a field. I'd like to further be able to export that file path, along with other fields in the same or related table, into an Excel database, such that a person on the same network can click that file reference in the Excel cell and it will open that file.

Is this possible? With or without the Troi plug-in?

Sounds like a tall order; I remember that the Troi file plug-in enabled one to at least store the path to an inserted file in a field.

Thx!

[comment]

If you mean a file that has been inserted into a container field as reference only, you can get the path by GetAsText ( Containerfield ). This returns more than the path (depending on the content type), so you'll need to extract the desired part from the result.

[Jhestler]

Very cool! I imagine I can use additional calculations to filter out the unwanted data in the string, thanks!

I emailed Troi to see if their plug-in enables the exported field data to land in the resulting Excel file as a ready-to-click hyperlink. I can't imagine that it does; that sounds like something that requires either applescripting or visual basic. I'll post the developments...

[Jhestler]

I've just about arrived at a solution that enables the above functionality, but there's one problem...

I'm able to obtain the filepath of a file inserted into a container field by creating a calc field. The calc field = container field, as text. No problem there...

As Comment pointed out above, this calc field returns some extraneous data. I'm able to filter that data easily enough using various implementations of both the "Middle" and Position" functions. No problem there...

Having done that, I next needed only create another calc field that adds the appropriate Excel syntax to the filepath, so that when that calc field's contents are exported to an Excel file, the data will land in the Excel cell as a fully-functional hyperlink to the file. That's done, no problem there...

The problem is that Filemaker--in retrieving the filepath of the file that's been inserted into the container field--uses the "/" symbol to indicate the folder depth, while Excel (or perhaps just my company's network?) uses "" to indicate folder depth. Hilarious.

For this reason, I'm dead in the water at this point.

I can't practically use the Left, Right, Middle, Position, or Replace functions to replace the forward-slashes with back-slashes because the folder depth and the name length of the folders and files will vary according to the file reference inserted into the container field.

Any suggestions?

J

[Jhestler]

Curious!

The forward slashes apparently aren't the problem; through trial and error, I've confirmed that Excel recognizes both forward and back slashes. When I copy the cell contents that Filemaker exported into the cell, and paste such content into ANOTHER cell, the hyperlink activates!

So there's another reason why the data doesn't land in Excel as a hyperlink. I don't understand it, as the cell contents as exported by Filemaker and the cell contents as manually typed into the cell are identical.

Anyone know what's missing?

Thx!

J

[comment]

I am not sure it's possible to export the contents of Filemaker field as Excel formula.

[Jhestler]

I am not sure it's possible to export the contents of Filemaker field as Excel formula.

Here's the Excel syntax for hyperlinks:

=HYPERLINK("[http://example.microsoft.com/report/budget report.xls]Annual!F10", D1)

And here's the Filemaker calc I used to strip away the extraneous data from Filemaker's "Get As Text" filepath calc field (we'll entitle this calc field "Filtered File Path")

Middle (File Container as Text ; PositionOfBackSlashSymbol + 1 ; 1000)

And lastly, here's the Filemaker calc I used to conform the above calc to Excel's hyperlink syntax (we'll entitle this last calc field "Final File Path")

"=HYPERLINK(" & Quote (Filtered File Path) & "," & Quote ("Click for report") & ")"

It shoves the filepath into the Excel cell upon export alright, but it ends up in the cell exactly as entered. Funnily enough, if I copy the contents and enter it into the formula of another cell, the hyperlink activates.

It's hilarious, I can see it, it's like looking at my friend waving at me... from the other side of the Grand Canyon, lol...

FMPro works with XML, ODBC and OJDC, so it's hard for me to believe that it can't do this. Could it be?

[comment]

I notice the same thing when I export "=2+3" - so the problem is not with your syntax, but with formulae in general. It seems that Filemaker somehow tags the cell permanently as text - perhaps as some kind of protection.

I believe it still can work, if you export as comma/tab separated, then import it into Excel.

[Jhestler]

I notice the same thing when I export "=2+3" - so the problem is not with your syntax, but with formulae in general. It seems that Filemaker somehow tags the cell permanently as text - perhaps as some kind of protection.

I believe it still can work, if you export as comma/tab separated, then import it into Excel.

I spoke with Pre-Sales at Filemaker this morning, and confirmed that the marriage between FM and Excel is such that FM can't export data into Excel as a formula. So this is a confirmed no-can-do at this point : (

Rather than export as tab-delimited, I instead have the filepath exported into Excel as text, and then created a macro to converted the data in that column into hyperlinks. Then created a button in Excel and assigned the macro to it. That reduces a conversion step.

It works but imho, FM and Excel should get their heads together on this one; would be really handy to be able to export a directory of files with ready-to-click hyperlinks...

Thanks again for sharing your outstanding knowledge, Comment : )

J