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?
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.
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.
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.