7 Replies Latest reply on Sep 2, 2014 11:07 AM by raybaudi

# Multi-step calculation

I have two fields of data that I want to use to populate third field. Field #1 contains a Joint Saluation (Mr. & Mrs.), Field #2 contains single salutation (Ms.). All #2 fields have data, some of the #1 fields are empty because not everyone has a joint salutation.

How do I write a calculation for a third field to copy info from column 1, but if #1 field is empty then it needs to take the info from #2 field.

• ###### 1. Re: Multi-step calculation

denversusan2 wrote:

[…] copy info from column 1, but if  #1 field is empty then it needs to take the info from #2 field […]

The basic way to do that is …

Case (

not IsEmpty ( field1 ) ;

field1 ;

field2

)

… but I'm not sure that the way you store and use these data is optimal.

1 of 1 people found this helpful
• ###### 2. Re: Multi-step calculation

I think this would work

SetField (Field3;If (IsEmpty ( field 1) =0; field1 ;field2)

Felipe

• ###### 3. Re: Multi-step calculation

sicosys wrote:

I think this would work […] IsEmpty ( field 1) =0

It does, but it's needlessly convoluted and unclear; one could ask what a 0 has to do with anything. There are more explicit way to test on Boolean values.

And how do you know there is a script involved?

• ###### 4. Re: Multi-step calculation

Hi erolst!!!

Thanks for the comment.

Just to clarify, i need to say that the "0" tests that field1 si not empty.

I know there are many ways and personal styles to solve different situations..... this is just one of them but not necessarily the best.

Felipe

• ###### 5. Re: Multi-step calculation

Just to note that

if ( isempty(field1) = 0 ; field1 ; field2 )

is the same as

if  ( not isempty(field1) ; field1 ; field2)

is the same as

if ( isempty(field1) ; field2 ; field1 )

Aside from that,  the List() function may help with this sort of problem. It may not be the answer in this case but you can decide.

if you do this:  list( field1; field2 ; field3 ) when field1 = Mr, field2 = Mrs and field3 = Ms

you get this:

Mr

Mrs

Ms

if field2 is empty you get this

Mr

Ms

You can then use substitute( ) to replace the carriage returns and make it readable, eg, Mr & Ms

• ###### 6. Re: Multi-step calculation

Thank you erolst for the suggestion - worked perfectly!