My goal is to have a calculated/or lookup column to display the company number if two columns contain certain information. I do not have access to PowerApps, Microsoft Forms, SharePoint Designer, limited use for Microsoft Access. I do have Microsoft Flow.
What I have is a flow that populates: when a file is uploaded to this document library, the file properties change according to the file name. See screenshot.
I do have lists with State Abbreviations, legal entity names, and entity numbers as shown below.
- State abbreviations list only has a title column with all 50 state abbreviations.
- US Entity States list has the company number listed in the title and a "State" lookup column that is linked to the first list.
- US Entities has a list of all of the (full) legal names of the company as the title, and the company code as a single line of text next to it.
I am trying to use these lists (or possibly edit/create another one if need be) that can help me auto-populate a column in a SharePoint library. For example, if a document is tagged as state "MO" and Nicknamed "Rec", then get the company number for Montana Real Estate, Co. which is 426 as shown below. I added the company number manually, but I would like this to auto-populate.
I understand I have to somehow show the relationship between "MO" and "Rec" = company 426, but I am not sure how to do so.
Thanks in advance!