Conditional formatting based on multiple rows or fields

[elsenorjose]

Hello everyone,

I'm hoping this is a simple fix but I need to conditionally format fields that don't match another field in my report but based on them showing up ANYWHERE in the fields I'm comparing.

Basically, in the attached, for Log #123 (which identifies a surgery), you can see that Surg1 'Z' does not show up under Prov_Name and all fields are correctly highlighted. However, Surg2 and Surg3 'A' does show up under Prov_Name so I would not want any of their fields highlighted (a Surgeon can show up under any of the 3 Surg fields). The same goes for Anesth2; since they do show up under Prov_Name, I would not want any of their fields highlighted. Anesth1 is correctly highlighted as they do not show up in Prov_Name at all. The business rule is that if a surgeon was involved in a surgery, Log#, they should also have a matching Prov_Name record as that identifies a billable service. If they do not show up under Prov_Name, then billers need to research and see why they did not charge for their service.

My formatting formula is as follows:

If {VW_SURG_FOR_SCL_1.PROV_NAME} <> {BILLPROV.PROV_NAME} Then crTeal Else crNoColor

I realize the problem is that because the logic is row based, as soon as the Prov_Name changes, the field will be highlighted. Is there a way to write this formula to look at ALL Prov_Names? I'm not sure if it's important but the Log#s change also. Each Log# identifies a surgery.

 

[IanWaterman]

Not sure what you are trying to achieve, can you show how you want output to look.

Ian

 

[Madawc]

I haven't got your attached item, security stops it and it's usually best to paste data as text.

Making a guess, you want formatting based on the properties of several records in a group. The general method is to use a Formula Field, e.g.

If {code} in ["A", "E", "O"]
then 1
else 0

You can then do a summary total of this field for the group. Summary totals can be tested for formatting etc. Hopefully you can adapt this method for your needs.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[elsenorjose]

Thanks for the replies. It's hard to describe without the visual because I'm conditionally formatting field background colors based on certain conditions.

Madawc, your suggestion doesn't quite get me to where I need to be since I don't know what the values would be ahead of time. I can't do an 'in list' condition since I don't know what the list would be. Is there any other way I can post an image as it will make it a lot easier to understand with the visual?

Also, sorry about not posting CR version. Rookie mistake, I should know better. I'm using CR 2008

 

[elsenorjose]

Hi again. I received some more direction on this and the best way to explain it is basically I need to emulate VLOOKUP functionality from Excel.

I'm posting the image again from a different site, perhaps that will enable it to be viewed.

Basically, I need to compare values in the Surg1, Surg2, and Surg3, Anesth1 and Anesth2 columns to the values in column Prov_Name and based on the Log #. So, for the pictured example, for Log # 123, look up the values in the surg and anesth columns, compare to the prov_name column and if there is no match, highlight the column. If there is a match, don't highlight. So, if the value in column Surg1 was SurgZ for example, and there is no SurgZ in Prov_Name for that particular Log #, then it should be highlighted. Since SurgZ could appear multiple times within the Log #, every instance of his/her name should be highlighted.

 

[IanWaterman]

Can see new file get this error

403 Forbidden


You are not allowed to access this page. Possible problems:
Missing index file
Misconfigured mod_rewrite settings in .htaccess
Authentication Failure
Incorrect file or folder permissions

Looks like you need to capture data in an array, declare and array in report or group header with enough elements to cater for surgeons and anesths.

Populate with names or IDs as required for each Prov_name group

You can then use the array in your formatting

whileprintingrecords;

global stringvar array list;

If surgeon in list then teal else white;

Ian

 

[elsenorjose]

Ian,

I'm not very familiar with arrays, not having used them previously, so I'm not sure if I understand correctly. When I define the array, do I use the field Prov_Name or Prov_Id or do I need to actually enter the values? Since I won't know who will show up in the report, I won't know what values to enter.

Also, is there a way to make the array 'reset' itself, for lack of a better word when it encounters a new Log#? As Log#s change, the same surgeon from a previous surgery could also be listed and I would want the array to know it's in a new surgery and therefore needs to 'start over'.

And lastly, can you recommend a good resource to learn arrays? The only document I could find on SAP's site is ages old. Not sure if the same syntax and limitations still apply to CR 2008.

Thank you.

 

[IanWaterman]

You will need to declare your Stringvar array in relevant group header, an array can have up to 1000 elements so make it big enough to hold list of surgeons and anaests etc
Depending on how you data is arranged you might be able to use one array for both Surg and anaest. Failing that you might need one for each.

@Inialise

whileprintingrecords;

Global Stringvar array list:=('', '', '','',........);
Global numbervar n:=1; // this will be array position count

I do not have CR2008 so not sure if you can dynamically size arrays, in older versions they are not dynamic.


In Details or a lower groupheader

@eVal


whileprintingrecords;

Global Stringvar array list;
Global numbervar n;

If Not(SurgeonID in list) "and what ever rules you require" then

