FM - Conditional Formatting Based on Existence of a File?

rvfowler2
rvfowler2 used Ask the Experts™
on
Attached is my script for opening various documents.  Note that if the doc does not exist, the user will get a dialog box informing them of this.  However, now my boss would like the button to be invisible if the file does not exist.  I have done conditional formatting for things that exist within FM, but can you do conditional formatting based on the existence of a file?  Thanks.
-openFile.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Will LovingPresident, Dedication Technologies, Inc.

Commented:
The only way I know of to check in real time for the existence of an external file is by using the Troi File Plugin (or a plugin with equivalent functions) than includes a File "Exists" function. In the case of the Troi File Plugin, the calculation looks like this using a previous defined variable with the file path:

TrFile_Exists( "" ; $FilePath )

The function returns a 1 if the file exists. I've used this for condition formatting to indicate if a listed file - in my case a PDF of an invoice - already exists.

Author

Commented:
Downloaded the file and reading the user's guide.

Author

Commented:
Read part of the user guide, but unsure if I need to install the server-side plugin as well.  Seems the client side would only apply to my PC and I want this to work for anyone, no matter which PC they have.  Given what I want to do above, what would be necessary?
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Author

Commented:
Can I just put your formuala below in the conditional formatting formula, or do you mean that I create a custom function?  My formula would look like this:

TrFile_Exists( "" ; $$setupPath)
Will LovingPresident, Dedication Technologies, Inc.

Commented:
The server-side plugin only needs to be installed if you are running scheduled server scripts that require it. It does need to be in the Extensions folder for every user who might need it. If you have a small number of users and relatively static set of computers, this can be accomplished quite quickly manually. With a bit more work, you can use FileMaker's Auto-Update feature, which will automatically 'push' the plugin from the server to any user who does not have it.

This FileMaker Guide explains how to setup FileMaker's Auto-Update feature so that plugins are automatically downloaded and/or updated (when out of date) when the user logs into the server and opens a database file requiring the plugin:

http://www.filemaker.com/downloads/pdf/fms10_autoupdate_en.pdf

Author

Commented:
OK, did you see the next question about whether I need to make a custom function?
Will LovingPresident, Dedication Technologies, Inc.

Commented:
You just use the formula not a custom function, but you will need to specify the path and you might need to specify it using a calculation field rather than a global variable if you have multiple files and need to display buttons - on not - for each of them in, say, a portal or list view.

Author

Commented:
Haven't used this before, so made a guess as to how it would work with the following (was unsure how you set it up so it returns the "1" you talked about).  Two problems:

1. It said I had an unregistered version of Troi.
2. My calc didn't seem to work.
-openfile2.JPG
Will LovingPresident, Dedication Technologies, Inc.

Commented:
1. If you have purchased the plugin and set it up to know that it is registered, then it will say "unregistered"

2. Plugins produce a result but it's not a FileMaker error, so you can't use Set Error Capture to trap for it. In theory it's a boolean calculation which should produce a 1 or 0, but if you want to be explicit about it, you could use:

If[ TrFile_Exists( "" ; $setupPath ) = 1 ]
    Open URL [No dialog; $setupPath ]

3. I think the primary issue is that your file path is wrong. The Troi file plug does not require a prefix such as "file://" that FileMaker expects when dealing with file paths. Check the documentation for the plugin to see what the file path should look like.

If it's still not working, my guess is that the problem is with your path. Since you are using FM Advanced, you can bring up the "Data Viewer" (under the Tools menu), go to the "Watch" tab, and enter:

TrFile_Exists( "" ; <theFullPathcalculation> )

click Evaluate and see what the result is. YOu can also do the same thing by creating a temporary calculation field. What you are looking for is a calculation and file path that produces a one (assuming that the file is actually there). It's easy to make mistakes when constructing file paths, so it's good to have a way to test it such as the Data View Watch list or a calc field.

Author

