cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ByPA
Helper I
Helper I

Lookup issues

Hi,

 

I have been advised to use Sharepoint and stay away from Excel but this is my first app project, and I am quite far in already. Appreciate it if you can cut me some slack.

 

On a canvas,

1) I have a global variable, HG, whose value is dependent upon some selection that the user has made earlier. Here, it is 4:

 

ByPA_2-1634549134699.png

 

2) Basically, I have some dropdown boxes and a "Next" Button to look up some values from an Excel sheet. It is like below:

 

ByPA_1-1634548717358.png

3) On the "Next" button above, I am trying to look up values in an Excel table. The selections in the dropbox are also from an Excel table. The code goes something like this:

 

 

 

 

 

 

ClearCollect(QA1A,HG&Q1DDBox.Selected.RS);
ClearCollect(QA1B,AddColumns(QA1A, "QnNo", 1, "RF", LookUp(Q1Table,CONRS=HG&Q1DDBox.Selected.RS,RF)));
ClearCollect(QAScr1,RenameColumns(QA1B,"Value","RS"));

 

 

 

 

 

 

4)In the collection, QAScr1, the RS is correct but the RF is wrong:

ByPA_3-1634549530313.png

5) The Q1Table is as below:

RV                              CONRS         RS                     HG        RF

Q1Concat125% or less25% or less11
Q1Concat126% to 50%26% to 50%10.75
Q1Concat151% to 75%51% to 75%10.5
Q1Concat1More than 75%More than 75%10.25
Q1Concat225% or less25% or less21.6
Q1Concat226% to 50%26% to 50%21
Q1Concat251% to 75%51% to 75%25.5
Q1Concat2More than 75%More than 75%22.3
Q1Concat425% or less25% or less42
Q1Concat426% to 50%26% to 50%41.5
Q1Concat451% to 75%51% to 75%41
Q1Concat4More than 75%More than 75%40.5

 

 

The look up problem remains for other dropboxes further down the app. Based on the pt 5, RF should have been 1 instead of 0.5. For some reason, the code is looking for the case where HG=1 & the dropbox.Selected.RS instead of HG=4 & the dropbox.Selected.RS. 

 

Any ideas?

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Community Champion
Community Champion

Hi @ByPA 

 

  • Please rename the Variable HG to varHG. This will conflict when you compare
  • In the filter criteria update to below
ClearCollect(QA1B,AddColumns(QA1A, "QnNo", 1, "RF", LookUp(Q1Table,RS=Q1DDBox.Selected.RS && HG=varHG,RF)));

 

In this case, I assume the Dropdown (Q1DDBox) selected value is "25% or less"


Thanks,
Stalin - Learn To Illuminate

View solution in original post

7 REPLIES 7
ByPA
Helper I
Helper I

The format type of "CONRS" is text.

Would the below somehow confuses the lookup condition, or more specifically, HG?

HG&Q1DDBox.Selected.RS

 

StalinPonnusamy
Community Champion
Community Champion

Hi @ByPA 

 

Please try this

ClearCollect(QA1B,AddColumns(QA1A, "QnNo", 1, "RF", LookUp(Q1Table,RS=HG&Q1DDBox.Selected.RS,RF)));

 

 

Hi @StalinPonnusamy ,

 

I tried and it is giving me blank:

ByPA_0-1634577347844.png

Just to clarify, is changing CONRS to RS the only difference? Because CONRS contains the conditions...

StalinPonnusamy
Community Champion
Community Champion

Hi @ByPA 

 

  1. I believe the Q1 dropdown comes from the RS field from the Q1Table. So we have to filter RS from the table and it's selecting the first satisfying condition.
  2. We are not filtering the HG column. Does HG mean this quarter?
ClearCollect(QA1A,HG&Q1DDBox.Selected.RS);
ClearCollect(QA1B,AddColumns(QA1A, "QnNo", 1, "RF", LookUp(Q1Table,RS=HG&Q1DDBox.Selected.RS && HG=4,RF)));
ClearCollect(QAScr1,RenameColumns(QA1B,"Value","RS"));

 

If you are looking for Oct is Q4 then we need to calculate the Q based on the current month. Let me know I will work on this and send the response. I want to make sure what you are looking for.


Thanks,
Stalin - Learn To Illuminate

 

 

Thank you @StalinPonnusamy 

 

I apologise as there are some gaps.

 

1) Q1 just stands for Question 1, not Quarter 1.

2) Q1 dropdown comes from another Excel table, Q1RS, not Q1Table (also an Excel Table). As I am not comfortable, I thought it might be better to seperate the two tables as I experiment with different actions. But the relevant values (RS) for display on the dropdown are intended to be the same.

3) The earlier table is from collection QAScr1

ByPA_0-1634579286243.png

4. HG is just a classifier I assigned based on the selected industry. It is not a date. It is stored as a global variable. 

 

Thank you again for your help.

 

StalinPonnusamy
Community Champion
Community Champion

Hi @ByPA 

 

  • Please rename the Variable HG to varHG. This will conflict when you compare
  • In the filter criteria update to below
ClearCollect(QA1B,AddColumns(QA1A, "QnNo", 1, "RF", LookUp(Q1Table,RS=Q1DDBox.Selected.RS && HG=varHG,RF)));

 

In this case, I assume the Dropdown (Q1DDBox) selected value is "25% or less"


Thanks,
Stalin - Learn To Illuminate

View solution in original post

Hi @StalinPonnusamy ,

 

I rename HG to varHG. It is as you mentioned that there is a conflict when there is a comparison.

 

I have yet to update the filter criteria as renaming HG to varHG seems to work so far...I did 2 rounds of testing and still doing so...

 

Thank you so much!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,698)