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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

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 (2,696)