formula to change multiple fields at once

Question

Hopefully, this is an easy solution.

I have a scenario in my data where if a field is blank I need to swap around different fields (based on parsing results).

So if App field is blank then the value in Country should be moved to Sector and the value in Sector should be moved to App. The Country value should be set to Null.

Do I need to create a new temp field to support this?

Adam

patrick_digan · Accepted Answer

First I would use this formula for App:

IIF(isnull(App),Sector,App)

Then this formula for Sector:

IIF(App=Sector,Country,Sector)

Then this formula for Country:

IIF(Country=Sector,Null(),Country)

This is assuming that App cannot normally equal sector, and country cannot normally equal sector.