Ignore missing column in formula tool

[rgusaas]

Using the formula tool, I want to create a new column that concatenates up to four columns if they exist in the source file.

New Column Name: CAGM_Key

Formula example: [Carrier ID] + "_" + [Account ID] + "_" + [Group ID] + "_" + [Member ID] (If all 4 columns exist)

Alternate formula:   [Account ID] + "_"  + [Member ID] (If NOT ALL 4 columns exist) 

Alternate formula: [Member ID] (if only Member ID is found)

I need to join two data sources together using the CAGM_Key field which will contain only the columns that exist. 

The formula tool errors out when a column is not found. How do I determine in advance if a column does not exist?

I have used the Field Info tool to identify what's missing but don't see a way to create then run an expression on the fly.

btw - This could be solved simply by a function within the formula tool like "IfFieldFound"

Suggestions?

[MarqueeCrew · Accepted solution]

@rgusaas,

I would suggest a slightly different approach to the problem.  I get that you don't know if you can trust that incoming data will contain a REQUIRED element.  You want to be flexible.  I would use a 'templated' input as a UNION to the incoming data where the template contains all of the REQUIRED elements.  After your calculations, you can filter/select remove rows or columns that you don't need.

Now you will always have the 4 columns and your formula will work with minor tweaks

IF IsNull([Account ID]) or IsNull([Group ID]) AND 
   (!IsNull([Account ID]) AND !IsNull([Member ID]))
   THEN [Account ID] + "_" + [Member ID]
ELSEIF
   ISNull([Carrier ID])
   THEN [Member ID]
ELSE
   [Carrier ID]+"_"+[Account ID]+"_"+[Group ID]+"_"+[Member ID]
ENDIF

Cheers,

Mark

[rgusaas]

Thanks Mark.

 I interpret the solution to be to add a Text Input tool containing the four field named columns, then use a union tool to append the columns to the end of the source file. If the columns do not exist in the source they will be appended. Otherwise they are dropped. 

That worked.

Thank you!