Trying to use a “Calculated Value” as the “Default value” in a choice column

Question

I am creating a drop-down choice column. For the default, I need to use a value that is based off of a value from another table. Here's the challenge.

In table A, I have a list of organizations. I have a column called "Needs Stuff", it's a one line text field.

In table A, I also created a calculated column called "nsCalc", using the formula: =IF([Needs Stuff]="","N/A","Pending"). Works perfectly.

In table B, I created a lookup from table A and added the columns "A:Needs Stuff" and "A:nsCalc". They show up correctly when viewing table B.

My challange is a drop-down choice column in table B, using a Calculated Value as the Default value using "A:nsCalc":

• Value from A:nsCalc (default)
• N/A
• Pending
• Approved

So far I am failing. There must be a way, but I am not finding it. Any ideas?

Using SharePoint on O365.

Answer 1

You can't do it like this because Lookups are internally based on reference-ids and not the values.

As Formulas can not expand these complex types (same applies to: Multiple Select, [Me] and Person Columns) you can not use them in Calculated Columns

Your workaround is to use Workflows to read the Complex type value and write it to a basic Text column, and then do your magic