Conditional Printing

[smatthews]

I have two tables. Table_1 has one record for every person. Table_2 could have one to six records or no records at all for a person in Table_1. The key is an ID field and a TYPE field. If a person has a Type_1 record I want to print out the data in that record. If the person has no Type_1 record then I want to print out a blurb stating "we do not have this info on file." I want to do this for all six Types. Any help will be greatly appreciated. Thanks.

 

[lbass]

Link from Table 1 to Table 2 with a left join using the ID field. Add {Table1.ID} field to the details section, and then create a formula {@Type1} and add it to the details section:

if isnull({Table2.ID}) then "We do not have this info on file." else {Table2.Type_1}

Substitute your own specific fieldnames and if {Table2.Type_1} is a number then substitute: totext({Table2.Type_1})

-LB

 

[smatthews]

Thanks. I am partially there but, I need to specify the six different types. This way if any of the types exist then it prints out the info or "we do not have this info on file." . I need six different lines. Thanks again for your help.

 

[smatthews]

I also have duplicate report pages For example, if a person has 3 different types in the second table then I have three pages for this person.
Thanks for your help.

 

[lbass]

Are the six types just instances of one field, or are they six different fields?

-LB

 

[smatthews]

Six different type codes for the same field.

 

[lbass]

Try this:

First group on {table.personID} and drag the groupname to the group footer. Then insert six Group1 footers sections below what is now Group#1a to create Group#1b - Group#1g.

Now create 8 formulas, starting with {@resetvar}, to be placed in the group 1 header:

whileprintingrecords;
stringvar Type1 := "";
stringvar Type2 := "";
stringvar Type3 := "";
stringvar Type4 := "";
stringvar Type5 := "";
stringvar Type6 := "";

Second formula {@detailvar} to be placed in details:

whileprintingrecords;
stringvar Type1;
stringvar Type2;
stringvar Type3;
stringvar Type4;
stringvar Type5;
stringvar Type6;

Type1 := if {table2.type} = 1 then {table2.type}&" "&{table2.relateddata}&" "&{table2.otherdata} & Type1 else "Type1 - No Info";

Type2 := if {table2.type} = 2 then {table2.type}&" "&{table2.relateddata}&" "&{table2.otherdata} & Type2 else "Type2 - No Info";

Type3 := if {table2.type} = 3 then {table2.type}&" "&{table2.relateddata}&" "&{table2.otherdata} & Type3 else "Type3 - No Info";

Type4 := if {table2.type} = 4 then {table2.type}&" "&{table2.relateddata}&" "&{table2.otherdata} & Type4 else "Type4 - No Info";

Type5 := if {table2.type} = 5 then {table2.type}&" "&{table2.relateddata}&" "&{table2.otherdata} & Type5 else "Type5 - No Info";

Type6 := if {table2.type} = 6 then {table2.type}&" "&{table2.relateddata}&" "&{table2.otherdata} & Type6 else "Type6 - No Info";

Third through eighth formulas:

Call this {@displaytype1} and put it in groupfooter #1b:

whileprintingrecords;
stringvar Type1;
Type1;

Call this {@displaytype2} and put it in groupfooter #1c:

whileprintingrecords;
stringvar Type2;
Type2;

Call this {@displaytype3} and put it in groupfooter #1d:

whileprintingrecords;
stringvar Type3;
Type3;

Call this {@displaytype4} and put it in groupfooter #1e:

whileprintingrecords;
stringvar Type4;
Type4;

Call this {@displaytype5} and put it in groupfooter #1f:

whileprintingrecords;
stringvar Type5;
Type5;

Call this {@displaytype6} and put it in groupfooter #1g:

whileprintingrecords;
stringvar Type6;
Type6;

Then suppress the details section.

In {@detailvar} the formula concatenates data that you want displayed for the particular type. You can substitute your own data fields for {table2.relateddata} and {table2.otherdata}. I think you have to be careful not to exceed 254 characters in the string if you are using a version lower than CR 9.0.

There are much simpler methods if you don't need to display messages to substitute for "empty lines". Please note that in my formulas I used "No Info" to be brief--you can substitute your longer message.

I did test this out, and it worked for me, but let me know if you run into any problems.

-LB

 

[smatthews]

LB, Thanks for all your efforts and it does work exactly how you said it would. I still have one problem. I think it may be related to the linking of the tables. I am using a left outer join but I still get no group footer info for individuals who do not have any records in table 2. Also, for however many records a person does have in table 2 I am getting that many pages for one person. Thanks again for all your help.

 

[lbass]

The left join is from Table1.PersonID to Table2.PersonID, right? And you are grouping on Table1.PersonID--NOT Table2.PersonID, right? Please confirm. You must have the left join for this to work. Did you copy my formulas exactly, but substituting your own field names? If you made any changes, please post the formulas here.

It sounds like you're getting one page per record. Go to format section and check details (and your other sections, too) and make sure you do not have "new page before" or "new page after" checked. You also might check the design canvas and make sure that each section is sized vertically just to fit your fields.

-LB

 

[smatthews]

The join and the grouping are correct according to your specifications. I did copy your formulas but just changed the field names. I made sure there were no new page before or new page after checks. There was one I unchecked it. I checked the size everything looks o.k. here too. I am still getting nothing if the person has no records in table 2 and I am getting multiple pages if the person has more than one type. Thanks for all your help and for hanging in there for me. Much appreciated.

 

