formula field dropdown list with existing fields

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a field entitled 'Account Owner' from there, i would like to be able to choose from two other fields (housed in a different table) called 'First Name' and 'Spouse First Name'. Ideally, I'd also like to be able to add two other options "Joint Account" and "Corporation", but they are standalones and don't come from existing fields.

How in the heck would I write this as a formula?

Photo of Lynn

Lynn

  • 0 Points

Posted 3 years ago

  • 0
  • 1
It sounds like you already have a drop down for the First Name and the Spouse First name and you have or could have a field to choose Joint Account and Corporation as other choices in a different field.

You can just list them together like
List(", ", [First Name], Spouse First Name], [Joint / Corporation])

Presumably the user knows not to make choices that do not make sense, so only 1 would be chosen.
Photo of Lynn

Lynn

  • 0 Points
I do not have a drop down for the first name or spouse first name. I have them housed in separate fields currently.  I do not have Joint Account or corporation presently in any fields, so I guess I'd like to combine 2 existing fields and 2 new items into one dropdown list.
You cannot combine difference sources into one drop down list.  You could have a drop down for
Corporation
Joint
Name, and then if the last optn was presented, them open up a drop down list of the records in the other table.  There may be alternatives on how to best do that as it appears that you are choosing records from a another table as your drop down, but sometimes you want 1 field and sometimes you want the spouse name.

Can you tell me the relationship you see in these tables. Can a name be applied to multiple records being created? It would be helpful to understand the business situation that you are trying to solve so I can advise the best way to set up the relationships to get your drop down working.
I think that what you need is a Relationship where 1 First Name has many Records  and look up the first name into your record.

The another relationship to choose the spouse name and lookup the spouse name into your record.  The combine them into a a single field using the List formula.  You can contact me directly via the information in my Profile if you want me to have a quick look at your app to give you a hand.