Check if a file exists

[DaCo]

I have these two columns: "products_model" and "products_image". The "products_image" takes the model number and adds ".jpg". Works fine, but it generates this for ALL records. I only want it to write the string for products for which I really have an image.

"products_image" is an calculation field: "covers/" & Products::MANUFACTURER & "/" & Products::PRODUCT & ".jpg"

[Fitch]

You've described a kind of chicken-and-egg problem. There's no FileMaker function for that. The path has to be defined first, and then you can use IsEmpty() to test it.

[DaCo]

True(1) if the specified field is empty, if the related field, related table, relationship, or file is missing, or if some other error occurs; otherwise returns False(0).

Okay, "file is missing" is what i need, but how do I lookup and read filenames on my drive?

I have spent several hours reading the reference now. Please note that I'm a novice. The best I can come up with is this:

IsEmpty ( filemac:/volumeName/directoryName/fileName )

- but unfornunately it does'nt work. Specyfing a calculation field with this string continues to tell me, that the field filemac: cannot be found.

[Vaughan]

The "file" that the IsEmpty() function refers to is a related FMP database file, not a file in a folder.

There are no native filemaker functions that read file names from a hard disk or network share drive. There are third-party plugins that can -- Trio File comes to mind.

If there aren't too many products, it might be easier to simply have a checkbox that indicates "image exists" and calculate (or not) the name based on this. Even better, make a container field and store the images in the FMP database itself.

[DaCo]

The "file" that the IsEmpty() function refers to is a related FMP database file, not a file in a folder.

There are no native filemaker functions that read file names from a hard disk or network share drive.

Aha, thanks.

There are third-party plugins that can -- Trio File comes to mind.

Aha, I'll take a look.

If there aren't too many products, it might be easier to simply have a checkbox that indicates "image exists" and calculate (or not) the name based on this.

27.000 and growing by avg. 200 pr. week.

Even better, make a container field and store the images in the FMP database itself.

Hmmm, I'll think about that.

[Aussie John]

Applescript can tell if a file exists and FM can call an applescript.

If this forum can't help - the apple support has a good forum to do this

[Aussie John]

Im not sure if this satisfies your need but...

To see if there is an image, show the image instead of making a text string.

Make a calculation "container" field and use "imagemac:/your path to images/"&Products::PRODUCT & ".jpg"

If there isn't an image the field will show an error message so make the text white and any error will not show.

[DaCo]

Hmmm, sounds good, but it won't work for me. Under all circumstances the new field tells me: "The file can not be found"

I have tried to state the absolute path (Macintosh HD/....) and also to create a folder "covers" in the Filemaker programe folder, but with no luck. I have checked the spelling several times and checked if the image really exists at the location.

[Vaughan]

I have reservations about what you're doing because even if you got it to work, if the files are moved or the hard disk renamed or anything like that, then it will all break. Also, the files are on your machine right now: what when the solution goes multi-user? Other people won't be able to see the files.

Look in to SuperContainer. Or, as I suggested earlier, put the images into the database. FM 12 would be great for this.

It would be helpful to know WHY you need to do this.

[Aussie John]

I have reservations about what you're doing because even if you got it to work, if the files are moved or the hard disk renamed or anything like that, then it will all break. Also, the files are on your machine right now: what when the solution goes multi-user? Other people won't be able to see the files.

Look in to SuperContainer. Or, as I suggested earlier, put the images into the database. FM 12 would be great for this.

It would be helpful to know WHY you need to do this.

You are right Vaughan it is tricky displaying pictures from a folder but it can work in a local environment. I have abandoned previous attempts as a remote solution is more complicated especially if windows gets involved.

[org]

I'd use the free BaseElements plugin. http://www.goya.com.au/baseelements/plugin (or scriptmaster)

function: BE_FileExists ( $path )

result: 1 or 0 (true or false)

you could test in a script and set fields based on that result

cheers

olly

[Vaughan]

I'd use the free BaseElements plugin. http://www.goya.com....elements/plugin (or scriptmaster)

function: BE_FileExists ( $path )

result: 1 or 0 (true or false)

you could test in a script and set fields based on that result

cheers

olly

No need for plug-ins, Aussie John's solution of a calculated container field with the calculation = the file path works perfectly -- just remember to make it unstored.

The problem is that the file paths are HARD CODED into the calculations, or into a text field.

[org]

The problem is that the file paths are HARD CODED into the calculations, or into a text field.

Yeah I use this workflow as get hundred of assets separately from associated metadata to those files.

As long the filenames are in the metadata its all good. ie.

- Chuck those files into shared folder (or onto CDN if so wish)

- Import metadata

- Check / fix any which don't exist.

- Display files container with cross plat calc. web-viewers etc

I'm looking at external containers in 12 & import folder as an option - pain point is the not being able to store those files away from the FM file on server.

Note: folder import seems to support more file types in 12 - but not all ie. word.doc , text.log files didn't import in quick test - however most movies, audio, pdfs, images did.

Cheers

Olly

[Fitch]

Attached is an example of what I had in mind, i.e., attempt to insert the picture into a "dummy" field, then set the "real" path depending on the result.

containerEmpty.zip

[DaCo]

It would be helpful to know WHY you need to do this.

I have 27,000 products, but only 22,000 images. I need to know which products are without pictures. From FMP I make a tab-separated file export and upload it to three OsCommerce webshops.

I have now created a container field and "embedded" the images. The field shows a tiny thumbnail. So far, so good, BUT ... the records can not be sorted on that field ... AND it only shows images for the specific folder. I'm working with around 200 record labels (classical music) and my images in a separate folder for each company. 27,000 files with crazy names in ONE folder ...? Is it possible to add a command like "/s" i.e. include subdirectories?

Thank you everyone. Nothing is final yet. I'm trying out all the good ideas given here.

[DaCo]

Attached is an example of what I had in mind, i.e., attempt to insert the picture into a "dummy" field, then set the "real" path depending on the result.

Gosh! Allow me a couple of hours to analyze that :sweat:

[Fitch]

I wouldn't recommend storing that many files in one folder (this is an issue with the OS, not FileMaker), but you don't need to.

To find all the records with empty containers, create a calculated field IsEmpty( your image field ) and search on that.

[DaCo]

Fitch, yes I could easyly do that IF my image field was a complete mirror for ALL my folders.

My base path is "imagemac:/Macintosh HD/Danacord/Webshops/4 Covers/

The folder "/4 Covers" has 2-300 sub folders - one for each record label. It would be nice to be able to add a parameter /include sub folders to this path.

My test string is: "imagemac:/Macintosh HD/Danacord/Webshops/4 Covers/Bridge Records/" & Procucts::Product & ".jpg". The result is ca. 250 icons related to products from Bridge Records and no more. i Can't imagine how to make an calculated image field for EACH subfolder. Too heavy, I guess.

[Aussie John]

you could list the label (or parse it from other info) and add it to your path string.

[DaCo]

Aussie, you are my sunshine

So easy, so obvious, so logical ...

My new string in the calculated container field: "imagemac:/Macintosh HD/Danacord/Webshops/4 Covers/" & LABEL & "/" & PRODUCT & ".jpg"

It works nice. Tank you very much and also thanks to the other participants. I'm constantly learning.

[DaCo]

The container field is hard to handle. It's not possible to sort the records, the field is not searchable, the field can not be exported and Excel can not open or import the database.

The purpose of creating the field is to make an overview of the missing images so I can take action and try to obtain them from our suppliers.

