Controls

Re: Conditional Formatting on Calculated Control in Repeating Table/Row isn't evaluating

Michael Boonie — Wed, 30 Jan 2019 18:58:53 GMT

Peculiar, because mine is set that way as well. My formula calculate fine, but it's the conditional formatting that goes awry.

I have a very-redacted version which reproduces the problem. RequestForm_demoNullsBad (attached) illustrates how each text box in the row has to have something in it for the Conditional Formatting to work properly. I can also upload RequestForm_demo if anyone wants, which illustrates a hack I cobbled together based on another "If()" hack I found a couple of years ago. Essentially, I use a variation on the following line for each text box, to make sure the conditional format has at least a 0 to work with:

number(concat("0", substring(my:QtyBlueTop, 1, (my:QtyBlueTop > 0) * string-length(my:QtyBlueTop))))

(Now that I look at it, it's unnecessarily complex for my purposes. I think number(concat("0", my:QtyBlueTop)) will do... oops! I also caught an error when totaling each row, but that doesn't matter for now, since it's the Condition for the formatting rule that needs tweaking.)

The template now incorporates the "run-time variable" that I described above, which is the factor by which the text-box amounts are multiplied; the numbers aren't hard-coded, but stored on Form Load in a secondary XML "FormLogic" file. (I also have a version that simply uses a rule to set a value to 0 if it happens to change to an empty string, which I described above.) I am curious about which method is easier for the browser to render (and why it's necessary at all, frankly.)

Re: Conditional Formatting on Calculated Control in Repeating Table/Row isn't evaluating

Hilary Stoupa — Wed, 30 Jan 2019 17:19:07 GMT

I actually just tried this in a form - and my calc'd default value came back as 0 when the fields that made up the calculation were blank - which caused me to remember the setting in the attached screenshot. Does changing that for your form make a difference?

Re: Conditional Formatting on Calculated Control in Repeating Table/Row isn't evaluating

Michael Boonie — Wed, 30 Jan 2019 15:35:17 GMT

Ah, well... picking up that line of thought, I tested my values a little more thoroughly and found that the conditions DID evaluate properly as long as none of the values were BLANK. There had to be a number in each field, even if that number was 0.

Which now begs the question: What would be the most appropriate way to address that? Having a default value isn't enough, because the formula still sees "" instead of "0".

Here is the criteria/formula:
my:QtyBlueTop * 4.5 + my:QtyRedTop * 10 + my:QtyMarble * 8.5 + my:QtyPurple * 4 + my:QtyGreen * 4 + my:OtherAmt * 0 > 24

If any of the fields are empty, the condition apparently doesn't evaluate as a number, and the conditional formatting doesn't evaluate as needed. (The actual numbers--4.5, 10, 8.5, etc.--will be replaced with run-time variables after I get this figured out... although that may be a challenge in itself.)

Is there a function I can wrap around each addend or value so that empty or null is treated as 0, or so that the formula uses 0 if the value is empty or null? Or is the solution more fundamental? (I know this is a rookie skill, but I promise I'll save the answer in my OneNote notebook this time!)

Thanks, Hillary!

(Edited ten minutes later!):
OK, so I just wormed around it by putting a rule on each text box that sets the actual value of the bound column to 0 whenever the value changes to null/empty. So, the question is now primarily academic: Is running a rule like that more "efficient" (great gobs of memory notwithstanding), or is it a better use of resources (the computer's, not mine) to rewrite the formula itself to check for nulls and use 0 instead? (Sort of a "give-a-control-a-fish or teach-a-control-to-fish" polemic.)

Re: Conditional Formatting on Calculated Control in Repeating Table/Row isn't evaluating

Hilary Stoupa — Tue, 29 Jan 2019 22:34:41 GMT

So, for me at least, when I run into formatting not behaving the way I expect, most often it is because the value of something isn't what I think it is....can you share your form? Just attach the template under the options tab in a reply...

Conditional Formatting on Calculated Control in Repeating Table/Row isn't evaluating

Michael Boonie — Tue, 29 Jan 2019 22:25:04 GMT

Hi. I'm not sure I should pursue this on my own until someone with more insight than I have weighs in. I am designing an InfoPath form (not a List form) with a very straightforward structure. In the data structure, I have one single repeating section, called "Animal", and this section uses "default values" to captures and display the properties of 12 animals, e.g. D.O.B., weight, etc. At the end of the row, I have a field that calculates a value which is the sum of several values in that row, each of which are multiplied by the weight shown in the row. This "Total" calculation works, and recalculates in real time when the other factors change; but basic conditional formatting (used to highlight a total when it exceeds a preset limit) does not. The condition/formula is simply "if the sum of all five calculated values in this row > 24" but it never "kicks in," neither in Filler nor as a browser-form. But while the calculations for each row update as expected, no Formatting Rules are being run on this repeating table. (They do run elsewhere on the page, however.) Is this a known bug/undocumented feature? Thanks in advance for your help. -Michael Boonie