Case or If Question - Field Color Change on Validation

[Tony Martin]

Hi all - I have a situation where i'm trying to get a field to change color (from red to green) if and only if 11 other fields are filled in with a value. In other words, if the user enters data in 9 out of the 11 fields, the field would not change color until all 11 fields had some value in them.

Not sure if it matters, but 10 of the fields are radio buttons and 1 is a text field. And they only need a value selected (or entered in the case of the test field) - it doesn't matter what that value is.

In lieu of not being able to change a field color, I could alternatively have the field show the text "***INCOMPLETE***" in RED when some or all of the fields are not filled out, and "***COMPLETE***" in GREEN when they are.

How would I go about doing this in FMP 8.5? Any thoughts?

Your help, as always, is appreciated.

Tony

[LaRetta]

You can use Count() function, formula in conditional format such as:

Count ( text1 ; text2 ; text3 ; text4 ; text5 ; etc to 11 ) = 11

Set the fill to green. If the count equals 11, the fill will turn green. You can set the regular fill to red.

Edited June 1, 2009 by Guest

[Tony Martin]

Hi and thanks...can you expand on how I'd set the field color using the count function?

[LaRetta]

Select the field you wish to change color. Change the field color to red.

Then select Format > Conditional.

In the pop-up, select Formula. In Specify, enter the calculation (be sure to double-click each of your fields as you insert them, with a semi-colon between). Then down below, select 'Fill color' and specify green.

If you get stuck, use FM Help and search for 'conditional format'

Edited June 1, 2009 by Guest
Added sentence

[Lee Smith]

Conditional Formatting is a feature of v9 and newer.

[Tony Martin]

Hi Lee - thanks for the info.

What does this mean for me if I develop it using 9, and my users are on 8.5? Will there be an issue for them?

[Lee Smith]

They will not see the formatting in 8.5 and below.

You will want to use one of the other methods that we used in older versions. Do a search for Flag Fields. In the meantime, I look to see if I can fine some with example files.

Lee

[Lee Smith]

Here are a couple of examples

Link

Link 2

HTH

Lee

[Tony Martin]

Lee - you're the best - I see I've found the answer for the text colorization idea. Looks like I'm going to have to do it that way for now and try the conditional formatting when I get everyone switched to 9+.

One thing tho - in this example:

Case (IsEmpty(Debt) and Living_Status = "Renting" ; TextColor ( "Enter Debt" ; RGB ( 0 ; 0 ; 200 ) ); "")

... how many times can I use the AND statement consecutively? Any idea? I'm going to need to use it at least 11 times

Tony

[Lee Smith]

I don't know of any limit.

However, maybe you should tell us the conditions to be met.

Lee

[LaRetta]

You can still use the same calculation I provided. My problem now (and thanks for catching it, Lee) is that you are using a calculation which changes the color of the text instead of the background so I can't tell you how to adjust the calculation to fit your current situation.

Do you want a background change or text color change?

[Tony Martin]

I would prefer a background change, but can it be done in 8.5? Lee's "if isempty and" example works fine for text color changes, but our management team wants to see a box turn from red to green.

