2 Replies Latest reply on Dec 24, 2012 8:37 AM by MichaelRoche

    Modifying Record Based on Contents of Another Record

    MichaelRoche

      Title

      Modifying Record Based on Contents of Another Record

      Post

           Hi everyone,

           I've been browsing these forums for a while, but finally have a question that I could use an answer to. I have a field called "Type" that uses a value list with around 6 different values. Then I have another field that I want to have a color set based on the contents of the first field. For example, see the chart below:

                                                                                                                                                                                                                                                                                               
                          When Field X is:                     Field Y Should Be:
                          ICM                     Orange
                          SPK                     Green
                          VID                     Blue
                          MIC                     Red

           The second portion of this is a little more complicated. I also want the background color of Field Y to change as the contents change. So when Field Y said "Orange", the background color would also change to orange. I think the easiest way to do that would be to have another layout that serves as a user interface to set these choices. So in this setup window, you could specify the options available in Field X, the resulting value that would appear in Field Y, and choose from a color picker the color that displays behind FIeld Y.

           I know that's a lot, but I hope someone can help me out. Also, I'm running Filemaker Pro 12 Advanced. Thanks so much!!

        • 1. Re: Modifying Record Based on Contents of Another Record
          philmodjunk

               Field Y Could be defined as a calculation field, but using a different TABLE (not just another layout) as you describe adds a great deal of flexibility to your system. The back ground colors could also be achieved with a conditional format using the same casefunction approach, but it also has the same limitations.

               So using that second table, set up this relationship:

               FieldXTable>--------FieldYTable

               FieldXTable::FieldX = FieldYTable::FieldX

               See the first post of: Common Forum Relationship and Field Notations Explained if this notation is new to you.

               In the FieldYTable, define three fields: FieldX, FieldY, ColorFIeld--> make this last field a field of type container.

               On your Layout based on the FieldYTable, create records for each value of FIeld X, and enter the text that you have shown in your first post here. Then, Enter layout mode and use the rectangle tool to create an orange rectangle on your layout. Copy it to the clipboard. Return to browse mode and paste the copied rectangle into the Color field. Repeat for each of the other colors.

               Now go to your layout based on FIeldXTable. Format FieldXTable::FieldX with a value list of values from FieldYTable::FieldX. Put the Field Y and ColorField fields from the FieldYTable on this layout. In data formatting on the inspector, select the "Reduce or enlarge" option and clear the "maintain proportions" check box so that the pasted rectangle resizes to fully fill the container field.

               Note: You don't have to use FileMaker in layout mode to create the color rectangles. Any simple graphics program, such as Windows Paint can be used to create these rectangles so that you can copy and paste them into the colorFIeld containers.

          • 2. Re: Modifying Record Based on Contents of Another Record
            MichaelRoche

                 Perfect, thank you so much!