Look up Field Power Automate

[hello_a_r]

Hello,
I’m new to PowerAutomate and I have managed to work out some simple workflows so far.

I have modified the standard SharePoint form (when I create a new item in my list). I have managed to add a look up for a column on separate list for a “client name” which then allows me to pick that “client name” from a drop down menu.

How do I also automatically assign the unique 3-digit prefix for that client - is that also a look up that is equal to the client name?

In other words. When I select my client name from the drop down menu, I want the 3-digit prefix associated with that client to auto populate in that field.

Any help would by appreciated.

A

Update:

I am trying to auto populate the "Client Prefix Number" field to match whatever the "Client Name" is listed above. Is "Lookup" the correct function?

[#2]

Hi @hello_a_r !

You're on the money there. You will be able to achieve that with a lookup.

Keep in mind I am guessing your list/field names, you could try something like:

LookUp('Client Name List', Title = Dropdown.Selected.Value, Client Prefix Number)

This is essentially saying in my Client Name List, I want you to pull all the titles that equal this dropdown, and return the corresponding Client Prefix Number. 

Hope this helps!

Rachel

[#3]

Hi @rachelcackleberry 

Thank you very much for your suggestion, but may I ask if you could review my attempt below. It doesn't seem to work for me.  Thank you in advance.

This is the SP list connected as a data source.

List Name: TestClient

ID: is the SP generated ID

Client Name: single line of text

Client Number: number

Active: choice (my hope with this (later) is to only populate the "Active" clients in the dropdown)

The SP list name we are adding entries to is called: TestP 

The list above "TestClient" is added to "TestP" as a connected data source

This is the formula in the "Client Name" field (Dropdown) which is pointing towards a column in a SP list data source with the Client Names. 

TestClient.'Client Name'

Note: I did start this with the "LookUp" function but it must have shortened the code to the above automatically.  This results in a drop down list which allows me to select the client name. 

This is where it shows how new I am to all this. I'm having trouble getting your code to work, I think its starting to breakdown at the "Title" and I am guessing it needs to be a value in the "Datacard" perhaps? The next image shows the error with your suggestion.

Thank you for your time Rachel, it is much appreciated. 

Alyne

[#4]

@hello_a_r 

Hi Alyne

Your formula is an exact copy of mine for the dropdown you are referencing. If I'm viewing everything correctly, on the Client Prefix Number formula, change dropdown.selected.value to DataCardValue25.selected.value and it should all work.

If it doesn't double check the column you're referencing is the title column and not one you have created.

Rach 🙂

[#5]

Hi Rachel,

Thanks again for the reply. In the SP list called “TestClient” i did rename the Title column to “Client Name”. Therefore the “Client Prefix Number” column is one I created.

Is this where my problem is. It’s still not working unfortunately. I will keep trying the (auto complete) options it offers to see if any other of those work for me.
A

[#6]

@hello_a_r The name of the column (which you can find if you hover within the list settings) will still be title so that's no issue. Are you able to click into the formula bar and take a screenshot with the error showing?

[#7]

@rachelcackleberry 

Thank you for all your help with this. Here is the screen shot showing the error.

The image below that shows the code in the original drop down we are trying pair up with.

[#8]

Thanks @hello_a_r - That's not showing me the actual error message. If you click into the formula bar so the squiggly lines are showing, then hover it will give you a message. The alternative is to click the stethoscope in the top right corner and navigate to the error message. Unfortunately your screenshot only tells me there's an error, not what the error is.

Thanks!

[#9]

Hi, @rachelcackleberry 

The entire line is underlined red and I cant seem to keep that red lines and the text that appears on hover when I go to take a screen shot. The text on hover says: "The function 'Lookup" has some invalid arguments" 

[#10]

One last ditch idea.... The Client Prefix Number in the formula bar, did it come up with the suggestions? I'm wondering if it should be enclosed in '' - 'Client Prefix Number'?

Also - fun windows tip (if you're a windows user) - if you hover over so you get the text, then use your keyboard and hold down windows/shift/S it will let you select a portion of your screen to capture! Best tool ever 🙂

[#11]

Hi @hello_a_r ,

I was just wondering if there's a reason you're not using the built-in SharePoint lookup column in your TestP list? That is, in the TestP list, make ClientName a lookup column (in SharePoint) to the TestClient list's Title column, and also tick the box in the lookup settings to include the Client Number. That way, when someone enters a new item in the TestP list, they can select the ClientName from a dropdown, and the Client Number will also automatically be populated.

This works whether or not you're using Power Apps to customize the form. That is, if you use the standard SharePoint new item behavior, you'll get that dropdown experience for the Client Name, and the Client Number will be automatically populated also. Or, if you still want to customize the form in Power Apps for some other reason, then the lookups are already done for you. Power Apps would automatically use a formula such as Choices([@TestP].ClientName_x003a_ClientNumber) for the Client Number - but you wouldn't even need to display that on your New Form, since it'll be provided automatically by the SharePoint lookup.

Could this make sense for your scenario...? I apologize if I've missed something about what you're trying to achieve...

Sandy