1 2 Previous Next 23 Replies Latest reply on Jul 16, 2013 7:15 AM by goodheart

    Conditional Formatting



      Conditional Formatting


           Can someone tell me what the Conditional Formatting formula would be to do what is shown in the screenshot below? There are 18 boxes showing. If the number 15 is typed into the top left box, then box number 15 will be highlighted. If the number 1 is typed in, then the box number 1 will highlight, etc. 

           The top left box is a number field. And each of the 18 boxes are defined as number fields; ie., Box 1; Box 2; Box 3, etc.

           I know this should be really easy, sorry to be so dumb :)

           Thank you.



        • 1. Re: Conditional Formatting

               For the Box 15 field, this expression could be used to specify a fill color for the field:

               TableOccurrenceName::TopLeftFieldName = 15

               The only change for each of the other fields would be the number used.

          • 2. Re: Conditional Formatting
                 I think in this case it is possible and appropriate to use a repeating field with 18 repetitions.
                 The calculation for the conditional formatting will be simply:
                  Get ( CalculationRepetitionNumber ) = YourTable :: box
            • 3. Re: Conditional Formatting

                   I copied and pasted your formula, then changed the names to my field names and I get an error "The specified table cannot be found".

              • 4. Re: Conditional Formatting

                     which formula? and filemaker highlights which part of that formula?

                • 5. Re: Conditional Formatting
                  /files/6b26020fb2/screenshot2.jpg 805x182
                  • 6. Re: Conditional Formatting

                         If Main::TopBox is the name of the field where you can enter the numbers from 1 to 18, the calculation for the conditional formatting of a repeating field named Boxes is:

                    Get ( CalculationRepetitionNumber ) = Main::TopBox

                    • 7. Re: Conditional Formatting

                           Ray - I'm trying your approach but I don't think it's going to work for me. The top left box is a defined field that is used in other places in the file so it can't be a repeating field. When I first saw your answer, I thought that was definitely the way to go. But, now I think the other approach, the one Phil provided is more applicable for my situation. Problem, though, is, when I used his calc  

                           TableOccurrenceName::TopLeftFieldName = 15


                           I received the same table error message.

                      • 8. Re: Conditional Formatting

                             Okay. I have this working great, now I just need one more twist to occur. The user may type in a 1 and it makes the first box highlight - but they may also type in a 1A or a 1B - I need it to highlight box 1 if the user adds an A or a B to the number they enter.

                             I was able to make it work when I did this   

                             TableOccurrenceName & TopLeftFieldName = 1    instead of   TableOccurrenceName::TopLeftFieldName = 1

                             So, how do I add an A or a B to the 1 that makes the first box highlight?

                             Thank you!!!!

                        • 9. Re: Conditional Formatting

                               Filter ( YourTable::YourField ; 1234567890 ) = 1


                               GetAsNumber (  YourTable::YourField ) = 1


                               but I'm sure that it is possible to use a repeating field instead of your 18 number fields.


                          TableOccurrenceName & TopLeftFieldName = 1 

                          is definitely wrong

                          • 10. Re: Conditional Formatting

                                 But it works. When I used the :: between the fields, it kept telling me Specified table not found. When I changed to the & it worked. I have tested all 18 boxes and it works exactly like I want it to - with the exception of adding a letter - then it doesn't work. How can I make it highlight if I add an A or B - like 1A and 1B should still highlight the first box the same way it does when it is just a 1 in the box.


                            • 11. Re: Conditional Formatting

                                   How bout something like this...If this works I'm sure someone can clean it up.  https://dl.dropboxusercontent.com/u/71328624/TestingDB.fmp12

                              • 12. Re: Conditional Formatting

                                     Hey Steve - You have it working pretty much the way I want it to. I was able to get mine working really nicely, with the following exception. I need to be able to have a box highlight even if there is a letter added. For instance, 1A, 1B or 1C still needs to highlight the first box. If I type in 1A, 1B or 1C now, the conditional formatting doesn't work.

                                • 13. Re: Conditional Formatting

                                       Do you need the first box to show 1A, or can it just show 1, even if they try to type 1A?

                                  • 14. Re: Conditional Formatting

                                         Actually, the difference in what you did and what I need, is that all the numbers are typed in. There could be a 1, 1A, 1B, or 1C added by the user at any given time. If the A's and B's are used, then there would already be a 1. So, in this instance, there would be 3 records and those records would contain a 1, which would highlight the first box. Then, the next record would be 1A which would also highlight the first box, then another record that would contain 1B and that would also need to highlight the first box.

                                         Each record in my layout contains the row of boxes labeled 1-18.

                                         Each record has the box that when a number is typed in highlights the corresponding box in the row. I just need box one to highlight whether the number 1 is typed in, or 1A, 1B or 1C.

                                    1 2 Previous Next