0

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.

enter image description here

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.

enter image description here

enter image description here

enter image description here

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.

enter image description here

I understand I have to somehow show the relationship between "MO" and "Rec" = company 426, but I am not sure how to do so.

This is the flow I currently use to fill in the State and Company "Nickname" columns: enter image description here

Thanks in advance!

  • You are going in the right direction. You need to have a connection - US Entities with either State Abbreviations or US Entity States. You can create a lookup column in US Entities list for this purpose. You can then use all these lookup values in your FLOW on document is uploaded. – harshal gite Nov 25 '19 at 19:41
  • Also, are 'State', 'Company' & 'US Entity' lookups in the library? If yes, then is 'State' coming from State Abbreviations? – harshal gite Nov 26 '19 at 5:36
  • Hi, sorry for the late reply. I will try a few combinations of that to see which works best. Also, the State column in the "US Entity States" list is a lookup, coming from the State Abbreviations list. Everything else is a single line of text. – gjustyna2 Nov 27 '19 at 15:21
  • How would I edit the flow to lookup the values? Is there a "best practice" for this? @harshalgite – gjustyna2 Nov 27 '19 at 16:01
  • State is a lookup column from State Abbreviations right? because you cannot use a lookup column in another lookup. – harshal gite Nov 28 '19 at 4:23
0

I went through your list structures and flow. And without changing your list structure, I have just added 2 actions in your flow and got the ALMOST desired result.

As per your structure, the US Entity (column which stores the company code in the document library) is a lookup column to US Entities list. And, Company Code in the US Entities list. But in SharePoint you cannot display a lookup column of a lookup column in the a list or library. I mean, you cannot show Company Code in your document library directly if your US Entity column is a lookup to US Entities list.

You can show the Title "Montana Real Estate, Co." instead of Company Code which will be a link to that item in the US Entities list. Please check the screenshots below.

enter image description here

Detailed actions: enter image description here

State/Title - here State is internal name of the column in the US Entity States list. So replace with your column's internal name. Follow this for the below action as well.

enter image description here

Expression used is as shown below:

first(body('Get_items_US_Entity_States')?['value'])?['State']['Id']

enter image description here

Here, In my case the lookup column in the document library is US Entity Title (in your case it is Company Code). So while updating the value of that column in the Power Automate (MS Flow), I have used the below expression.

first(body('Get_items_US_Entities')?['value'])?['Id']

The library data will look like this enter image description here

Hope it helps. Please let me know if this solution is fine or you want the Company code to be displayed and linked to the US Entities list. There is a workaround for that but without really creating a lookup field in the library. Please up-vote and mark as answer if it helped.

0

I assume the file name is always in the format of US Abbreviations_Company nickname name_etc. We shall be able to pick up the two piece of string in Flow.

In the US entity states list, is there a column showing the nickname of the company which will be used in the uploaded file name? If now it will be a bit hard as we have no clue to connect Rec to Montana Real Estate. If we have it, things will not be too tough.

First we try to split the uploaded file name into MO and Rec and save them in two variables, then update the State column with MO. Use Rec to query the US entity states list and find corresponding Full name of company and the company code, and update the last two column in your library.

How to use substring in flow

Common Filters for Microsoft Flow: A Reference Chart for Users of all Kinds

  • Yes, the naming convention is: SS_nnnnn_qqqqYY_Income Tax (if that makes any sense). I guess my question is how to make that column within the US entity states list. Would it be a lookup to another column or can I simply create a "single line of text" column and manually type in the appropriate nickname per company code? – gjustyna2 Dec 2 '19 at 15:18
  • I am trying to follow the split that you mentioned, but I do not really understand how to do that. – gjustyna2 Dec 2 '19 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.