8 Replies Latest reply on Jun 22, 2012 3:31 AM by FrankBell

    change field color and fill based on other fields?

    FrankBell

      Title

      change field color and fill based on other fields?

      Post

      What’s the best way to have a field in a list format change text and fill color depending on the contents of other fields in that record? I set up a text field, global storage, and a script which goes to the list layout with “if/then” conditions to describe the contents… but that doesn’t seem to work. (I assume that's because the script only runs once, to go to the layout, so doesn’t evaluate fields in each record as the list is populated).  I also set up conditional formatting on said global field, with similar “if/then” conditions, but the fill doesn’t change either.

      (Goal: a flag indicating that any of several events in a list is overdue to happen, based on expected date/time of said event compared to current date/time)

      Clear as mud?

        • 1. Re: change field color and fill based on other fields?
          philmodjunk

          Say you have 3 values in your value list set up on a field named "Priority": "Urgent" ; "optional" ; "normal"

          You can set up 3 conditional format expressions for the same layout object or a group of layout objects. (can be fields, layout text, buttons, tab controls...)

          YourTable::Priority = "Urgent"   // specify the fill and text colors you want when record priority is Urgent
          YourTable::Pirority = "Optional"   // specify the fill and text colors you want when record priority is Optional

          If the priority for a given record is "normal", then the text and fill colors specified on the layout will be seen instead of one of the conditional format controlled settings.

          • 2. Re: change field color and fill based on other fields?
            FrankBell

            Thanks, Phil. But I'm wanting the value to depend on other fields in the record, rather than on a value list. For example, "if DATE IN RECORD > GET(CURRENTDATE)" then GLOBAL FIELD = "XXX"... and further, if... then GLOBAL FIELD = "YYY", ETC.

            • 3. Re: change field color and fill based on other fields?
              philmodjunk

              I'm afraid that doesn't make any sense to me.

              The global field isn't global if it depends on values from individual records. A global field stores a single value for all records in your table--which is not the case in your example as each record may have different values in the fields shown in your example.

              Perhaps you can post a more detailed example of what you are trying to do?

              • 4. Re: change field color and fill based on other fields?
                FrankBell

                Doh... You're right, of course. (I was thinking that a global value would change for each record... wrong). So now I understand that I need a calculated text field, not a stored field, which should work fine. But when I create the field and enter the calculation, it appears that the calculation doesn't happen, at least not right away. The calculation is an If() with two possible results, and most of the records in the list show no result... which would indicate that the field has not been evaluated. Is there some trigger that needs to happen for the field to be evaluated in each record?

                • 5. Re: change field color and fill based on other fields?
                  philmodjunk

                  I suggest posting the actual calculation that you are using. This can be done with a simple copy and paste from your specify calculation dialog. Also tell me if this is a text field with an auto-entered calculation or a calculation field set to return text. The results can be the same in many cases, but how they update can vary with each method in certain calculations.

                  • 6. Re: change field color and fill based on other fields?
                    FrankBell

                    Ok, here's the calculation:

                    If ( Date out  ≤  Get ( CurrentDate ) and (Status ≠ ("Has left") or Status  ≠ "Has returned") ; "Hasn't left yet?";"test" )

                    It's a text field with an auto-entered calculation. When I use this field in a list, it presently gives a return value for some records, but remains blank for most. (It'll be a more complicated "Case..." statement in the long run, but this will do for test purposes.)

                    Hadn't occured to me to use a calculation field returning text... would that make a difference?

                    • 7. Re: change field color and fill based on other fields?
                      philmodjunk

                      Fields with auto-entered calculations that use Get ( CurrentDate ) won't update when the date changes.

                      Stored Calculations that use Get (currentDate ) also won't update.

                      And Unstored Calculation will update.

                      Find your field in Manage | Database | Fields

                      Click it, select "Calculation" as it's field type and click change.

                      Now Double click it or click options to open the specify calculation dialog.

                      Click the storage options button and specify "do not store".

                      • 8. Re: change field color and fill based on other fields?
                        FrankBell

                        Roger that. Got it. I'm learning!