6 Replies Latest reply on Jan 19, 2015 9:14 AM by chris.schmitz

    Conditionally formatting multiple cells at once.


      Does anyone know how I can conditionally format several hundred fields either by using a script (preferable) or by copying the conditional formatting from one to all the others at the same time?  I am trying to build a gantt chart based on dates for work / leave rosters.

        • 1. Re: Conditionally formatting multiple cells at once.


          I think a pesonnal fonction is better, and you can find one or you can use "case"

          • 2. Re: Conditionally formatting multiple cells at once.

            kaiviti wrote:


            Does anyone know how I can conditionally format several hundred fields


            You may want to use one or more repeating fields for such a purpose, which will a) leave your schema uncluttered, and b) lets you treat each field's layout object as the single field it is.


            A more flexible approach would possibly be a Web Viewer and some JavaScript / JQuery plugin.


            kaiviti wrote:


            […] format several hundred fields either by using a script (preferable)


            You cannot format fields – conditionally or otherwise – with a script. What you can do is select any number of fields and apply your CF calculations simultaneously.

            • 3. Re: Conditionally formatting multiple cells at once.

              What does your layout that is showing the cells look like? Are you in table or list view looking at multiple rows(records) of data? Are you in Form view looking at a static set of cells(fields) all contained within one record?


              I'm assuming the former. There are numerous ways you could format multiple cells programmatically and cleanly. Here's how I thought through the option I'm suggesting.


              What are you trying to accomplish?


              What you want is to target a specific "cell", or in FIleMaker terms a specific field for a given record, to change the color of. Knowing that, we can target the cells with two pieces of information: a record identifier and a field identifier.


              The Record identifier is easy, you can use whatever primary key you've assigned to your table, i.e. the field containing the auto-entered serial value. In my example, the primary key for my table <code>formattingcells</code> is called <code>ID</code>.


              The field identifier is also somewhat easy, you can get the name of the field by using the function calculation <code>GetFieldName()</code>. Now that you have your two identifiers, let's identify stuff.


              How do I identify the cell?


              You're on the right track with conditional formatting. We can use a simple let statement to control the formatting:


              Let ( [

                  highlightList = ¶ & $$HighlightList & ¶ ;

                  fieldName = ¶ & GetFieldName(Self);

                  recordId = formattingcells::ID  & ¶;

                  seperator = "|"


              PatternCount ( highlightList ; fieldName & seperator & recordId ) <> False



              You could write this in a less verbose statement, but the longer I code the more I'm for spelling it all out so that when you come back a year from now it will be easier to read. An explination of what you see above:


              • highlightList:
                • This let variable will hold the list of values we build to tell the fields who should highlight and who shouldn't
                • I'm wrapping the list in carriage returns because when we'll be using a pattern count for determining if the cell should be highlighted or not. Pattern count will count partial values if we don't limit it, e.g. the count of the pattern "hi" will be two for the list "hi¶hi there". Wrapping the list in carriage returns and then wrapping our search value in carriage returns will mean that we're looking for a specific value followed by a row delimiter (and if FileMaker that happens to be a carriage return).
              • fieldName:
                • This will be our column identifier
                • We're prefixing it with a carriage return to denote the start of our search value
                • By using <code>Self</code>, we can apply this same calculation to any field on our layout, or any layout for that matter, as long as the script we use to build the list accounts for the field.
              • recordId:
                • This is our row identifier
                • We're suffixing the value with our carriage return which will end our value
              • seperator:
                • We're going to programmatically "glue" our values together and we'll do this by using the pipe character.
                • You could take the extra step of moving the separator and maybe the recordId values out to custom functions so that you're not actually writing them into the cells conditional formatting, but that's up to you
              • The pattern count calculation:
                • The basic idea is that we're looking through the list of row|column identifiers that we'll be building in our script and if the current cell's calc is in the list we'll apply our highlight


              You'll add this calc to all of the fields on your layout that you want to highlight.


              How do I build the list?


              This one is going to depend on your table and how you want to loop through it, but basically when you know you have a cell you want to highlight, you need to add it to your list with something like this in a <code>Set Variable</code> step:


              $$HighlightList = List ( GetFieldName(formattingcells::First Name) & "|" & formattingcells::ID ; $$HighlightList)


              Here you're building your value for the highlight list and adding it to anything existing in your highlight list. Using the <code>List()</code> function means it will be appended with a carriage return. If you move the separator out to a custom function you'd be able to include it as <code>seperator</code> here which would clean up the code a bit AND allow you to switch out separators as needed in the future without having to touch your code.


              At the end of your script, be sure to use a <code>Refresh Window</code> step so that the conditional formatting fires. Normally I would say use the <code>Refresh Object</code> step to minimize the impact of the redraw, but since you could potentially be refreshing a lot of cells, the window is ok.


              How do I remove items from the list?


              FileMaker has a function for denoting the things you want to keep from a list, but doesn't have a function that allows you to denote things you want to remove from the list. I would write this out by hand for you, but I'm already being a bit more long winded than I've meant to be, so what I would say is find a custom function like this one for removing values from a list.


              With this you can programmatically add and remove the cells to highlight to and from your list respectively.

              • 4. Re: Conditionally formatting multiple cells at once.

                Repeating fields aren't a bad choice for Gantt charts.


                But you can apply the same conditional formatting calculation to all fields pretty easily, by selecting them and adding the calc. Can you construct a calculation that will work for a large number of fields? The Self function is pretty useful here.

                • 5. Re: Conditionally formatting multiple cells at once.

                  You can add and/or remove textformating to/from the field contents though... But I don't think that is a good option in this case.

                  • 6. Re: Conditionally formatting multiple cells at once.

                    Yeah, I considered that idea as well.


                    There are some advantages to it. Applying the change to the data would mean the color change would stick even if you showed the data in a different layout that did not have the conditional formatting apply. It would also mean the user could copy and paste while retaining the format which is useful sometimes.


                    The downside is that you're actually modifying the data so if there's an error during the process you risk losing it, you're updating any timestamps for something that really shouldn't update it, and you may trigger an auto-enter calculation that relies on the field when it shouldn't actually be triggered.