Cell References in #IF tag

I have a master-detail report where both the master and detail datasets have a price field. I wish to color the detail prices where they are lower than the relevant master price.

So, say, the master cell in I6 contains <#master.price>, and the detail cell in I7 contains <#detail.price>

I have the coloring happening OK in that some cells have the special font color, and some do not. But it is not obeying the intention of the #IF tag. I won't bore you with all the variations I have tried.
They ranged from the simple "<#if I7 < I6;<#Format Cell(Lower)>;)>" on upwards - all without success.

Any assistance greatly appreciated.

Hi,

In general, you can't reference other cells inside <#tags>, because you can't be sure those cells will be filled when the <#tag> is evaluated. The order of evaluation is not defined, it used to be down-up and left to right (that is FlexCel started filling the report at the last cell, say D7, then D6... up to A1). Today the order is from A1 to left and down, but in the future we might introduce parallel filling where cells A1 and A2 are filled at the same time by different threads.

So in short: Don't reference cells from tags. Instead, use what the cell has. In your example, if I6 contains <#master.price> and i7 contains <#detail.price>, then write:
<#if <#detail.price>  <  <#master.price> ;<#Format Cell(Lower)>;)>

If the contents are more complex, say I7 is at its time some <#If...> thing, then you can define an expression in the config sheet. So you define for example the expression "DetailPrice" to be <#if...whatevergoes in i7>, then in I7 write <#DetailPrice> instead of the if, and the format becomes:
<#if <#DetailPrice> < I6;<#Format Cell(Lower)>;)>

That is, if the expression in the cell is simple, just write it directly, if not define an expression and use it both in the cell and in the <#if>

Another thing you can try, to have a better idea of what is going on, is to debug the report:
http://www.tmssoftware.biz/flexcel/doc/vcl/guides/reports-designer-guide.html#debugging-reports

This will tell you the values of i7 and i6 and you can get a better idea of what is going on, but anyway, as said, just use as a rule not to reference any other cell that is filled dynamically. The order in which the cells are filled is not defined and has changed in the past, and might (likely) change in the future.

Now, with all of this being said, make sure that you can't use normal Excel conditional formatting here too. Using semiabsolute references in the report ( http://www.tmssoftware.biz/flexcel/doc/vcl/api/FlexCel.Report/TFlexCelReport/SemiAbsoluteReferences.html )you can reference the master in each detail subreport ($I$6) and it will be changed for every detail (detail 1will use $I$6, detail 2 will use $I$10, etc)

Thanks, Adrian, for another detailed reply.


I had actually tried your first suggestion but it threw an exception - probably a minor typo somewhere. Anyway, it's all good now. Cheers.