Complex Formula help needed 1

[HookEmHorns]

My client sent me a form they want immunization dates to pull onto (last 5 per Category). Earlier today, LBass helped me set up the below mentioned formula that works fantastic. I currently have 5 formula's all similiar with the exception of what pulls to {cusASIISC_ImmunizationsGet.Sequence}.

My 5 formulas @DTPDate1, @DTPDate2, @DTPDate3, @DTPDate4 and @DTPDate5

@DTPDate1 = 

if {cusASIISC_ImmunizationsGet.Sequence} = 0 and
(
{cusASIISC_ImmunizationsGet.Category} = "Diphtheria, Tetanus, Pertussis" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90696" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90723" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90698"
) then
{cusASIISC_ImmunizationsGet.ImmunDte}

@DTPDate2 = 

if {cusASIISC_ImmunizationsGet.Sequence} = 1 and
(
{cusASIISC_ImmunizationsGet.Category} = "Diphtheria, Tetanus, Pertussis" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90696" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90723" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90698"
) then
{cusASIISC_ImmunizationsGet.ImmunDte}

@DTPDate3 = 

if {cusASIISC_ImmunizationsGet.Sequence} = 2 and
(
{cusASIISC_ImmunizationsGet.Category} = "Diphtheria, Tetanus, Pertussis" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90696" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90723" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90698"
) then
{cusASIISC_ImmunizationsGet.ImmunDte}

@DTPDate4 = 

if {cusASIISC_ImmunizationsGet.Sequence} = 3 and
(
{cusASIISC_ImmunizationsGet.Category} = "Diphtheria, Tetanus, Pertussis" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90696" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90723" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90698"
) then
{cusASIISC_ImmunizationsGet.ImmunDte}

@DTPDate5 = 

if {cusASIISC_ImmunizationsGet.Sequence} = 4 and
(
{cusASIISC_ImmunizationsGet.Category} = "Diphtheria, Tetanus, Pertussis" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90696" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90723" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90698"
) then
{cusASIISC_ImmunizationsGet.ImmunDte}

In my form, I have #1, #2, #3, #4 and #5 listed left to right. @DTPDate1 going to #1, @DTPDate2 going to #2 and so on to #5 gettin @DTPDate5.

How I am telling which one is the oldest is based on this field - {cusASIISC_ImmunizationsGet.Sequence}. The most recent one gets the 0. The second oldest = 1, then = 2 and so on <see examples above>.

My client tells me I have it backwards and instead of having the most recent date in #1, they want the oldest one in #1. I thought if I just moved my formulas on my page around it would fix my issue and I could move on.

How it is now:
#1 = @DTPDate1, #2 = @DTPDate2, #3 = @DTPDate3, #4 = @DTPDate4 and #5 = @DTPDate5

How I thought I could switch it to fix the issue:
#1 = @DTPDate5, #2 = @DTPDate4, #3 = @DTPDate3, #4 = @DTPDate2 and #5 = @DTPDate1

Issue I am finding is this. Lets say I have five spaces to fill in a date but the patient only had 2 injections on 2 distinct dates. If I moved them around, space #5 and space #4 would be populated with what I want in #1 and #2.

I need a way to check if all the dates have a value and if not move it down the chain. Make sense? Clear as mud! I think if you jot it on paper and visually see what I am saying it will make sense. I think. LOL

As always any suggestions or help is appreciated.

 

[lbass]

Change your formula to the following:

if (
{cusASIISC_ImmunizationsGet.Category} = "Diphtheria, Tetanus, Pertussis" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90696" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90723" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90698"
) then
{cusASIISC_ImmunizationsGet.ImmunDte} else
date(9999,9,9)

Then create five formulas like this:

//{@earliest} for the #1 spot:
nthsmallest(1,{@yourformula},{table.category})

//{@2ndearliest} for the #2 spot:
nthsmallest(2,{@yourformula},{table.category})

//etc.

Then format each formula to conditionally suppress with this formula:

currentfieldvalue = date(9999,9,9)

-LB

 

[HookEmHorns]

LBass,

This is painfully close. When I created the 5 formulas, it made me create a group on Category, which in turn now prints a page for each category. Is it possible to keep it all on one page and not do this?

 

[lbass]

You wanted the five most recent based on category, right?

You shouldn't get a separate page per category unless you set new page after on the group footer or possibly if you checked "keep group together" in the group expert.

-LB

 

[HookEmHorns]

lbass -

You are correct, I want the five most recent based on category.

This is what I have:

Report Header
Page Header
Group Header #1: Category - A
Group Header #2: Last - A
Details
Group Footer #2: Last - A
Group Footer #1: Category - A
Page Footer 
Report Footer

Currently, my form is sitting on the Group Footer #1. Everything else is set to suppress.

On the report header, I have the following checked:
Free Form Placement
Suppress (Drill Down OK)
New Page Before (greyed out)
Keep Together

On the Page header, I have the following checked:
Free Form Placement
Suppress (Drill Down OK)
Keep Together (greyed out)

On the Group Header # 1, I have the following checked:
Free Form Placement
Suppress (Drill Down OK)
Keep Together

On the Group Header # 2, I have the following checked:
Free Form Placement
Suppress (Drill Down OK)
Keep Together

On the Details, I have the following checked:
Free Form Placement
Suppress (Drill Down OK)
Keep Together

On the Group Footer # 2, I have the following checked:
Free Form Placement
Suppress (Drill Down OK)
Keep Together

On the Group Footer # 1, I have the following checked:
Free Form Placement
Keep Together

On the Page Footer, I have the following checked:
Free Form Placement
Suppress (Drill Down OK)
Print at Bottom of Page (greyed out)
New Page After (greyed out)
Keep Together (greyed out)
Keep Together

On the Report Footer, I have the following checked:
Free Form Placement
Suppress (Drill Down OK)
New Page After (greyed out)
Keep Together

I went into the group expert and unchecked the "Keep Group Together".

With the current set-up, its giving me a page for each Category and I've tried darn near everything I can think of to avoid this. I need it all on one page, versus one for each category.

 

[lbass]

Go to report->group expert->options and make sure you do not have "keep group together" checked.

-LB

 

[HookEmHorns]

lbass,

"keep group together" is not checked on either one.

 

[HookEmHorns]

lbass -

Should I move it off this group footer or does that not matter? I have triple checked the settings and confirm "keep group together" is not checked. I am stumped.

 

[HookEmHorns]

lbass,

I changed my concept on some of this, put I just wanted to take a minute to say how thankful I am that you took some time out for me today. You truly save people and you educate them. Your a savior! Thank you! Thank you!