Commented:
I did not purchase the plugin yet; it never asked me to.  Maybe that's my issue.  Also, not sure I undersstand #1, since if you set it up to know its registered, why would it say the opposite, "unregistered"?
Will LovingPresident, Dedication Technologies, Inc.

Commented:
Sorry, typo in #1, should have said "registered".

I believe that when the plugin is unregistered, it gives you a delay dialog the first time you call a function (via a script or whatever) that uses the plugin. It's fully functional and you should be able to develop and test functions without registering. (I've had a developer license for 15 years for this plugin so I'm trying to remember how it works when it's not registered.)

Also, Troi File Plugin version 5.x does support FileMaker File Reference style paths, so the path may or may not be your issue but that's where most problems occur in my experience. If you put the calculation with fully spelled out path into the Data Viewer Watch list or into a calculation field and tweak it until you get a 1 when the file is present and null or 0 when the file is not, then that should clear up any issue around determining the correct file path syntax.

Author

Commented:
First, the Troi user guide says that it now recognizes FM's "file:" notations.  Second, I tried the following calc and it didn't work.  However, when I changed the equation to equal 0 it did work.  Maybe it worked because an unrecognized file path is equal to 0?

Set variable
If [Trfile-Exists (""; $setupPath ) = 1]
Open URL [ No dialog; $setupPath]
End If

Author

Commented:
Also, nothing showed in the Watch when I ran this script.

Author

Commented:
Sorry, just got it to work by taking out the file:\\.  However, how do I work this into conditional formatting.  Seems that you would have to set a variable to intialize OnRecordLoad, so that every time I choose a different record, the file path is reset.  Otherwise, how would the conditional formatting recognize the correct setup path?
Will LovingPresident, Dedication Technologies, Inc.

Commented:
The documentation says:

The returned result is a boolean: The function returns 1 if the file or folder exists. If it does not exist or if an error occurs it will return 0.

Not sure why it would work just the opposite.

FileMaker "If" statements are boolean, meaning "non-zero values are true, zero and empty values are false." So, you should not have to put the " = 1" part in at all, just

If [Trfile-Exists (""; $setupPath ) ]

but I sometime do it when trouble shooting to explicitly make the result 1.
President, Dedication Technologies, Inc.
Commented:
Did you add a calculation to the Watch list? I wasn't actually saying to observe the Watch list when running the script, that's actually what the "Current" list is for. Rather, I use the "Watch" list to test a calculation because I can just keep clicking Evaluate until I get it right, rather than having to run a script or open and close the Manage Database field definitions. It just a quick way to test and trouble shoot a calculation.

I don't know how your database is structured, but conditional formatting is generally based on a knowable value such as a field value or a calculation based on field values. If you have a record (portal or otherwise) for each document and want to have a button conditionally appear or not if the path is valid, then you can apply the conditional formatting using a path from field values in that record, either via a field that actually has the path name in it or using a calculation that determines what the path should be.

Author

Commented:
First, not sure how the Watch list works.  Looked it up in Help and it had two lines, but I'm sure I can figure it out.

Second, sorry, a bit dense, I just needed to use the file path instead of the variable.  The following worked fine on the title.  The only disappointment was that if you make a button out of a graphic, the graphic does not have the option for conditional formatting.  Following is my conditional format that worked to hide the title text above the button if the file did not exist.  Thanks, Will.

TrFile_Exists ( "" ; "S:/Website/Lee/" & PropertyInfo::PropCode & PropertyInfo::EntityNumber & PropertyInfo::RaishCode & "/plan/" & PropertyInfo::PropCode & PropertyInfo::EntityNumber & PropertyInfo::RaishCode & "_frontage.pdf") = 0
Will LovingPresident, Dedication Technologies, Inc.
Commented:
The way around this is to create a calculated container field. I have a separate table in my files called "Resource" which just contains graphics that I reuse repeatedly. Things such as Add and Delete buttons taken from some of the open source icon libraries like Tango Desktop - http://tango.freedesktop.org/Tango_Icon_Library - but also specific buttons like you "Open Doc" button. I then create a calculation with Result = Container, that says, "If the file exists, show the button from the Resource table, otherwise nothing..."

