Changing color of header from the footer 1

[unborn]

I have a group that pulls the Doctors names and under that group it pulls the Forms that they are missing. This was done buy a formula that lbass helped me create. Well the documents report at the group footer of the documents what was missing if anything was. I am trying to have the Group header turn white so it looks like nothing is there and it doesnt print the Doctors name if they were missing no docs. When I try to do this if the doctor has no missing documents the NEXT record's dr name is missing. Is what i am trying to do possible?

DR Name Header - Gathers docs names
Doc Header - This gathers docs they have and puts in array
Detail - hidden
Doc Footer - Hidden
DR Name Footer - This checks the names of the docs they have to the names of the docs they should have and then returns the missing docs. (at this point if 0 docs missing I want it to change the DR Name Header field white.)



Running in circles is what I do best!

 

[lbass]

If I remember correctly, there were six documents. So if you only want to display those with missing docs, go to report->selection formula->GROUP and enter:

distinctcount({table.documents},{table.doctor}) < 6

-LB

 

[unborn]

Ok, If only i had made it that easy :/ I had to modify your code to fit the departments needs.

The doctors have 2 statuses, "Active" has 5 documents they need, and "Courtesy" has only 1 document they need but can have up to all 5 of them. I have modified your code so it can decide what documents to look for depending on status.

I noticed it is not working as expected because of this. Is there any suggest you have? Maybe I should have it check to see if it has the file specifically? Im going to see if i can do that. I appreciate your help you rock LB!

Running in circles is what I do best!

 

[lbass]

Didn't you then limit the documents in the record selection formula? As in:

(
{table.status} = "Active" and
{table.docname} in ["doc1","doc2","doc3","doc4","doc5"]
) or
(
{table.status} = "Courtesy" and
{table.docname} = "doc6"
)

If the document names are unique, then my first post above should still work. If you are saying the code in your other post no longer works, then please return to that thread and explain the changes in the report structure and show the code you are currently using which you are saying doesn't quite work now.

-LB

 

[unborn]

Ok using,

if {@Answer} = 1 then
distinctcount({RV_Document.DocumentName},{@PractSort}) < 5
else if {@Answer} = 3 then
distinctcount({RV_Document.DocumentName},{@PractSort}) < 5
else
{RV_Document.DocumentName} <> "Privilege Form";

I have been able to get it to exclude them if all they have is their Privilege Form. But if they have more forms then the privilege form it post that they are missing the form even though they are not missing it.

Running in circles is what I do best!

 

[lbass]

I don't understand what you are doing. What I showed you is a record selection formula, NOT for the group selection. How do answer 1 and 3 relate to the statuses? I don't know what privilege form is either.

-LB

 

[unborn]

Im sorry LB I am still new and trying to grasp all the different location of this application.

Active is represented as a 1 or a 3, Courtesy is represented as a 2. I was using the above formula in the Group selection.

My current record select is:
{RV_Practitioner_Facilities.Current_status} = "Active" and
{RV_Practitioner_Facilities.Status_category} = {?PracCategory} and
{RV_Practitioner_Facilities.FacCode} = "SJ" and
{RV_Document.DocumentName} in ["State License, FL", "Board Specialties", "Board Approval Letter", "DEA Certificate", "Privilege Form"] and
{RV_Practitioner_Images.ContentType} in ["image/jpeg", "image/tiff"] and
{@Answer} = {?Status}

{@Answer} is the number that decided (Active or Courtsey) and {?Status} is my parameter to decide which to show (1 or the other or both).

So in this section:
and
{RV_Document.DocumentName} in ["State License, FL", "Board Specialties", "Board Approval Letter", "DEA Certificate", "Privilege Form"] and

I should add my ifs? like..

{RV_Practitioner_Facilities.Current_status} = "Active" and
{RV_Practitioner_Facilities.Status_category} = {?PracCategory} and
{RV_Practitioner_Facilities.FacCode} = "SJ" and
IF {@Answer} = 1 then
{RV_Document.DocumentName} in ["State License, FL", "Board Specialties", "Board Approval Letter", "DEA Certificate", "Privilege Form"]
else IF {@Answer} = 3 then
{RV_Document.DocumentName} in ["State License, FL", "Board Specialties", "Board Approval Letter", "DEA Certificate", "Privilege Form"]
else
{RV_Document.DocumentName} in ["Privilege Form"];
and
{RV_Practitioner_Images.ContentType} in ["image/jpeg", "image/tiff"] and
{@Answer} = {?Status}



