Simple Script for Color Change

[Jonathan Smith]

Hello,

I recently began using file maker, however, I am not yet used to the format of the scripts; I used to program in Visual Basic, therefore, the scripting in filemaker is still a bit foreign to me.

Anyways, I am working in a database that was already created. The script I am attempting to make needs to recognize text in a field, and change the color of other fields based on what is found. For example, the "Status" field ranges between 9 steps;

Case (Date Quote Dead;"LOST";

Date Contracted;"WON";

Pricing Submitted;"7 PRICING SUBMITTED -";

Price Approved;"6 READY TO SUBMIT -";

Take off finished;"5 NEEDS MANAGER APPROVAL -";

Estimating Accept;"4 PRICING IN PROGRESS -";

Estimating Reject;"3 ESTIMATING NEEDS MORE DETAILS -";

Rec Planes;"2 ESTIMATING REVIEW -";

Date;"1 PLANS NEEDED -")

What I need to do is, after this status is calculated, I need to change the text of other fields based on this status. I attempted the following:

If [Get (Status) = "LOST"]

TextColor(QuoteID; RGB(#,#,#))

TextColor(Salesman; RGB(#,#,#))

TextColor(Total Copy; RGB(#,#,#))

I immediately get an error. Any suggestions?

[mr_vodka]

Each of the fields that you want to change the color for will have to have ts own Case statement. You can use an auto entry of calculated result with the 'do not replace...' Unchecked.

So for QuoteID it would be something like:

Let ( [ f = QuoteID;

          n = Case ( Status="LOST"; 1;

                            Status="Won"; 2;

                            Status="BLAH"; 3;

                            0

                           )

        ];

        Choose ( n; TextColorRemove (f); TextColor(f; RGB(255;0;0)); TextColor(f; RGB(0;255;0)); TextColor(f; RGB(0;0;255)) 

                   ))

[mr_vodka]

Actually, now that I take a second look at it, you may want to create a new calc field to do the checking since in the end it seems are though the criteria is the same.

This calc could be called cCheck and do the following:

Case ( Date Quote Dead; 1;

       Date Contracted; 2;

       Pricing Submitted; 3;

       Price Approved; 4;

       Take off finished; 5;

       Estimating Accept; 6;

       Estimating Reject; 7;

       Rec Planes; 8;

       Date; 9

     )

and then the Status field would simply be:

Choose ( 

         cCheck; ""; "LOST"; "WON"; "7 PRICING SUBMITTED -"; "6 READY TO SUBMIT -"; "5 NEEDS MANAGER APPROVAL -"; "4 PRICING IN PROGRESS -"; "3 ESTIMATING NEEDS MORE DETAILS -"; "2 ESTIMATING REVIEW -"; "1 PLANS NEEDED -"

       )

...and finally for each of the fields that you want the color to change for (using auto-entry calculated value with 'do not replace...' unchecked)

Let ( f = QuoteID;

        Choose ( cCheck; TextColorRemove (f); TextColor(f; RGB(255;0;0)); TextColor(f; RGB(0;255;0)); TextColor(f; RGB(0;0;255)) 

                )

     )

P.S. The first Case statement could probably be optimized but you get the point.

[Jonathan Smith]

John-

I have made the following changes:

Let ( [ f = Quotes::Quote ID;

n = Case ( Quotes::Status="LOST"; 1;

Quotes::Status="WON"; 2;

Quotes::Status="7 PRICING SUBMITTED -"; 3;

Quotes::Status="6 READ TO SUBMIT –"; 4;

Quotes::Status="5 NEEDS MANAGER APPROVAL -"; 5;

Quotes::Status="4 PRICING IN PROGRESS -"; 6;

Quotes::Status="3 ESTIMATING NEEDS MORE DETAILS -"; 7;

Quotes::Status="2 ESTIMATING REVIEW -"; 8;

Quotes::Status="1 PLANS NEEDED -"; 9;

0

)

];

Choose ( n; TextColorRemove (f); TextColor(f; RGB(255;0;0)); TextColor(f; RGB(0;255;0)); TextColor(f; RGB(0;0;255))

))

When I run the script, I don't get any errors, but the field color doesn't change... Any suggestions?

[mr_vodka]

Oh sorry I should read more carefully...

I didnt realize that you wanted it to change via a script. I thought you wanted it to change automatically. Do you still want to do it via a script?

[Jonathan Smith]

Well, it doesn't have to be a script. was the code that you put in for the definition of the field? actually, i'd really rather do it without using a script...

[mr_vodka]

cCheck is a calculation field. The status field could be a calculation field as well if you do not intend on entering data directly into it and want to use if for display purposes.

The other fields will have to be fields with an auto-entry option of calculated result with the 'Do not replace'... unchecked as I previously mentioned. This is because they are field that need to be able to be data enterable.

[Jonathan Smith]

ok, I created a new cCheck calc and a cStatus calc. I see how the cCheck produces the corresponding number. I also created a cQuoteID which changes colors. I think that did the trick. If the field that changes colors is opened in a seperate table, will the color information go along with it? (By the way, thanks a lot for your help... I just started this job and it's nerve racking having to learn an all new program)

[mr_vodka]

If you made the QuoteID an auto-enter calculated value then yes the color is stored within the field content. However if you made a new Calculation field that only displays the color, then no it would not obviously. I think you may have done the latter since you have cQuoteID ? and you are welcome.

[Jonathan Smith]

Yes, you're right... when I attempt to open the cQuoteID, cSalesman, and cTotal COpy in another layout all it shows is a question mark. Is there a way to have thie colored info appear ?

[mr_vodka]

Well depending on what table occurance the layout represents and also the relationship structures of your db then it may not show. Are the tables related?