Thank you so much for your assistance - both of you :(

[LaRetta]

Create a global container called gBackground. Under Options > Storage, check 'global' and specify two repetitions.

Create a calculation (result is container in pop-up at bottom of calc box). Calculation would be:

Case (

Count ( text1 ; text2 ; text3 ; text4 ; text5 ; etc to 11 ) = 11 ; gBackground ; gBackground[2]

)

Select gContainer on the layout and Field Setup > Behavior. Under Repetitions, show 1 through 2 (you need to be able to paste into them). Create a small rectangle of red (do not have borders on it. Go to Browse mode and paste into repetition 1 of gBackground. Paste green in rep 2. If the rectangles don't fill the container, select the calculation field and select Graphics and change it to reduce/enlarge.

And I apologize for missing that you were using 8.5. I try to pay attention to that but I'm so used to using Conditional Formatting that I missed it.

Edited June 2, 2009 by Guest

[Lee Smith]

Here is a file that show LaRetta's approach that I made a while back.

Link

[Tony Martin]

I would just like to say for the record that, if I had doubts there is a God, I no longer have those doubts. Thank you two very much for your assistance. I don't believe I would have ever figured this one out on my own.

Tony

[Tony Martin]

Just an update...

We moved everyone to FMP 10 and this is no longer an issue.

T

[Lee Smith]

You will not regret the move.

Now you need to update your profile to reflect [color:blue]Version 10. :clap:

[Tony Martin]

Hi, my name is Jon. I'm currently working with Tony, and I've got a new issue with conditional formatting.

I'm looking for a way to perform a find request that will retrieve all records that fit a specific color code for a conditional format field. Any and all help would be greatly appreciated.

E.g. I need a Set Variable[] Line which searches on the status of the conditionally formatted field.

Again, any and all help would be very helpful.

Thank you.

[Tony Martin]

I think I've found a solution, it should just be as simple as replicating the conditional statement from the conditional formatting script and using the boolean result to determine the status of the conditionally formatted field. Any other suggestions/solutions would be great though.

====

We were wrong, it didn't work. SOS!!!

:THUD:

Edited June 19, 2009 by Guest

[LaRetta]

What is the conditional format calculation?

[Tony Martin]

Formula is

Count (

LVD_CLARK::BASREV_Roof;

LVD_CLARK::BASREV_Landsc;

LVD_CLARK::BASREV_Hsext;

LVD_CLARK::BASREV_Neighbr;

LVD_CLARK::BASREV_Forsale;

LVD_CLARK::BASREV_Garage;

LVD_CLARK::BASREV_Lotsz;

LVD_CLARK::BASREV_AC;

LVD_CLARK::BASREV_Pool;

LVD_CLARK::BASREV_Spa;

LVD_CLARK::BASREV_Brkwd;

LVD_CLARK::BASREV_OBStxt;

LVD_CLARK::Photo_1;

LVD_CLARK::Photo_2;

LVD_CLARK::Photo_3;

LVD_CLARK::Photo_4;

) = 16

[LaRetta]

When I gave the original solution for using Count() on multiple fields to check if they were empty, I didn't realize what the fields represented. I thought it was simple method to be sure a form was completely filled in by the User.

This is one reason, Jon, that it always helps to see the field names:

Formula is

Count (

LVD_CLARK::BASREV_Roof;

LVD_CLARK::BASREV_Landsc;

LVD_CLARK::BASREV_Hsext;

LVD_CLARK::BASREV_Neighbr;

LVD_CLARK::BASREV_Forsale;

LVD_CLARK::BASREV_Garage;

LVD_CLARK::BASREV_Lotsz;

LVD_CLARK::BASREV_AC;

LVD_CLARK::BASREV_Pool;

LVD_CLARK::BASREV_Spa;

LVD_CLARK::BASREV_Brkwd;

LVD_CLARK::BASREV_OBStxt;

LVD_CLARK::Photo_1;

LVD_CLARK::Photo_2;

LVD_CLARK::Photo_3;

LVD_CLARK::Photo_4;

) = 16

This HIGHLY indicates that these LVD_Clark 'pieces' should be related records to LVD_CLARK and not fields within the same table. You should have a Type field Roof, Landsc, Neighbr etc. Then you are simply finding or counting related records instead of always adding fields together. I have the feeling that this type of structure will bite you in the future and this won't be your only need. For instance, what if you want a report and a list of all properties broken down by their Type, ie:

Garage

1737 N. Simmons AVe.

22 Magnolia Drive

Pool

1421 Westinghouse

... etc. YOu won't be able to do it because one record can't appear in two different summaries! So am I going to answer your question about finding those records with Count equals 16? Sure. Create a calculation (result is number) and search for a 1 in it. Then consider if your base structure is correct before continuing forward because this need will come up again and again and I hate to see you use duct tape on your solution.

UPDATE: BTW, what happens when you add another photo? You will need to add another field and then remember to change your conditional format, your find script and/or your calculation ... and ... and ...

Edited June 19, 2009 by Guest
Corrected poor to pool

[mr_vodka]

Enter Find Mode []

Set Field [ LVD_CLARK::BASREV_Roof; "*" ]

Set Field [ LVD_CLARK::BASREV_Landsc; "*" ]

Set Field [ LVD_CLARK::BASREV_Hsext; "*" ]

Set Field [ LVD_CLARK::BASREV_Neighbr; "*" ]

Set Field [ LVD_CLARK::BASREV_Forsale; "*" ]

Set Field [ LVD_CLARK::BASREV_Garage; "*" ]

Set Field [ LVD_CLARK::BASREV_Lotsz; "*" ]

Set Field [ LVD_CLARK::BASREV_AC; "*" ]

Set Field [ LVD_CLARK::BASREV_Pool; "*" ]

Set Field [ LVD_CLARK::BASREV_Spa; "*" ]

Set Field [ LVD_CLARK::BASREV_Brkwd; "*" ]

Set Field [ LVD_CLARK::BASREV_OBStxt; "*" ]

Set Field [ LVD_CLARK::Photo_1; "*" ]

Set Field [ LVD_CLARK::Photo_2; "*" ]

Set Field [ LVD_CLARK::Photo_3; "*" ]

Set Field [ LVD_CLARK::Photo_4; "*" ]

Perform Find []

Or you can create a calculation that uses your prior formula and find for 1 or * in there.

*Edit - Didnt see LaRetta's post or pay attention to the field names. If her deducation is correct, then indeed follow her advice and use a related table.

Edited June 19, 2009 by Guest
added PS

[Tony Martin]

Tony here - Jon's in a coma at the moment :

...Are either one of you available for consulting to help us unravel the spaghetti that is our solution? If so, please send me a private email with contact info so we can discuss. I don't want to waste any more of your valuable time here in post-land Plus you probably should be getting paid for helping us...

T

[LaRetta]

Hi Tony, I'm not available; I can't speak for John. But here are some things to consider when you find yourself unsure of your design:

1) Post in the Classified section here for Services Wanted.

2) Private message Developers that you feel comfortable with and, even if they aren't available, they can recommend.

3) Here on FM Forums we help a lot of people get their base structure correct. If the base is right, the rest will come much easier without have to 'unravel the knitting' and redoing it. So you could post in Relationships, a demo file showing your base structures and explain your pieces (or you can post a cloned, zipped copy of what you have) and ask for suggestions.

As in any building project, the foundation is the most important. You may only need a few changes or you may need serious assistance. The decision can only be yours because we don't know your business, how much time/effort you have to expend, your design background etc.

Tell Jon I said 'hi' and I'm sorry we sent him comatose. :crazy2: