9 Replies Latest reply on Aug 22, 2013 3:14 PM by philmodjunk

    Conditional formatting needs to prompt for a value

    mdallie

      Title

      Conditional formatting needs to prompt for a value

      Post

           I've got a situation where I am printing a report and I have a date field and a text field with each record.  I run this report and send it to a client every two weeks.

           I need to have the text field highlighted in yellow if the date field is greater (newer) than the last time I ran the report.   I haven't done it (new at this conditional formatting) but I assume I can figure out the concept of a formula that says, for example, if datefield > 8/7/2013 then make background of the text field yellow.  What I'd like to do, though, is have the date be external to the conditional formatting formula so I don't have to go in and change the formula every time I run the report.  I would like it to either be prompted for the date, or perhaps I can put it in some external location.

           Any help would be sincerely appreciated.  I've not used conditional formatting at all before and this is a pretty critical requirement.

        • 1. Re: Conditional formatting needs to prompt for a value
          philmodjunk

               Whether you host your file over a network or not changes how you can do this automatically.

               If it is NOT hosted over a network, you can define a global date field that stores the date you last produced your report. If you use a script to find, sort and perhaps preview or print the records for your report, that script can also update the global date field, which you can then use in your conditional format expression. You could also edit this field directly if needed--say to reset the date if you run the script and then need to run it again without the formatting being changed.

               If it's hosted over the network, changes to the value of a global field will not be retained and thus you have to use other methods to store the date. It can be stored in a non-global field of a separate table and then a start up script can load this value in a global field or variable each time you open the file.

          • 2. Re: Conditional formatting needs to prompt for a value
            mdallie

                 I'm not running over a network.  Conceptually this sounds like it would work.  I just need to get a little further along in my learning curve to understand how to define a global date field, use a script, etc.  LOL

                 I've got a lot to learn.  If you've got anything you can point me to, that would be much appreciated and save me time.  But this conceptual answer is a big help and tells me what to search for.  Thank you.

            • 3. Re: Conditional formatting needs to prompt for a value
              philmodjunk

                   When you open Manage | Database | Fields, you can double click a field listed in that window to open a dialog where you can specify global storage for a field.

                   THere are quite a few training resources available in a number of different media that you can investigate for learning more about FileMaker. Which is best for you is not a choice that I can make for you as this will depend on your current skills and prior experience with FileMaker or similar products.

              • 4. Re: Conditional formatting needs to prompt for a value
                mdallie

                     Very helpful Phil.  Got it!!

                     I figured out the formula thing and it is working perfectly!

                     I appreciate the thoughts on training classes.  I've been in IT for about 40 years (just left it, actually).  I did my first ten years as a programmer and then management the rest of the time.  My big problem is that I develop something in Filemaker very infrequently, and usually it isn't too complex, so between a book I bought (The Missing Manual) and Google, I can usually get through.  Once in a while, though, I get stumped like this.

                     I have given much thought to going through some thorough training as I'm really impressed with what the product can do.  But the way it works is that by the time I go to use it again, I'll have forgotten most of what I learned.

                     I can't thank you enough, Phil.  Much appreciated!!!!!

                     Mike

                • 5. Re: Conditional formatting needs to prompt for a value
                  philmodjunk

                       SInce you have a programmer background, the FIleMaker Training Series (Soliant Consulting) may be a useful resource manual for you. It's the recommended resource for those training to take the certification test on FileMaker.

                  • 6. Re: Conditional formatting needs to prompt for a value
                    mdallie

                         Hmmmm...... just looked it up.  Not very expensive at all.   Thanks a lot, Phil.  I think I'll get this.

                         In my new job, I think I'm likely to want these job aids I build to be a little more automated with scripting, etc.  Maybe this would give me what I need.

                         My lucky day to have had  you stumble across my post.  Thanks again.

                          

                         Mike

                    • 7. Re: Conditional formatting needs to prompt for a value
                      philmodjunk

                           Then I also recommend spending the $$ to get FileMaker Pro Advanced. The script debugger alone will save you many hours when dealing with complex scripts and the ability to add custom menus and custom functions to your database solutions also greatly enhance your options for creating effective designs for your database.

                      • 8. Re: Conditional formatting needs to prompt for a value
                        mdallie

                             Hmmmm....... I didn't realize that custom menus required Advanced.  I haven't looked at it in a while, but thought the big advantage was being able to generate runtime applications or server-based apps.

                             I've got to think about this.  I'm in M&A work and my company (of 11 people) uses a pretty old CRM system.  It isn't up to the task of certain reporting I need to give my client so my answer was to put together a database because it is a heck of a lot easier to update and generate a proper report.  I'm working hard to not become the IT deparment for my company because I get paid for closing deals, and nothing else.  However, when my boss saw what I did (which is pretty standard stuff for the Fortune 500 companies I used to work for), he asked about it as I think he wants to see the company improve as it grows.

                             I appreciate the perspective on Advanced.  I'll keep it in mind as I start putting together my scripts.

                             Thanks again!

                             Mike

                             P.S.  I already ordered, and am about to download, the training series you recommended.  

                        • 9. Re: Conditional formatting needs to prompt for a value
                          philmodjunk

                               FileMaker Advanced can produce run times, but I've found that to be far less valuable than the other features. "Server based apps"? FileMaker Advanced has nothing to do with any such thing.