Automating conditional formatting

[Jarvis]

I have developed a business need to be able to conditionally format the active field on the fly.

I have a database that has a list of manufacturing tasks.

The field names in this database are:

Task 1

Task 2

Task 3

Etc.

There is an additional field that counts the number of workers that are available to perform these tasks.

I would like to be able to change the colored fill pattern for any particular field based upon quantity of workers available.

For example:

If WORKER QUANTITY = 1, Task 2 = BLUE FILL

If WORKER QUANTITY = 2, Task 2 = RED FILL

I would like, however, to be able to accomplish these formatting decisions without going into layout mode,i.e., I would like to script this conditional formatting.

Is there a way to do this within filemaker or does this require learning applescript?

For what it is worth, I am running OSX 10.4.11

Any ideas?

[Vaughan]

Things like this are often accomplished by setting up a preferences table to hold the data or formulae.

At some stage the conditional formatting needs to be set up in layout mode.

If the underlying issue is that there are dozens or hundreds of fields to format, then the data structure might not be correct.

[David Jondreau]

It's not clear what you want to do...Do you want 1 worker to be blue and 2 to be red for all time or does that change day to day? What is the significance of the colors? What do you mean about not going into layout mode? Ever??

[Jarvis]

Here is the real world problem I am trying to solve.

We build a variety of products at my cabinetshop.

Examples might be drawer boxes, doors, faceframes etc.

Each product has a specific set of processes.

The specific task assignments depend on how many people we have available to allocate to the task. For example, if there is only one person available, all task fields would have a yellow fill pattern.

If two people were involved, some of the task fields would stay yellow but others would be green. In the case of three there would be a third colored fill pattern.

The intention here is to produce a visual map of how to proceed, with task assignments being color coded depending on how we want to set up the play.

Conditional formatting can do this at the layout level but I would prefer to be able to make these modifications in a more nimble fashion. Does this help explain it?

[David Jondreau]

I'm not sure your resistance to layout changes, that's how Filemaker works.

I'm assuming each task is a different record.

One things you could do is set up your conditional formatting with the following criteria:

$color = "Green" then green fill

$color = "Red" then red fill

$color = "Yellow" then yellow fill

etc

Then in your task table set up an unstored calc field with whatever criteria you have:

color =

Let([

//put your task color criteria here, eg

part = tablename::part;

num = tablename::num workers;

$color = Case(part = frame and num = 1; "Green"; part = frame and num > 1; "Yellow"; etc)

];

$color

)

Depending on how flexible you want the color coding to be, you may want to create a table to store the criteria and the result in. That way you don't need to redefine the field every time the criteria changes.

Edited October 12, 2008 by Guest