Conditional Formatting (like Excel)

[Conner]

Hi All!

Does FMP have a feature like Excel's Conditional Formatting?

Conditional Formatting is an option where a formula is created for a cell (not in the cell) where if the formula is true, the format of the cell changes.

Can FMP do this for fields?

-Conner

[Lee Smith]

yes.

[mz123]

Yes, you can do so by defining a field and clicking on Options...

Then choose Calculated Value and enter your conditional formatting, there.

[LaRetta]

Hi Conner,

Telling you 'yes' and nothing else doesn't help does it? Thanks, Martha, for helping Conner.

In addition to formatting a field as a calculation, you can also format a standard field by using an auto-enter (replace) calculation. When someone types into the field, the calculation will change the data and make it whatever you wish. If you have a specific need in mind and want to share it, we can help you get there.

LaRetta

[John Mark Osborne]

Make sure to uncheck the Auto-Enter option to "do not replace existing value (if any)" so your calculation continuously updates. You'll want to look at the Case statement to test for conditions as well as all the Text Formatting functions to change the format of the text.

[Genx]

Lee was just answering the question directly, it was after all phrased "can" not "can and if can then how". But then again wide interpretation is often required and i suppose the poster would like to know how to do it ...

[comment]

Actually, the correct answer is "No". Except for some very limited options to format a NUMBER conditionally (in Layout mode > Format > Number...), the Text formatting functions work inside the formula, and modify the data itself.

In Excel, you can define a cell to display as bold if a test is true, and the formatting does not affect the underlying data: you can define another cell as "=theCell", and the formatting will not be carried over.

In Filemaker, if you define a calculation to format the result as bold when a test is true, the result is styled text, with the style stuck to the data. Another field defined as "=theField" will show bold text as well.

[Conner]

Thanks for your help, everyone!

To clarify, I want to change several fields’ background color from blue to grey if the contents of another field contains the text "Closed". This will make it easier for the users to distinguish the "Closed" records from the "Open" records in List View.

My terminology might be a little off. I'm an Excel guru where "Format Cell" means "make the cell/field look different, but leave the contents untouched". In FM, "Format Field" means "set up the parameter of what appears inside" and that is not what I meant to ask about.

-Conner

[mz123]

Hey there - you can create a calculated container field.

If( Field1 = "closed"; ContainerYellow; "")

Where global Container Yellow is a container field that contains basically a yellow box (for the color)

Then put this field behind all the fields you want to "change the background on"

I hope that helps :P

[comment]

In FM, "Format Field" means "set up the parameter of what appears inside"

Not exactly:

- Formatting a field in Layout mode works on a layer that is separate from the data layer, just like in Excel. However, this cannot be conditional.

- Formatting by using a text formatting function in a calculation can be conditional. However, this works directly on the data layer.

The difference is moot here, because there's no function to change the background color of a field based on a condition, anyway. So the only way is to use a calculated container field, as suggested by Martha.

[Genx]

Hmmm, heres a question, at the moment i've got over 700 additional highlighting fields.. one for each field on a layout, is there anyway to use repeating fields or something if thats even relevant?

[Razumovsky]

Yeah, repeaters are great for that kind of thing!

I have just been having some fun with:

Evaluate ( "Extend("  & GetValue(FieldNames(Get(FileName); "YOURTO"); Get(CalculationRepetitionNumber)) & ")" )

in a repeating calc field.

Swap in any TO for YOURTO and access all your data in a single field. Seems like you have found a practical use for it.

-Raz

[Genx]

Still confused, could you dumb it down to lamen's terms. I'm really not at all proficient with repeating fields... at all.

Cheers,

~Genx

Edited June 10, 2006 by Guest

[Razumovsky]

Sure-

I'm just getting in to repeating fields after many years of shunning them, but they appear much more powerful than I had thought.

Create a repeating unstored text calc field of n repetitions (n being a number that is larger than the max number of fields you might ever want for that table)

Paste in the calc:

Evaluate ( "Extend(" & GetValue(FieldNames(Get(FileName); "YOURTO"); Get(CalculationRepetitionNumber)) & ")" )

Have a look at your field and all its repetitions. Each rep should show data from a different field for that record going in field creation order, with all fields being represented. You could create a second repeating calc field to use as a guide for the field names when you placed them side by side:

GetValue(FieldNames(Get(FileName); "YOURTO"); Get(CalculationRepetitionNumber))

So, now that you have all of your data in one field, you could apply logic to all at once. If you wanted to highlight all empty fields, format the calc as a container and then:

case(isempty(

Evaluate ( "Extend(" & GetValue(FieldNames(Get(FileName); "YOURTO"); Get(CalculationRepetitionNumber)) & ")" )

); HighlightContainer)

Of course, you will still have to remember to overlay each field on its correct repetition, but it should cut down your total fields by 699.

