12 Replies Latest reply on Aug 7, 2017 2:04 PM by keywords

    Conditional Formatting Based on File Extension

    MauriceG

      Hi,

      I want to apply conditional formatting to a field when the file name of what's in the field has an extension that ends with something other than .jpg  I found some clues on the forum but I'm still going in circles. Could anyone put me on the right track?

      Thanks.

        • 1. Re: Conditional Formatting Based on File Extension
          keywords

          The following CF calc will do it—

          PatternCount ( Self ; ".jpg" ) = 0

          1 of 1 people found this helpful
          • 2. Re: Conditional Formatting Based on File Extension
            MauriceG

            It's working fine. Thanks very much.

            • 3. Re: Conditional Formatting Based on File Extension
              TomHays

              The following calculation will check specifically for "ends with something other than .jpg".

               

              Right(Self; 4) ≠ ".jpg"

               

              This would come in handy if you encounter odd file names like "photo.jpg.doc" that might result from people converting formats.

               

              -Tom

              • 4. Re: Conditional Formatting Based on File Extension
                beverly

                Sometimes the extension is '.jpeg' (not a perfect world, eh?)

                Beverly

                • 5. Re: Conditional Formatting Based on File Extension
                  MauriceG

                  Thank you Tom and Beverley,

                   

                  My FMP solution stores travel pictures and videos. When a video, as opposed to a picture, is in the container field, I want the button used to launch that video to turn green. For the time being, all pictures in the container field have a .jpg extension. Over time, I will add thousands more files (pictures and videos) and I'm not sure all pictures files have that same extension. So with Tom's suggestion, the launch button would turn green if the extension of the picture file name is not .jpg. I guess I can get around this difficulty by checking all file extensions before uploading the files to my FMP solution and modify the conditional formatting calculation to add to it any extension that is not .jpg. A little cumbersome....

                  • 6. Re: Conditional Formatting Based on File Extension
                    TomHays

                    To prepare for additional JPEG file extensions and other picture formats...

                     

                    Case(

                    Right(Self; 4) = ".jpg"; 0;

                    Right(Self; 5) = ".jpeg"; 0;

                    Right(Self; 4) = ".gif"; 0; // GIF

                    Right(Self; 4) = ".png"; 0;

                    Right(Self; 5) = ".tiff"; 0;

                    Right(Self; 4) = ".bmp"; 0;

                    Right(Self; 4) = ".jp2"; 0; // JPEG2000

                    // Add other known picture extensions

                     

                    1  // Default value for no match against the above extensions

                    )

                     

                     

                    If you wanted to work from a list of extensions in a text string could do something like this...

                     

                    IsEmpty( FilterValues(Substitute(RightValues(Substitute(Self; "."; "¶."); 1); "¶"; "");

                    ".jpg¶.jpeg¶.gif¶.png¶.tiff¶.bmp¶.jp2"

                    ) )

                     

                    For this calculation you can use a text field as the source of the list of extensions.  Thus you could update the list by just typing another line into a field instead of having to modify the database calculation.  Similarly you could build the list using List(RelatedTable::ImageExtension) and have each extension as a separate record in a table.

                     

                    You might be tempted to simplify this and remove the "." in the calculation.  Keeping the "." was done to ensure that this calc behaves properly when the file extension is absent.

                     

                    In both calculations, change "Self" to your field name if your context needs it.

                     

                    Note that in both calculations, the matching is done without regard to upper/lowercase.  The filename "image.jpg" will be treated the same as "image.JPG".

                     

                    -Tom

                     

                     

                    Edit:

                     

                    Additional info on how the calculations were formulated.

                    The Case() calc is pretty self-explanatory.

                     

                    The FilterValues method is a little more involved.  It pulls off the filename extension and compares it to the list of extensions.

                     

                    Substitute(Self; "."; "¶.") // turns "filename.jpg" into "filename¶.jpg" which is a FileMaker list.

                    Result: "filename¶.jpg"

                     

                    RightValues( ...; 1) // pulls out the last item in the list but has the side effect of adding a ¶ giving ".jpg¶"

                    Result: ".jpg¶"

                     

                    Substitute(...; "¶"; "") // removes the trailing ¶

                    Result: ".jpg"

                     

                    FilterValues(...; "".jpg¶.jpeg¶.gif¶.png¶.tiff¶.bmp¶.jp2") // result is empty unless it matches an item in the list.

                    Result ".jpg"

                     

                    IsEmpty(...) // Checks to see if the result is empty.

                    1 of 1 people found this helpful
                    • 7. Re: Conditional Formatting Based on File Extension
                      MauriceG

                      Thanks again Tom,

                       

                      The FilterValues method works well. At first, it looked like Greek to me, but thanks to your further explanations, I understand it well. I keep learning

                       

                      The Case approach, however, for some reason, does not work.

                      • 8. Re: Conditional Formatting Based on File Extension
                        MauriceG

                        I've resolved the issue with the Case approach by changing Right(Self; 4) = ".jpg"; 0; to

                        Right(Self; 4) ".jpg"; 1;

                         

                        Thanks so much for your help.

                        • 9. Re: Conditional Formatting Based on File Extension
                          TomHays

                          Something else must be going wrong with your Case() function.

                           

                          Using

                          Right(Self; 4) ".jpg"; 1;

                          as the first condition will defeat the purpose of using the Case() and a bunch of tests instead of just

                          Right(Self; 4) ≠ ".jpg"

                          directly without any Case().  The subsequent tests will only be performed if the extension is ".jpg" so none of the other image file extensions will be handled correctly.

                           

                          -Tom

                          • 10. Re: Conditional Formatting Based on File Extension
                            MauriceG

                            Yes, you are entirely right. I realized that after my previous post. I'll keep looking at the Case function to try and find out what else is wrong with it.

                            • 11. Re: Conditional Formatting Based on File Extension
                              TomHays

                              Feel free to copy/paste the function here so that we can help spot the error.

                              • 12. Re: Conditional Formatting Based on File Extension
                                keywords

                                A couple of additional points:

                                1.     Depending on how many variations are involved, it can sometimes be more efficient to set your CF calc up in converse form—i.e. specify it as anything other than movie file types. (If you had, say, four possible movie types but 12 picture or other file types it would be easier to just specify the four.)

                                2.     Whichever way you go, you might have to also account for upper case file extensions—i.e. .JPG as well as .jpg, etc. Not sure about that, but I'd certainly test it to see.

                                1 of 1 people found this helpful