[smatthews]

I am down to only one problem. If someone has no records then I don't get anything. Everything else is fine. Any suggestions? Thanks again.

 

[lbass]

I didn't account for the nulls in the detail formula, but haven't quite figured out how to do that without interfering with the variable, despite trying for some time. I'll keep trying, but maybe someone else can jump in to the rescue.

I wonder whether a separate formula to create mock results for nulls would work, like:

If isnull({Table2.type}) then "Type 1 - No Info" + chr(13)+"Type 2 - No Info" + chr(13) + "Type 3 - No Info" +chr(13)+"Type 4 - No Info" + chr(13) + "Type 5 - No Info" +chr(13)+"Type 6 - No Info"

The chr(13) create "carriage returns." You'd have to make sure "can grow" was checked on the field.

-LB

 

[lbass]

How about an entirely different approach--I'm pretty sure this will work and wouldn't be too hard to implement. So, starting from scratch (sorry!):

1-Create a formula that concatenates the data fields you want to show for types that are not null--we'll call it {@detailinfo}, e.g.,:

{Table2.relatedinfo}&" "&{Table2.otherinfo}&" "&{Table2.quant}

2-Group on {Table1.PersonID} and then create six running totals, using the running total editor, one for each Type. For example: for {#Type1} select {@detailinfo}, and choose Nth Most Frequent, with N = 1, as your summary, and then select evaluate based on a formula: {Table2.type} = 1
Reset the running total on change of Group 1 (Table1.PersonID). Create the remaining five running totals using {@detailinfo} in each one and changing only the running total name and the evaluation formula to correspond to the type number.

3-Place a text box containing "Type n: " in each of six group footer sections, where n is a number from 1 to 6, and then add the corresponding running total to each group footer section, next to the box.

4-Create six formulas like {@nullTypen}, where n varies from 1 to 6:

if isnull({#Typen} or if {#Typen} = "" then "No Info"//where the {#field} is your running total for that type number.

Place this formula right on top of the corresponding running total in each of the six sections.

Suppress all but the group 1 header, which perhaps contains customer name and ID and the six footer sections for a display that should look like this:

Dermot Mulroney
Type 1: No Info
Type 2: Shirt Red 3
Type 3: Belt Brown 1
Type 4: No Info
Type 5: Shoes Yellow 2
Type 6: No Info

Cameron Diaz:
Type 1: No Info
Type 2: No Info
Type 3: No Info
Type 4: No Info
Type 5: No Info
Type 6: No Info

I'm pretty sure that Cameron Diaz will show up, but who knows what the famous will do, so please let me know--I can't totally test it, so I won't know unless you tell me. Thanks!

-LB

 

[smatthews]

I am only up to step 3. After adding the running totals, if a person has at least one type, it give me the data for that type again for all other types. That person may or may not have addtional types. The result is still the same, only the one type's data prints out.

For example this person only has a Type_1 record however, the Type_1 data prints out on all other types.


Type_1 PA 123456
Type_2 PA 123456
Type_3 PA 123456


Any suggestions?

 

[lbass]

Please double check each running total. Choose the formula for the concatenated field and use as the summary "Nth Most Frequent" with N = 1 (Note: Use N=1 for each running total)

For the evaluation section, make sure you have used the evaluation formula: {Table2.Type} = 1

And also make sure you have it reset on change of group 1 (PersonID). This is probably why you are getting the results you see--because the results for a previous customer are displaying.

The running totals need to be placed in the group 1b - g footer sections--{#Type1} in group #1b footer, {#Type2} in group #1c footer, etc.

-LB

 

[smatthews]

I double checked and everthing looks correct.

In the running total you have

For the evaluation section, make sure you have used the evaluation formula: {Table2.Type} = 1
The is a text field. Could that be the problem?
But, for the evaluation when I use {Table2.Type} = 'abc' it seems to work the first. However, the second {Table2.Type}= 'def' repeats the values of the first type.

Thanks again for the colossal amount of help you have given me.

 

[lbass]

Where do you have the running totals placed? Since they are unrelated to each other, they cannot be picking up each other's values, and since you are placing them only in a group footer, each one will appear only once.

Also, I am using {table2.type} = 1 to represent the first of your six type fields--so what are the type values? Are you saying that type "1" is really "abc"? If so, then the formula has to = "abc" not 1. And if type "2" = "def" then your evaluation formula will be: {table2.type} = "def"

-LB

 

[smatthews]

I did have the running total formulas correct as I suspected because the type_1 was printing. So, yes, I did change {table2.type} = 1 to {table2.type} = 'abc'.

Running totals are placed in the group footer #1b:, #1c:,#1d:, etc.

Could the fact that I have version 7 be a problem?

 

[lbass]

I'm not familiar with 7.0. I was assuming you were using the running total editor, though, not manual running totals. Am I right? And do you have the running total resetting on change of PersonID? I'm at a loss really--this tests out here at my end.

-LB

 

[smatthews]

LB
Yes, you are right about using running total editor.
But, I think some of my issues are due to Version 7 so I orderd 8.5 today. Can't go to 9 yet, hasn't been tested with the database software I am currently using.

Thanks again for all your help.