Note that the get(FileName) is indeed quirky. I would suggest scripting a global list of fields and swapping that for the FieldNames(Get(FileName) instances for reassurance.

[comment]

I believe that

Extend ( GetField ( GetValue ( FieldNames ( Get (FileName) ; Get ( LayoutTableName ) ) ; Get ( CalculationRepetitionNumber ) ) ) )

would do just as well, though I don't quite see why this would be useful. Neither will refresh well, BTW, which is understandable, as no field is expressly referenced.

I agree that Get ( CalculationRepetitionNumber ) is extremely useful - beats a custom function in many cases.

[Razumovsky]

Neither will refresh well, BTW,

I havent experienced that yet. I suppose I should incorporate a triggerfield autocalc for backup if I decided to use this.

though I don't quite see why this would be useful

If you wanted to highlight individual number fields among a collection whenever any went over 100.

If you wanted to highlight all empty required fields

If you wanted to make a master data key for dynamic portals...

I think this might come in handy in several situations. The complete FieldNames is overkill for most purposes, but a targeted list of data fields would be very appropriate I believe.

[comment]

I think I would feel more in control if I referenced the required fields expressly by name, rather than "wholesale". For a collection of 100 fields, I would use a portal with 1 field and 100 records. For a master key, I think getting the related ID's would be enough - but perhaps I am misunderstanding what you meant by that.

[Razumovsky]

I think I would feel more in control if I referenced the required fields expressly by name, rather than "wholesale".

I agree against the FieldNames function, especially after experiencing strange behavior with some design functions. Would you still feel uncomfortable working off a text list of Field Names?

For a collection of 100 fields, I would use a portal with 1 field and 100 records

Could you describe how you would do this to highlight your 100 required fields that were still empty?

*Ah, I see. I think you misunderstood my first point here. I meant more to have "Gauge indicator" type highlights that would turn red whenever any of many different number fields approached a value. Or that could globaly color code different dates (promise date, orderdate, builddate; finishdate; shipdate) by their distance from a given date. Kind of reaching here, but who knows what someone might want...

For a master key, I think getting the related ID's would be enough - but perhaps I am misunderstanding what you meant by that.

I am still figuring out what I mean by that too. Just occured to me a few hours ago, and is proving kind of fun. Basically, one big repeating field stored calc key that has Fieldname - Data in each repetition, referencing a local trigger field that updates anytime data is changed (to keep the key current)

ID - 1

Name - Bob

City - Austin

Sign - Taurus

Occupation - Florist

etc...

Use this at the child end of a relationship, and you can relate by any field you wanted (within the table, of course) through a single relationship.

Edited June 10, 2006 by Guest
ah

[Genx]

...ummm, so i can't use this?

[Genx]

As to your hundred related records comment. Um.. the thing is the condition is IsEmpty then highlight... so i'm not exactly 100% sure how it would work

[comment]

Would you still feel uncomfortable working off a text list of Field Names?

I don't know - it's hard for to me to think of a situation where there is "a bunch of fields" acting together as a group. Anything that comes to mind, I would probably make it a repeating field to begin with.

I find it even harder to imagine a single layout with 100 fields - would it be possible to work with something like that?

[comment]

the condition is IsEmpty then highlight... so i'm not exactly 100% sure how it would work

Me neither, since we are talking in abstract. I guess each record would have a field to be filled in, and a calc field to highlight it when it's not?

[Razumovsky]

GenX-

I think if you are indeed trying to higlight many fields in a table if empty, there is no reason why you can/should not use this (in fact, if you are happy with your structure, it sounds like precisely what is called for).

I believe comment is reacting to the fact that so many fields on a single layout/table hints at errors in normalization. I would tend to agree, but I suppose there could be cases...

You do not need over 100 fields to put this to use though, It can be helpful anytime you want to apply the same logic to more than one field.

With that being said, if you have 700 fields that evaluate 700 other fields to highlight them if empty, you could easily reduce this to two fields until you address deeper structural issues:

Create 2 repeating fields:

tList

cHighlight

List will just have each field you want included in the highlight condition in a separate repetition. Highlight would be:

Case(IsEmpty(Extend(GetField(tList))); 1)

[comment]

I believe comment is reacting to the fact that so many fields on a single layout/table hints at errors in normalization.

Actually, I meant that having so many fields on a single layout is crowding the layout to the point of being incomprehensible. Unless you're building some kind of a Christmas tree - and that (I said) I would make with repeating fields to begin with.

[Razumovsky]

True,

I am having a hard time imagining the need as well. ..

[Genx]

Lol, i didn't say it was on a single layout, its on around 150... and the highlight fields are distributed through 50 tables. Honestly, i'm a little nuts but not that crazy .

[Genx]

Create 2 repeating fields:

tList

cHighlight

List will just have each field you want included in the highlight condition in a separate repetition. Highlight would be:

Case(IsEmpty(Extend(GetField(tList))); 1)

Now, back to the issue at hand, i'm not sure if you comprehend exactly how much i don't comprehend about repeating fields...

Just to clarify then: cHighlight of the particular repetition is placed be hind the appropriate field? Further, tList would what type of field exactly, global? Stored Calc? And how would i specify the data for it to store. I'm sorry, i just still havent grasped the concept of repititions, i sort of get it, yet i don't... you know?

Hmmm,

Cheers,

p.s. I am not building a christmas tree comment !

~Genx

[Razumovsky]

Sorry, my calc was a bit over-simplified.

I would have it a global gList.

cHighlight should be unstored non global container calc, and should read:

Case(IsEmpty(Extend(GetField(gList))); gYellowContainer)

Where gYellowContainer is a global container field that has yellow fill stored in it.

Now, in gList, type a field name in each repetition for the fields you want to monitor.

The corresponding repetition of cHighlight will be blank or yellow depending on if the field is empty or not.

[Genx]

Yay that makes sense... Not that i'm going to do it right now in my current solution because of the sheer amount of time it would take. But, good for future reference.

Cheers Raz,

~Genx