Is it possible (i've tried) to refer to that field somehow, e.g.: If CONTAINER = [What_to_put_here?] THEN "X" ELSE "0"?

[Vaughan]

Create another calculation field that is based on the container field, typically GetAsText( container field ). This will display the filename and path.

[DaCo]

Thanks, Vaughan. It works "better than nothing" (I can sort by this field), but since the newly created "show-tiny-thumbs-field" calculates for ALL records (if no image: error message), the new calculation field also generates the file path for ALL records. All records have in common the basic path imagemac:/Macintosh HD/Danacord/Webshops/4 Covers/. Therefore the sort will be done after the next character, which means the the varying folders, e.g. Walhall/WLCD 0272.jpg.

This "list" does'nt tell me if I have got the image for that product or not, unless I compare with the first field and ... ta-daaaa ... how do I do that? The dog is biting its own tail.

The ideal list or sort order would show me all missing files collected/isolated - ascending or descending after the 1: LABEL and 2: PRODUCT_NUMBER or just PRODUCT_NUMBER.

[DaCo]

A strange observation: As menthioned before, it's not possible to include the calculated image field in an export from FMP.

If I select Save/Send Records As instead of Export Records... all fields are exported to Excel. The calculated image field now shows exactly the same (full path + filename) as the newly created field in FMP.

I don't know the difference of these two variants of export method. Nice to know, but it does'nt help me right now.

[Vaughan]

This "list" does'nt tell me if I have got the image for that product or not, unless I compare with the first field and ... ta-daaaa ... how do I do that? The dog is biting its own tail.

The ideal list or sort order would show me all missing files collected/isolated - ascending or descending after the 1: LABEL and 2: PRODUCT_NUMBER or just PRODUCT_NUMBER.

Create another calculation field to do the "comparison".

[DaCo]

That would be a chicken-and-chicken compare.

The calculated image field shows the full path + filename. Not visible in FMP with any method I can think of, but that's what the field contains when exported to Excel (as explained above).

The GetAsText () returns exactly the same and in both cases that similar string is put out for EVERY record in the table.

So, Vaughan, what to compare? No differences will appear. For the record, I have tested it with If ( COVER = PATH ; "X" ; "Y" ), where COVER is the calculated image field and PATH is the GetAs.....

[Vaughan]

I am now very confused.

The original spec was to work out which records had images. The solution was a calculation field = not Isempty( image path ). There was a bit of discussion about generating the image path, but that's basically it.

Is that not enough?

[DaCo]

WE are confused, are'nt we? Well, here goes the detailed version. Hope this helps

My calculated container field: "imagemac:/Macintosh HD/Danacord/Webshops/4 Covers/" & LABEL & "/" & PRODUCT & ".jpg" shows an image when the file is present and an error if not ("The file cannot be found:"). All my troubles would be gone, if it was possible to sort on that column, but it's - unfortunately - not. The "real" contens is invisible in the view in FMP in all ways. The only way to inspect the content is to export the database to Excel by using "File > Save/Send Records As". This shows the full path + the file name as a test string.

The next field was created on your suggestion: GetAsText ( COVER ), where COVER is the first field, makes excatly the same output: full path + the file name as a test string.

Both calculates in ALL records, since the formula is present in all records. Summa summarum: The output in both calculated fields is completely identical and a third calculation to compare them or use Isempty( image path ) is therefore meaningless.

[Vaughan]

I cannot see a way in FMP 11 to natively determine whether the image from the path exists. Org suggested the Base Elements plugin, this will do it, but there is no way to do it natively.

Except for Fitch's suggestion (post 14) of importing the images into the database from the path then checking for those that did not import.

FMP 12 has some new container functions that might work but I haven't worked with them yet.

[DaCo]

I cannot see a way in FMP 11 to natively determine whether the image from the path exists.

The final conclusion, right? Well, thank you for trying to help.

Org suggested the Base Elements plugin, this will do it, but there is no way to do it natively.

Yeah, I tried it out, but could'nt figure out how to use it. Okay, I was a little impatient that day.

Except for Fitch's suggestion (post 14) of importing the images into the database from the path then checking for those that did not import.

Thank you everyone. Nothing is final yet. I'm trying out all the good ideas given here.

I must admit that I switched completely to the solution we have discussed recently, so all other experiments have been put to sleep.

I have now experimented with Fitch's suggestion and part of it works immediately, and if nothing else, it allows to sort on one of the columns so that all records that have photo can be sorted at the top or bottom.

[Vaughan]

Solutions get very, very difficult if part of the data is in FileMaker and part is somewhere else. (It's not just a FMP problem.) WHich is why I suggested that the database also be the repository for the images. That way it's easy to work out which products have images and which do not.

Try to get the plug-in working.

[DaCo]

Attached is an example of what I had in mind, i.e., attempt to insert the picture into a "dummy" field, then set the "real" path depending on the result.

As mentioned in my above reply to Vaughan, I have not been able to handle more than one series of tests at a time. Novice, you know

Now I have taken a closer look at your suggestion and it looks promising. I have "analyzed" your script. My chicken brain can roughly follow the principle, but after either a successful import or display of the dialog "No image ...", what happens then and where, if the picture is not available?

I do not understand why the last two fields should be there if not to write anything in them. I had perhaps expected, that "No image ..." was written, for example. in the "Image" or "Image result", but they remain blank.

Thus, only those items that have a image is being written anything in. It is very fine that can be sorted by that column, but since it's the ones with errors that interest me, I'd like to see that the attempt has been done without result, and with the opportunity to inspect whether it is just a misspelling in my data that I can fix.

[Fitch]

The "image" field is a global container that is used to do the testing, you might call it a "dummy" field. The reason it's blank is that I explicitly set it blank at the end of the script. The "image result" field isn't really being used for anything, I was experimenting with it.

This is what the script does:

1. Assembles the path. You could do this all in the script, or use a calculated field, which is what I think you're doing. That's fine, but the Insert Picture command can't use a calc field directly, so you still have to put it into a variable, which I creatively named "$path"

2. Inserts picture into dummy field

3. Returns a result. If the image was inserted successfully, the error will be zero.

4. Does something based on the result. What that "something" is, is up to you. What my script does, if the error is 0 (success), it sets the path into the "product_image display" field. If not, it sets that field blank and displays a dialog.

If you sort on the "product_image display" field, the blanks will go to the top of the list. Or you could do a Find to isolate them.

If you want to display some data in field instead of blank, that's fine. It's the line after the Else statement in the script. You could set the field to, e.g. $path & " IMAGE MISSING"

Your actual image container is calculated on the "product_image display" field.

Hope that helps.

[DaCo]

Thank you, Fitch, now it is implemented with a few refinements and do exactly what I need.

I currently have 27,000 items in the database, of which I have images to approx. 22.000. It will be something of a pastime, if I have to sit and click on each one, so I am looking for a way I can let the script run through all the records - at least the first time, but probably also frequent, as several different people create new products and either forget to add a picture or that the associated image first comes later. A weekly update of all records that would be nice.

[DaCo]

Nobody will come to say that I can not think for myself. Well, think and think ... I'm hard to google

I have now made ​​a new script that loops the first script, so the entire database is updated with the pictures that exist. I can run the script weekly or as needed - up to me.

Thank you everyone for your help and inspiration.

Oh, one more thing ...

Approx. 22,000 images are now "embedded" in my database, but the file size of the database has only increased by a few kilobytes. So: they are not really embedded, but linked, huh?