if field contains...

[Ofina]

I want to have an if statement for a formula field based on the contents of an entire field.

For instance, if field A for this order has "Won" anywhere down the list, then "Won". If it has "declined" all the way down then "Declined".

The thing is that I want this formula field in the page header with one value, but based on the contents of the individual lines.

Does that make any sense?

 

[IanWaterman]

Group report by order number

Create a formula

//@status
If uppercase(fieldA) like '*WON*' then 'Won' else
If uppercase(fieldA) like '*DECLINED*' then 'Declined' else
else ' '

Add a maximum summary on this formula to group header

Changing fieldA to uppercase overcomes possible errors with users inputting mixed case strings.

Ian

 

[Ofina]

Don't need to worry about the upper case thing. The users don't input this data.

Can you explain the maximum summary part a bit more?

 

[lbass]

I think you need two formulas:

//{@Won}:
if {table.fieldA} = "Won" then 1

//{@Declined}:
if {table.fieldA} = "Declined" then 1

Then assuming you are evaluating this per a group on order, create a formula like this:

if sum({@Won},{table.order}) > 0 then
"Won" else
if sum({@Declined},{table.order}) = count({table.order},{table.order}) then
"Declined"

Assuming you have a new page before after set on the group footer, then you could place this formula in the page header.

-LB

 

[Ofina]

Well, I just got it...I think. I didn't do it the ways suggested, but they both gave me ideas.

I formed a ranking of the values that could be on the lines and then in the summary chose the minimum of them. I think it'll work.