Boolean Number format

[LaRetta]

Am I the only one that is irritated when I format a number field as boolean and want the results to display as Yes or No ... but, because there is NO value, I cannot get the No to display? Boolean - true boolean - should produce NO whether the number is 0 or BLANK and that is how boolean always works ... except in this instance.

I assume it can't evaluate because the field is empty so is treated as null but that is pretty weak defense, in my opinion. When we specify a result (whether no or anything else), then FileMaker should default enter that value even if the field produces null.

I am still hoping that the boolean option will allow more characters as well. Maybe our next version will have it!

UPDATE: To clarify a bit more ... if I type dddd in a number field, it isn't empty. FileMaker says that any 'non zeros' will display as Yes (for example). But that is NOT true. FM knows we are dealing with boolean function and thus if ddd is typed into the field, it is ignored because it is not a number. So FM breaks its own theory.

Edited December 3, 2008 by Guest
Added update

[Søren Dyhr]

When is it most likely to be empty? Perhaps you should make a default autoenter 0 ... or make an updating one:

Case ( IsEmpty ( GetAsNumber(Self );0 )

...the only thing that could be a bit worrying is color and font gets zapped. But this trick works fortunately:

http://www.briandunning.com/cf/562

--sd

Edited December 3, 2008 by Guest
read the update

[LaRetta]

Hi Søren, yeah, I appreciate you attempting to solve it for me. It isn't that I am unaware of how to use auto-enter 0 or other methods ... it is that I shouldn't have to.

If I have a number field (whether calculation or standard number), and I want to display for users just that the field has a value, then I don't want to have to pre-fill my fields with 0. That simply goes against (even your) understanding of how to handle number fields; just as we don't pre-fill number fields with trailing zeros after a decimal.

This is why I post it in the wishes section and not posting it as an issue. But every time I bump up against this issue, it irritates me. So I plan to send my suggestion to FileMaker like a good girl because, if I didn't want a value in the 'no' field, I wouldn't type anything in that spot.

UPDATE: Typo error - rented fingers this week. ;-/

Edited December 3, 2008 by Guest

[Genx]

... I'm just confused... below is a series of tests and what they return. I think I'm missing the point?

If( "" ; 1 ; 0 ) returns 0

If( "asdfasd" ; 1 ; 0 ) returns 0

If( -16 ; 1 ; 0 ) returns 1

If( "1" ; 1 ; 0 ) returns 1

[LaRetta]

Hi Alex ... I'm speaking of formatting a number at the layout level as boolean. Or are you speaking to Søren instead? :qwery:

[Genx]

Wow, I've never even looked at boolean formatting on the layout level hahahaha. That could've saved a few headaches. It is a little counter intuitive in that case especially if their calc engine (per above) respects general boolean rules.

[LaRetta]

It is a little counter intuitive in that case especially if their calc engine (per above) respects general boolean rules.

Indeed. And especially since typing 'dddd' is NOT a null value but STILL won't produce the 'no' result. True boolean would consider 'dddd' as 0 or empty. It seems like a small thing but I would think it is a small thing to fix also and it bugs me! :tongue2:

[Genx]

It would be a bit rich for FileMaker to use the NULL excuse... They don't (visibly) believe in any such thing. Besides, null technically only exists prior to the field having a value - once the field has been set even once, it becomes empty. Distinct difference.

[LaRetta]

...null technically only exists prior to the field having a value - once the field has been set even once, it becomes empty. Distinct difference.

Ah. But the field has never had a value and wouldn't in this example either ... we are talking about DISPLAY only. This is why FM should STILL display the no value if requested. It's all an illusion anyway. :mickey:

[comment]

I agree that this should be an option (to format empty/non-numerical values as zero), but I would be VERY unhappy if that were the rule.

BTW, in form/list views this can be easily simulated with conditionally formatted text.

[LaRetta]

Yes and no; there are some limitations there as well particularly when results are null. For instance, conditional format will not work on summaries when the values are null.

[comment]

Can you elaborate on that, perhaps with an example?

[LaRetta]

For instance, conditional format will not work on summaries when the values are null.

I've no doubt you are aware of this, Michael. It again is one of the limitations of the boolean-like request to display 0 in null fields (even with conditional formatting). Business request is complex multi-column report, pulling data from 8 relationships and several calculations ... and display any blanks as $0.00.

In my example report:

Example 1 has no special formatting. It shows some calculations and then the resultant summary as blank.

Example 2 shows using conditional formatting underneath. There are several types of calculations which would work here, but I'm tired tonight so I used not not. Notice that the summary would not produce the zeros. But next to it (orange on the right), I used GetSummary() and that DID produce the zeros. Nonetheless, it means stacking zeros which takes time.

Example 3 shows attempting to uncheck 'Do not evaluate if all referenced fields are empty.' It does no good.

Example 4 shows changing the calculation to force display of the $0.00 if no results. But it only works if 'Do not Evaluate' is unchecked as well. When the calculation is unchecked, it also forces the summary to produce zeros because the field it is summarizing no longer is null.

Which method would I use? The 4th example (until I find a better way). Because stacking those $0.00 text fields behind every calculation and summary AND having to format each one independently (depending upon the field it is mirroring) is PIB.

The calculations and relationships are irrelevant (as far as I can tell) and (from testing) all calculations work the same in this regard. I was a bit lazy on producing this sample; please forgive its nature. But I hope what I'm trying to explain is clear. I'm not trying to solve the relationships nor the calculations - only to more thoroughly understand null/zero behaviors and i look forward to learning other options to force zeros or conditional format (boolean) to action on null fields. Sorry this is long ...

UPDATE: BTW, it isn't possible to force the zeros prior to these calculations (from within the other tables). We do not have access to that external information - only what appears on these reports (or in our field defintions in Main).

LaRetta

ConditionalZeros.zip

Edited December 4, 2008 by Guest

[comment]

I don't see how this is connected to Boolean formatting - it seems it has to do more with 'Format as decimal'. Anyway, have a look at the attached.

ConditionalZeros2.fp7.zip

Edited December 4, 2008 by Guest
replaced attachment

[LaRetta]

Windows cannot unzip the file because it has illegal character (*). Can you attach again, Michael?

Edited December 4, 2008 by Guest

[comment]

D@mn your Windows - what's wrong with the * character? It's a VERY nice character (see on the left).

[LaRetta]

Thank you for the new file. Yes, GetSummary() is exactly what I used on the orange fields. Conditional format works but I still need to stack the zeros under the fields if I want them to display AND apply a specific formula to each text box. You are right that conditional format could be used to produce 0 in null fields but there are limitations (as in a high cost).

My point was simply that conditional format fails the standard boolean understanding as well - ignoring null values on summaries (unless we apply tricks such as GetSummary()) and it still means stacking text behind the numbers. Efficiency-wise, I think I'd prefer using Case() within the already-required calculations.

As an aside ... Conditional format calculation box incorrectly reads: Non-zero values are true, zero and empty values are false.. Also, if you click learn more, it shows the standard Help for regular calculations. But conditional format is NOT regular calculation (notice the lack of the 'do not evaluate...' checkbox and the required boolean result.

Thank you for partaking! :laugh2:

[LaRetta]

ROFLMAO!!! Yes indeed! * is a very nice character; one of my favorites. Windows' mother sews socks that smell!!

[LaRetta]

By the way, on conditional format, I prefer to always show the default result in a color which displays. I see you even used grey instead of white so it could be seen. So I would do anything to reverse the boolean evaluation, such as using not not or GetAsBoolean() or whatever it takes. While in layout mode, I want to see the puppy!

UPDATE: My orange field using GetSummary() is just GetSummary() which produces white if true. :smile2:

Edited December 4, 2008 by Guest

[comment]

Conditional format works but I still need to stack the zeros under the fields if I want them to display

Have you looked at the "Example3" column?

[LaRetta]

Yes I did. But there is no conditional format on your Example 3. :blush2:

[LaRetta]

YIKES!! 1 * Sum ( Dept1::DollarsSpent)

Okay!! It still means affecting the calculation itself!! But I like yours MUCH MUCH better! YaY!!

[comment]

Well, you need to make some allowances for the fact that you're producing a "half-breed" report (mixing summaries with aggregating related sets), just to get some dummy data where nothing would appear on a "normal" report (one produced directly from the data table).

[Vaughan]

I often use ...

function + 0

to force the result to zero instead of empty.

[mr_vodka]

D@mn your Windows - what's wrong with the * character? It's a VERY nice character (see on the left).

Or a flower...

:P

[comment]

Don't you start again on that. It's an asterisk. Asterisk!!! Got it?