Lookup function, in the following scenario

(0) Share

Report

Posted on by JimboSey

321

Hi,

I have a SharePoint list as below,

The columns are set as follows (but I can change them if would be more practical) Rate Band = Single line of text, Rate Percentage = Number, Rate Threshold = Single line of text.

What I am trying to achieve using Lookup on the App side is as follows,

I have a label in the App (Label1), which shows an an amount, in this scenario the amount is £2500, so falls in the 'Low Rate'. I want to Add a Label, lets call it Label2 in this case, that will Lookup my SharePoint list and identify which rate band the figure in Label1 falls into, then calculate the appropriate percentage of Label1 to show in Label2, so in this scenario it will be 10% of £2500.

Any help much appreciated.

Thank you

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Verified answer

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @JimboSey ,

If you use two currency columns: Minimum Rate and Maximum Rate to store the ranges, it would be much easier:

LookUp(YourList,Value(Label1.Text)>= 'Minimum Rate' && Value(Label1.Text) <= 'Maximum Rate').'Rate Percentage'

Best regards,

Was this reply helpful? Yes No
JimboSey 321 on at

Like (0)

Report
Copy link

Link copied!

Thanks so much, so I have implemented the columns/code you suggest.

So at the moment, the value of Label1, shows as a percentage in Label2, is there a way I can take the amount in Label1, take the relevant % for the Rate Percentage column and then show in a Label (lets say label3) the number value.

e.g. Label1 = £2500
Label3 Id like to show the £ value based on the Rate Threshold %

Thanks again

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @JimboSey ,

Of course you can.

You can use Label1.Text and Label2.Text values for Label3 easily.

Best regards,

Was this reply helpful? Yes No
JimboSey 321 on at

Like (0)

Report
Copy link

Link copied!

This worked great thanks, just one thing, how did you get the text to diaply as 10%, see image below (red text) mine shows 0.4, how do I code this to convert to a % in this case 40%

Thank you

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @JimboSey ,

It seems the rate percentage column is of type number. You can use Text/Value functions to convert number to Text with specific format:

So on your end, the Label to display rate percentage, Text set to:

Text(Value(LookUp(YourList,Value(Label1.Text)>= 'Minimum Rate' && Value(Label1.Text) <= 'Maximum Rate').'Rate Percentage')*100,"0%")

The reason is, for a Number column set to percentage format in SharePoint, the data saved in backend is actually still a decimal number, you need to convert it to percentage number in frontend (which is the Label).

Best regards,

Was this reply helpful? Yes No
JimboSey 321 on at

Like (0)

Report
Copy link

Link copied!

Worked perfect, thanks so much 🙂

For a novice like myself, instructions/solutions like this really help.

Was this reply helpful? Yes No