(List[n]:= SurgeonID field or anaestID ;// assuming IDs or names are strings

n:=n+1;

Then in a lower group section where you are using highlights, use this to control highlighting

whileprintingrecords;

Global Stringvar array list;

If {Surgeon ID in List} then crteal else crwhite

Hope this gives you some guidance

Ian

 

[elsenorjose]

Thanks again for the direction.

My report doesn't have any groups, will this still work?

Also, what does this part of the @Initialise formula mean:

Global Stringvar array list:=('', '', '','',........);

Do I need to populate the list manually?

 

[IanWaterman]

The initialise creates the Array

You do not need to populate manually, the formula @eval should do that for you.

You will need to group on at Prov_name as a way of resetting your lists. You can suppress the group headers and footers. The header will hold the initialse formula.

Ian

 

[elsenorjose]

Ian,

Thank you very much for all your assistance. I took a slightly different, but similar approach that did not involve arrays. I used the '3 formula' trick to accumulate the list of Providers I want to compare. Basically, it's what you were suggesting but without using arrays.

@InitProv // In group header
WhilePrintingRecords;
StringVar ProvName := "";

@AccumProv // In details
WhilePrintingRecords;
StringVar ProvName := ProvName + " " + {CLARITY_SER_TDL_BILLPROV.PROV_NAME} + "; ";

@ProvList // In group footer
WhilePrintingRecords;
StringVar ProvName;

So my follow-up question is this: Now that I have a semi-colon delimited list of providers in the group footer, what would be the best way to search that list to see if any of the values in my columns can be found and format accordingly. I need to take into account the fact that if there is only one name, there will be a trailing semi colon after the name.

I tried the following in the conditional formatting section of the field:

If {VW_SURG_FOR_SCL_1.PROV_NAME} In(split({@ProvList},";")[1]) Then crTeal Else crNoColor

And I get an error - "A subscript must be between 1 and the size of the array"

I've seen that error before and some explanations here in the forum, but not specific to conditional formatting. Any suggestions?

Thanks again!

 

[IanWaterman]

Instead of using the formula try just the varaibal

WhilePrintingRecords;
StringVar ProvName;

If {VW_SURG_FOR_SCL_1.PROV_NAME} In(split(ProvName,";")[1]) Then crTeal Else crNoColor

It could also be that Provname is empty when you test

Try testing first

If Instr(provname, ";") >0 then
(If {VW_SURG_FOR_SCL_1.PROV_NAME} In(split(ProvName,";")[1]) Then crTeal Else crNoColor)
Else crNoColor

Ian

 

[elsenorjose]

I can't believe how hard this is turning out to be

Since I'm looking for values in my report columns that are NOT in the provlist, I changed it slightly:

WhilePrintingRecords;
StringVar ProvName;
If Instr(provname, ";") >0 then
(If Not({VW_SURG_FOR_SCL_1.PROV_NAME} In(split(ProvName,";")[1])) Then crTeal Else crNoColor)
Else crNoColor

But it's not working as expected. For example, the list has A, D, E so NO Surg1 values should be highlighted, ALL Surg2 values should be highlighted, and All Surg3 values should be highlighted. However, in all columns the first value is not highlighted and last 2 are:

Surg 1             Surg2            Surg3
A                  B                C
[COLOR=green]A[/color]                  [COLOR=green]B[/color]                [COLOR=green]C[/color]
[COLOR=green]A[/color]                  [COLOR=green]B[/color]                [COLOR=green]C[/color]

Definitely not what I'm looking for. Additionally, if I have Surg1, Surg2, or Surg3 values but the list is empty, none of the values get highlighted when they ALL should.

Thanks for keeping at this with me Ian.

 

[IanWaterman]

Try listing out contents of ProvName alongside each row. At least then you might see why its behaving as it

Formula like
@display ProveName

WhilePrintingRecords;
StringVar ProvName;

Ian

 

[elsenorjose]

Thanks Ian, I did that and for the first record that is not highlighted, the formula displays a blank. The rest of the values are the first value in the comparison list. So it looks like the conditional formatting formula that looks at the list is only looking at the first value in the list as opposed to the entire list. Is there a way to change this behavior?

 

[lbass]

What you need to do is to create multiple subreports for the group header section (and linked on the group header field). One would collect the values of the Prov Name, as follows:

//sub1 detail section formula:
whileprintingrecords;
shared stringvar x;
if not({table.provname} in x) then
x := x + {table.provname}+",";

//sub 1 report footer formula:
whileprintingrecords;
shared stringvar x;
left(x,len(x)-1)

Then for each column you want to compare, set up another subreport:

//sub 2 detail:
whileprintingrecords;
shared stringvar y;
if not({table.anesth1} in y) then
y := y + {table.anesth1}+",";

//sub 2 report footer:
whileprintingrecords;
shared stringvar y;
left(y, len-1)

In the main report, create a reset formula for the group footer:

whileprintingrecords;
shared stringvar x := "";
shared stringvar y := "";
booleanvar z := false;

Then use a color formula like this to format:

whileprintingrecords;
shared stringvar x;
shared stringvar y;
booleanvar z;
stringvar array y2 := split(y,",");
numbervar i;
numbervar j := ubound(y2);
redim preserve y2[j];
for i := 1 to j-1 do(
if y2 in x then
z := true else
z := z
);
if z = true then
crteal else
crnocolor

-LB

 

[elsenorjose]

Thanks LB. For clarification, I have different ProvName fields; 5 are the for the 3 Surg and 2 Anesth columns and 1 is the {CLARITY_SER_TDL_BILLPROV.PROV_NAME}. It is the latter that I am 'searching' to see if any of the Surg or Anesth values appear in that column. When I created the 1st sub as you described, I am only getting 1 value from the {CLARITY_SER_TDL_BILLPROV.PROV_NAME} column and a partially truncated 2nd value. Something like:

Jones, Albert B.; Smi

Where 'Smi' is the truncated value for 'Smith, David'.

Any thoughts?

Thank you.

 

[elsenorjose]

Ok, so MAJOR apologies for the brain fade. The field wasn't truncating, it was too short and I was just not seeing the entire data. Is my face red!