topic Re: Nulls Causing Conditional Formatting to Fail in App Development

Nulls Causing Conditional Formatting to Fail

steverosebrook — Sat, 16 Nov 2024 21:31:08 GMT

In some of our table objects we're applying conditional formatting using a background color expression. Specifically, we're applying color banding to the rows of the table to make things easier to see. We've noticed that for cells that are null, the formatting doesn't work.

Here's the formula we're using:

if(RowNo(total)>=1 ,if(mod(Rowno(total),2)=0,RGB(184,204,228),RGB(220,230,241)),if(RowNo(total)=0,RGB(0,60,100)))

This works for everything but null cells. A screen shot of the issue is below. These nulls aren't in the source data - they are "app-side" nulls caused by a lack of data points for certain combinations of fields/tables. So I don't think we can handle this in the load script.

Any ideas on how we might tweak our above formula to handle these nulls?

Thanks,
Steve

Re: Nulls Causing Conditional Formatting to Fail

y_grynechko — Mon, 28 Jan 2019 20:06:04 GMT

Hey,

is this RateM... field calculated or just a field loaded from database?

Re: Nulls Causing Conditional Formatting to Fail

steverosebrook — Mon, 28 Jan 2019 20:23:08 GMT

It's a field loaded from the database.

Re: Nulls Causing Conditional Formatting to Fail

y_grynechko — Mon, 28 Jan 2019 21:38:16 GMT

Okey, so in the column just use this if:

=if(IsNull([Your Field]),'-',[Your Field])

this will allow you to color field with '-' in it same as other fields.

Re: Nulls Causing Conditional Formatting to Fail

steverosebrook — Mon, 28 Jan 2019 21:51:49 GMT

Unfortunately, that won't work in our case. The nulls in this particular field aren't in the source data - they're being caused on the app side. For nulls in fields/cells in the source data, we typically try to handle them in the load script. However, there are also nulls that show up in the app, due to combinations of products/dates/etc. that didn't even exist in the source data. For these app-side nulls, I've never found a good solution on how to handle them.

Re: Nulls Causing Conditional Formatting to Fail

y_grynechko — Mon, 28 Jan 2019 22:20:35 GMT

do you use where exist when you load tables?

Re: Nulls Causing Conditional Formatting to Fail

y_grynechko — Tue, 29 Jan 2019 07:39:53 GMT

This generated NULL value is being called Missing value and there is a lot about trying to color it in this community.

I did go through threads but didn't find solution.