Jump to content
Sign in to follow this  
Jonathan Smith

Simple Script for Color Change

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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)) 

                   ))

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 ?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.