This of course doesnt work because I am not quite sure how to implement this into the code. Also isnt there an easier way instead of me using 2 ifs that I can use an OR statement? When I try i get an error.. im mainly referring to the code below..

whileprintingrecords;
if {@Answer}= 1 then
stringvar array x := ["Privilege Form","Board Approval Letter","Board Specialties","DEA Certificate"]
else if {@Answer} = 3 then
stringvar array x := ["Privilege Form","Board Approval Letter","Board Specialties","DEA Certificate"]
else
stringvar array x := ["Privilege Form"];

numbervar i;
numbervar j := ubound(x);
stringvar y := "";
stringvar z;
for i := 1 to j do(
if instr(z,x) = 0 then
y := y + x + chr(13)
);
y;

I really appreciate you working with me on this.




Running in circles is what I do best!

 

[unborn]

Ok awesome looking at your other example i noticed you used ( ) so i tried it out and it works..

{RV_Practitioner_Facilities.Current_status} = "Active" and
{RV_Practitioner_Facilities.Status_category} = {?PracCategory} and
{RV_Practitioner_Facilities.FacCode} = "SJ" and
(IF {@Answer} = 1 then
{RV_Document.DocumentName} in ["State License, FL", "Board Specialties", "Board Approval Letter", "DEA Certificate", "Privilege Form"]
else IF {@Answer} = 3 then
{RV_Document.DocumentName} in ["State License, FL", "Board Specialties", "Board Approval Letter", "DEA Certificate", "Privilege Form"]
else
{RV_Document.DocumentName} in ["Privilege Form"])
and
{RV_Practitioner_Images.ContentType} in ["image/jpeg", "image/tiff"] and
{@Answer} = {?Status}

Now if they dont have a Privilege form... is it going to still pull the records is my next question. I noticed when i run the report every record has the form which makes me wounder if its not pulling the DR if he doesnt have one. I need it to pull the doctor as long as he meets all other requirements and just report back what forms hes actually missing. I hope I am not annoying you to much I am learning so much right now working with you im pretty excited.

Running in circles is what I do best!

 

[unborn]

Ok my assumption, The reason the missing docs actually works is because there is more that 1 file its looking for. So its looking for 5 forms and it assumes everyone is going to have atleast 1 of the 5 forms... now that I think about it I should probably take the form "documentname" selection out so it will load all the doctors information regardless of their forms and then I will get a more real impression of what is actually missing.

LB, I believe I have taken the wrong path.. you seem extremely knowledgeable.. how would you suggest me going about this? I feel the select should remove the doc selection but at that point how do I further remove the names that arent missing anything? Maybe I should just leave blank names in there? Then they will atleast KNOW that the doctor was checked an no missing docs were found? .. Maybe I should even add that to the formula and have it return "No missing docs"

my goodness i feel my brain wrinkling up as i type!

Running in circles is what I do best!

 

[lbass]

I cannot follow this. Please explain in words what you are trying to do. Specifically, indicate what documents you want to check for what statuses. I also don't understand why you are allowing the privilege form in as a default.

It also looks like you are limiting all records to active status and then later expecting to see multiple statuses.

-LB

 

[unborn]

I can definitely see where you would be confused because I am confused on how to describe it let me try again.

Selection:
I have 3 statuses fields that I am actually filtering,RV_Practitioner_Facilities.Current_status and RV_Practitioner_Facilities.Status_category are 2 of them which are working fine and how they should be.. the 3rd one is {@Answer} which is a status also. The {@Answer} is a formula to take a user defined field from the database and convert it to a number of 1, 2 or 3. The 1 and 3 means they are "Active" and 2 means they are "Courtesy".

Active doctors need the following forms: "State License, FL", "Board Specialties", "Board Approval Letter", "DEA Certificate", "Privilege Form".

Courtesy doctors only need the "Privilege Form".

I was defaulting the selection for the Privilege Form in the selection because that was he form I was looking for that was missing. After I looked at the results it dawned on me that of course it is actually only pulling those doctors that have the form which is exactly what I don't want. At that point I also realized that my filter for the other documents was invalid also because if there is a doctor who is missing all of the forms then he would not be pulled for the report at all. I removed the document filter completely so it can check all of the documents the doctors have and check to see if he is missing any of my specified documents with the formula you created.

