cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simms7400
Post Prodigy
Post Prodigy

Lookup formula in a Combo box?

 

Hi Folks -

 

I have a form that is used for both change requests and new requests for data elements for our Master Data Management System.   I need to add a ComboBox to the form instead of converting an existing field to a dropdown as there are quite a few values so the end user needs to be able to search.

 

The ComboBox's data source a list of "Indications" called "RD_Indication_List" on SP.

 

My question is, can the ComboBox be inclusive of a Lookup formula?

 

For instance, if an end user is submitting a change request, it means the  "Project Code" field (DataCardValue43.Text)  is populated with a data element and existing in a SP list ("rdInvesments") and also is mapped to an indication using a column "IND_DESC".   THerefore, I want to prepopulate the box with the current mapping, but still allow the end user to make a new selection if need be.

 

However, when I try to use this formula, it fails:

LookUp(rdInvestments, InvestmentId=DataCardValue43.Text, IND_DESC)

c1.png

Here is the layout of the "rdInvestments" table:

 

combo2222.png

 

 

 

20 REPLIES 20

@RandyHayes  Thank you so much!  Yes, we are on the same page!!

 

To clarify, "Project Code" on my form is called "InvestmentId" in table "rdInvestments". 

 

And to answer your questions:

Is this an EditForm for the rdInvestments datasource?  Is the value in the Project Code text box from the rdInvestments datasource?  

 

Yes & Yes!

 

But when I use your formula, it doesn't validate:

combobox.png

 

Thoughts?

@RandyHayes 

 

Furthermore, the "InvestmentId" field is what I'm trying to use to grab the "IND_DESC" value in the table "rdInvesments".

 

And "Project Code" (InvestmentId) is populated based on a selection from a pervious form gallery.

@simms7400 

Sorry, I think I inadvertently gave you a formula before with IND_DESC.Value

Just make it Lookup(RD_Indication_List, Alias=ThisItem.IND_DESC)

Sometimes I get lazy with the auto-suggestions in PowerApps that when I think these things out on the fly I forget.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

@simms7400 

AH...and again, thinking on the fly...I've been referring to the Default property all along...what I meant was the DefaultSelectedItems property.

 

And, set your SelectMuliple to false as well.

 

SET THAT ONE...not the Default one.

Sorry about that.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

@RandyHayes 

 

Hi Randy - I think this may be  lost cause at this point...This isn't working either.

no.png

 

Why would I be using "ThisItem" on the ComboBox?  I still dont see how this would return the value of "IND_DESC" based on the "Project Code" (InvestvestmentId" value of the table "rdInvestments"?  The combo box is already returning (in the dropdown) all values of the "Alias" column in "RD_Indication_List" which is right.

 

but I need to return the "IND_DESC" value in "rdInvestments" based on the value in the "Project Code" (InvestmentId)... Then, if the end user wants to change that, they can using the drop down....

 


Hi Randy - I think this may be  lost cause at this point...This isn't working either.

Not a lost cause...this is very feasable and workable.  I know I use this scenario often.

no.png

 

This is telling me that there is No IND_DESC column on your rdInvestments table.   I see it in your photo, but something is not right about it.  Oh Wait...I believe I'm forgetting about this being a Lookup column?  Is that correct?  what kind of column is IND_DESC in the rdInvestments table?

If it is a Lookup to the RD_Indication_List then we are back to needing to specify they Value (ThisItem.IND_DESC.Value)

Let me know what type that column is and how it's configured.

 

Why would I be using "ThisItem" on the ComboBox? You are using ThisItem as your filter of the Lookup.  You want to use the value of the IND_DESC field to lookup the Alias in the other table.  I still dont see how this would return the value of "IND_DESC" based on the "Project Code" (InvestvestmentId" value of the table "rdInvestments"? This does not return anything to do with IND_DESC.  Your combobox is set to get values from the RD_Indication_List, therefore you need to specify default values that are compatible with that list...not the rdInvestments table - that's why we do a lookup, to cross reference the correct value in the other table.  The combo box is already returning (in the dropdown) all values of the "Alias" column in "RD_Indication_List" which is right. Yes, that is all governed by the Items property, which we have no problem with.  You just need to return for your defaultselecteditems the properly structured value from that SAME table, which is why we lookup  the value based on the value of ThisItem (which is the curent record of the rdInvestments table).

but I need to return the "IND_DESC" value in "rdInvestments" based on the value in the "Project Code" (InvestmentId)... Then, if the end user wants to change that, they can using the drop down....

YES, this is exactly what we are doing - we just need to get the syntax of your IND_DESC field in your rdInvestments table proper.


 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

@RandyHayes 

 

Hi Randy - IND_DESC is not a look up column and exists in rdInvestments...so not sure what is going on.

and also, "ThisItem" isn't showing up as a value choice when manually typing the formula...This may be the issue. How come?

@simms7400

So...is this ComboBox part of your EditForm??  Or have you placed it outside of the EditForm?

That would be why ThisItem doesn't work if it is outside of the EditForm.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

@simms7400 

In this scenario, you're going to want to have this ComboBox in a DataCard inside your EditForm.  Although you can put it outside, there is a bit more "plumbing" that you'll have to do to connect, display, edit and update the data.

 

If you throw that in a custom DataCard in your edit form, the Formula I gave you last should work just fine.

 

Let me know how it goes.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (80,598)