suppress part of a formula field??? 1

[piovesan]

I have the following formula field:

if {LND_PR_CLIENT.CLIENTCODE}= "V"
then (maximum({LND_PR_CLIENT.CLIENT_ID}))& Propercase ({LND_PR_CLIENT.CLIENTNAME})

The only problem is, I do want to make sure what displays is the Client name that was input last (has the maximum Client ID), but I don't actually want the Client ID to display......

I can't just put the {LND_PR_CLIENT.CLIENTCODE}= "V" in the report selection formula either because of data issues in the tables..........

Help please?

 

[fisheromacse]

I did not test the below, but think it will work...or at least be sorta close. (it feels like the 2nd Monday of the week for me, so be kind to my mistakes.)

create a SQL expression for {%MaxClientID}:
(
Select max("CLIENT_ID")
from LND_PR_CLIENT
where "LND_PR_CLIENT"."CLIENTCODE"='V'
)

for your formula use:
IF ({LND_PR_CLIENT.CLIENT_ID} = {%MaxClientID}
then Propercase ({LND_PR_CLIENT.CLIENTNAME})

 

[Turkbear]

Hi,
Assuming that your posted formula does give you the name you want, then adding something like this change and a new formula for the display should work:
@NameOnly

if {LND_PR_CLIENT.CLIENTCODE}= "V" 
then (maximum({LND_PR_CLIENT.CLIENT_ID}))& "*" & Propercase ({LND_PR_CLIENT.CLIENTNAME})

@DisplayName

if {LND_PR_CLIENT.CLIENTCODE}= "V" 
Then
Right(@NameOnly,Instr(@NameOnly,"*") +1 )
Else
""

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I posted the following in response to your other post:

Your logic is incorrect here. This formula says if the client code for the current record = "V" then show me the last record for the entire report. I think you want to show the name for the most recent record where the client code = "V":

if {LND_PR_CLIENT.CLIENTCODE}= "V" then
{LND_PR_CLIENT.CLIENT_ID} & Propercase {LND_PR_CLIENT.CLIENTNAME}

Right click on and insert a maximum on this formula at the group or report level.

-LB

 

[piovesan]

lbass....... how do I do that?? How do I insert a maximum on the formula??

 

[lbass]

Place the summary in the detail section->right click on it->insert->summary->maximum->group or report level.

-LB

 

[piovesan]

Thank you so much you saved me again lbass!!

 

[piovesan]

actually, after testing this, what I am actually getting on the reports are the MAX alphabetically on the Clientname instead of the Client ID like I wanted.......... I actually don't even want to "see" the Client ID on the report, but if there are two or more clients related to this record, I do want the report to display the Client name that is related to the maximum (or last) record added. So, if the first client name was "Apple" and they have client id 1, and the second client name was "Zebra" and they have client id 2, I want Zebra to display....... the way I have it currenly set up, Apple will display.....
if client 1 was Zebra, and client 2 was apple, then I am still seeing Apple in the report..... which in this case would be what I want, but it is only a coincidence that Apple has the greater client id............

 

[lbass]

The formula I suggested would not do that, so please show the actual formula you created.

-LB