It takes a bit of detail work but it can be done. We used to do this long before we could use a conditional format on a field.
Define 1 global container field and paste or insert a red rectangle into it
Define 5 calculation fields for your bar. In each calculation, select Container field as the result type. Place them adjacent to each other on the layout like you would with your conditionally formatted fields.
Give each calculation field an if or case function that returns the contents of the global container field if the values in the record indicate that the field should have a red fill color.
On the other hand, this looks like something where you could add a second chart object with a single horizontal bar also.
If it can be done by a second chart I will try that first. Might be hearing back from me on this :)
It should be possible to get the chart, but you may not be able to get a format for your chart object that you can work with.
Been trying to get this to work and I am stuck. In the conditional format I have the option of picking numbers between ranges and I do not know how to write that script for the calculatioin field. So here are the fields that I created:
The number that they will be basing if the bar needs to change color is off of this field
5 calculation fields:
You wrote, "In each calculation, select Container field as the result type." Don't know where that option is.Gender = "Female" ;"< 19.1")OptimalBF%Range: Case (Gender = "Male" ;"10-15";Gender = "Female" ;"19.1-24")ModerateBF%Range: Case (Gender = "Male" ;"15-18";Gender = "Female" ;"24.1-29")HighBF%Range: Case (Gender = "Male" ;"18-21";Gender = "Female" ;"29.1-34")VeryHighBF%Range: Case (Gender = "Male" ;"> 21";Gender = "Female" ;"> 34")But for example in OptimalBF%Range field, instead of giving me range of Male 10-15 or Female 19.1-24 it uses that minus sign as subtraction. That is why I have to put the parenthasis in there but it gives me "10-15" and "19.1-24".It should be if the BF% is 20% (MeasurementsData::Fat%) for Female that the red bar would fill because it falls in the range 19.1-24. Or if BF% for Male is 12% (MeasurementsData::Fat%) it falls in range 10-15 and the red bar would fill.
See the screen shot for the drop down where you select a result type for a calculation field.
I'm not sure if you are showing a red rectangle just for the range or like a bar chart bar where a reading in the High range would color in all the fields up to High, leaving Very High still white.
I will guess that you want just the field in the specified range to change color. I will also assume that your percentages are stored as decimals and displayed as % values. If that is not the case, you'll need to move the decimal point two places in my example. To display a colored rectangle when Fat% is in the moderate range, you would use a calculation field with a case function like this:
Case ( gender = "Male" and MeasurementsData::Fat% > 0.15 and MeasurementsData::Fat% < 0.18 ; Globals::gContainerFIeld ;
gender = "Female" and MeasurementsData::Fat% > 0.241 and MeasurementsData::Fat% < 0.29 ; Globals::gContainerFIeld )
Globals::gContainerField is what I am calling the global field where you have inserted a red rectangle.
The two logical expressions that check gender and %Fat should be very similar to the ones you used for conditional format expressions.
You can format a calculation field like this on the data tab in the Inspector to "enlarge or reduce to fit" and you can clear the "Maintain proportions" option. Then any rectangle that you insert/paste into the container field will fully fill the field.
Does the Global table need to be releated to another? Also when you mention insert red rectangle in the global field what do you mean by that?
Sorry and appreciate your patience and help.
A field with global storage specified in field options can be defined in any table in the file and it will still be accessible everywhere in your file. No relationship is needed. I put such global fields in a common "globals" table for convenience but you do not need to do so.
A container field stores files--including graphic images. To put a graphic image in a container field you can paste into the container field or you can select one of the insert menu options. Since all we need is a colored rectangle here, you can enter layout mode and use the rectangle tool to create the colored rectangle of your choice. THen copy it to the clipboard, enter browse mode and paste it into the global container field.
So I finally got everything in. Place the red bar in the container. But the bar does not change length. It stays full.
Did you put one field in place or 5? to show the bar code? You need 5 calculation fields so that the bar code can change lenght and this differs from my assumptions in my previous post. The method I describe still works, but the logical expressions you'd use in each of these calculation fields would need to change.
The global field does not need to be on this layout at all.If you place it on top of the other 5, it will obscure them and show a single bar (if data formatting has been set to reduce/enlarge to fit.)
Oh :) It works now!!! The bar is narrow though. Any way of widening it? Where do I need to put the global bar? Can I just move it in the gray area within the layout?