Formula to select a value in a record based on highest ID field 1

[Trogg]

Using Crystal 2011

Thanks for your assistance in advance...
I am trying to select the height based on the highest encounter id for the account. The data (simplified for example)looks like this:

Account[tab]Encounter_ID[tab]Ht_In
116284[tab][tab][tab]2[tab][tab][tab][tab]60
116284[tab][tab][tab]5[tab][tab][tab][tab]57
116284[tab][tab][tab]10[tab][tab][tab][tab]64

I need to be able to select the Ht_In from the highest Encounter_ID for the account. In the example above that would be 64. I have a simple formula field to convert the inches to Ft/In (@Height - Ht_In/12). Right now it just retrieves the Ht_in from the first encounter. If I make a group formula using the Maximum function it works (Ie. Encounter_id = Maximum(Encounter_id)but if there is no encounter_id I lose the record. If there is no encounter then I just want the height to be blank and not lose the whole record. Hope this is clear... thanks!

 

[fisheromacse]

If at all possible, I would group descending on encounter and put the info in the group header.

or you could probably change your formula to be:

IF is null({table.Encounter_ID}) then ""
else
IF Encounter_id = Maximum(Encounter_id) then {@Height}

 

[fisheromacse]

looking at what i just posted, i am not certain. i need more coffee and then if all goes well, will take another look and repost if i see something glaringly out of place.

 

[IanWaterman]

Replace Ht_In with a formula

If isnull(Encounter_ID) then 0 else Ht_In

Use this formula in your max group filter. Format your (@Height - Ht_In/12) so that zeros are suppressed.

This assumes that data exist and encounter Id is null. If there is no actual record then this will not work.

Ian

 

[Trogg]

Thanks for responding to you both but I can't get either of these to work... Fisher yours gives me an error at the second If after the else.. "A String is Expected Here". Ian you are correct there will be no record in the height table because no entry was made for height but I do not want to not see the rest of the data for the account just because a height was not entered. If I can provide any more info please let me know.

 

[IanWaterman]

As long as the height table is joined using a left join then all other data will be present and my formula should work.

The encounterID field should come from the height table, or replace with whatever field is used to join height table to rest of data.

Ian

 

[Trogg]

Sorry Ian.. I'm sure I am not following correctly. Left join on the height table is correct. I then do a group selection formula like so:

If isnull({t_ptheight.encounter_id})
Then 0
else {@Height}

When I try to save it I get "The result of the selection formula must be a boolean".

I tried (quotes around the 0):

If isnull({t_ptheight.encounter_id})
Then "0"
else {@Height}

...and got "A string is required here." and {@Height}is highlighted.

Sorry for being dense.. thanks for helping... what am I doing wrong?

 

[fisheromacse]

i don't think it should make any difference, but thy this:
f isnull({t_ptheight.encounter_id})=TRUE
Then 0
else {@Height}

 

[pmax9999]

Hi Trogg

Assuming you only want the report to list the records with the highest Encounter_ID or those with a NULL Encounter_ID (and assuming the report has been grouped by Account, add a Group Selection as follows:

(
    NOT(IsNull({Table.Encounter_ID})) and
    {Table.Encounter_ID} = Maximum({Table.Encounter_ID}, {Table.Account})
)
or
(
    IsNull({Table.Encounter_ID})
)

Cheers
Pete

 

[Trogg]

Pete!! You nailed it! Worked like a charm. Now I've discovered I have the same issue with weight... the only difference is the table name. So if I wanted to incorporate weight into this would I just copy this, change the table from height to weight, and append it to this using "and"??

 

[pmax9999]

Hi Trogg

This may well be considerably more complicated.

Probably best if you can give us some sample data (including the table/column names, and and explanation of how the report is grouped) once you have added the weight field to the report, and post the SQL generated by the report so we can see the table relationships.


Regards
Pete.

 

[Trogg]

Thanks Pete! I already tried what I posted above and it worked perfectly sir! Your solution was spot on. Thanks to Ian and Fisher as well for helping with the thought process here. Just for the record I could have added 2 new groups for height and weight based on encounter ID for each, sorted them in descending order to get highest first, supress those groups and put the results in the group 1 header. This solution cuts out the need for more grouping. Thanks again.