The {?Status} is a parameter that lets them select "Active" or "Courtesy" or Both of them for a complete report on all documents missing. Currently I don't know how to combine the 1 and 3 together so I actually have 2 actives in my prompt that they have to select in order to get all the actives.

Ok I think that covers the selection part.

Currently I have it pulling all the missing files which is great it is exactly what I need. I just want to hone the report though to not show the names of the doctors who are not missing any reports. Currently I have them displaying the name of the doctor but it says "No Missing Reports" but I would prefer if it is possible to just have them not show up in the report at all. That way as the documents are added they can rerun the report and get an actual idea of how many doctors are left to rescan for.

Definitions:
{RV_Document.DocumentName} - This is where the Form Name is
{?Status} - Prompt for "Active" or "Courtesy"
{@Answer} - 1,2,3 which decides the status
{@PractSort} - Doctors name & Title sorted alpha.
{@ChckDocs} - Compares to list of docs and reports missing
{@Accum} - Gathers the docs that the Doctor has
{@Reset} - Resets the document variable

Groups:
(Group)(Location)(Grouped by) - (Fields in group)
Group1 H {@PractSort} - {@PractSort} & {@Reset}
Group2 H {RV_Document.DoocumentName} - {@Accum}
Group2 F - Nothing
Group1 F - {@ChkDocs}

Not sure what else I can put. I think this pretty much covers it please let me know if more information is needed thank you for being you!

BTW do you mind if I ask what you do professionally? I got into this stuff because I would like to become a crystal report developer so I am currently learning by doing these side project voluntary.


Running in circles is what I do best!

 

[lbass]

Okay, you should build in a null check, and I would check the two statuses separately as follows:

//{@reset} to be placed in the doctor group header:
whileprintingrecords;
stringvar z;
stringvar q;
if not inrepeatedgroupheader then (
z := "";
q := ""
);

//{@accum} to be placed in the document group header or footer:
whileprintingrecords;
stringvar z;
stringvar q;
if {@answer} in [1,3] then (
if isnull({table.docname}) then
z := "" else
z := z + {table.docname}+","
) else
if {@answer} = 2 then (
if isnull({table.docname}) then
q := "" else
q := q + {table.docname}+","
);

//{@chkdocs} to be placed in the doctor group footer:
whileprintingrecords;
stringvar array x := ["State License, FL", "Board Specialties", "Board Approval Letter", "DEA Certificate", "Privilege Form"];
numbervar i;
numbervar j := ubound(x);
stringvar y := "";
stringvar z;
stringvar q;
for i := 1 to j do(
if {@answer} in [1,3] and
instr(z,x) = 0 then
y := y + x + chr(13) else
if {@answer} = 2 and
instr(q,"Privilege Form") = 0 then
y := y + "Privilege Form"
);
y

After placing this last formula in the doctor group footer, right click on it->format field->common tab->check "can grow." Then suppress the detail section. This will show a list in the group footer of the missing documents.

I do think you are correct in NOT selecting based on documents in the selection formula.

-LB

 

[unborn]

Hey LB,

Thanks for the help, unfortunately it is still returning the doctors name even if they are not missing any documents.

Running in circles is what I do best!

 

[lbass]

Create a formula like this {@activedocs}:

if {@answer} in [1,3] and
{table.doc} in ["State License, FL", "Board Specialties", "Board Approval Letter", "DEA Certificate", "Privilege Form"] then
{table.doc} else
{@null}

...where {@null} is a formula created by opening and saving a new formula without entering anything.

Create a second formula {@courtesydocs}:

if {@answer} = 2 and
{table.doc} = "Privilege Form" then
{table.doc} else
{@null}

Then go to report->selection formula->GROUP and enter:

(
{@answer} in [1,3] and
distinctcount({@activedocs},{table.doctorname}) < 5
) or
(
{@answer} = 2 and
distinctcount({@courtesydocs},{table.doctorname}) = 0
)

-LB

 

[unborn]

LB im sure you have heard this plenty of times but..

Y O U ... A R E ... T H E ... M A N!

Thank you so much!!!! I will now go and analyze the code so I can utilize it in future projects!! Thank you so much for your help this is awesome! If I could give you a dozen stars I would!

Running in circles is what I do best!