It's not as neat as doing it with Conditional Formatting, but formatting is formatting whereas this is making the button appear or not depending on a condition set in the calculation. I do something similar for a "Refund" button such that the button only appears if a payment exists to refund...

Author

Commented:
If the button is in a separate table, are you showing it via a tiny one-field portal?  If so, quite the workaround!  I'll try it.
Will LovingPresident, Dedication Technologies, Inc.
Commented:
No, I just create a cartesian relationship between each table in the database and the Resource table, so I can reference it from anywhere, e.g. Contacts_Resource, Payments_Resource, Invoices_Resource, etc. (A cartesian relationship is the "x" operator in the relationships dialog in which every record is related to every other record. The Resource table in fact has only one record in it.)

Author

Commented:
Thanks for introducing me to the Troi plugin.  It not only solved this conditional formatting problem, but looks very powerful on first glance.

P.S. - The plugin will only say "unregistered" the very first time you use it.  After that it doesn't come up.  Unsure how long the trial period is.
Will LovingPresident, Dedication Technologies, Inc.

Commented:
I think that if you quit FM and then reopen, you will get the Unregistered dialog again.

Some of the basic file functions are available in other plugins - plugin developers tend to through them in as a freebie - but nothing else has the full range of functionality that the Troi one does. I also make extensive use of the Troi Dialog Plugin, which among other things, allows you to:
Put popup menus and checkboxes in a dialog
Give you a fourth button rather than FileMaker's three
Let you present the user with a scrolling list of options including the ability to select non-consecutive items in a list
Give you an input dialog with up to nine input fields rather than FileMaker's 3 (plus they can be text entry, checkbox or popup menu
Give you a large text area to enter into rather than just one line

And lots, lots more....of course you can do many of these things using layouts in FM, and sometimes I prefer that method, but from an appearance and functionality standpoint, Troi Dialog is great. http://www.troi.com/software/dialogplugin.html

Author

Commented:
Went and downloaded it to.  However, noticed in the pricing that the dialog plugin, for example, costs $59.00 per user.  We have 25 users here.  Even at the discounted 25 multilicense fee of $449, I don't think my boss would go for it.  I would pay that much for all there plugins, but not just for one.

As a contrast, we purchased SmartSerialMail by JAM software, which only costs us $70 per year and this manages all our bulk emailing, including bounced mail, cancellations, subscriptions, as well as sends email out in threads and in delayed time.
Will LovingPresident, Dedication Technologies, Inc.

Commented:
Some plugins are actually much more expensive than that. When you get up around those numbers, then a developer license is about the same cost. You can check around and you may find another plugin, free or cheaper, that provides an Exists function. I think there is a way to do it on Matt Petrowsky's site that does it completely within FM but involves some funky programming.

Author

Commented:
So, a Developer's license allows unlimited users?  If you code for someone, do you add Troi Plugin calcs in and if so, do your customers have to buy the Troi licenses?
Will LovingPresident, Dedication Technologies, Inc.

Commented:
A developer license works like this:

You hold the license and have a developer registration key. You setup a script that registers the plugin using that license key on startup of your file using the TrFile_Register function. After that, the Plugin considers itself registered - See Preferences > Plugins - and executes functions without the delay dialog.

The main consideration is that the license obligates you to lock, obscure or otherwise make unavailable the registration key you are using so people can't go in and just grab it for their own uses. There are different ways to do this, the simplest one being to have a privilege set for normal users that does not allow script editing access.

Some plugin developer license have a limit for the number of end users/per site (often 10) that you can deploy to, others do not. I'm pretty sure that the Troi plugins have no such limitation.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial