Answered

Lookup Using Values from Controls

(0) Share

Report

Posted on by bkkueh

Hi All,

I'm having a few controls, named DatePicker2 for the Date field, Combobox1_1 for the Level field, Combobox2 for the Meeting Room field, Dropdown1 for the Start Time field, and Dropdown1_1 for the End Time field as below.

The values in the Dropdown1 and Dropdown1_1 are actually the column names from my Sharepoint list called "Test".

The columns in my Sharepoint list are as below:

So the problem I'm currently facing is, I would like to output the value under the column selected in Start Time field while fulfilling the conditions from my Sharepoint list using the lookup function, where the conditions are from the DatePicker2, Combobox1_1, Combobox2 while referring to column from selection of Dropdown1, it outputs the value selected in the Start Time field instead. My current lookup function in a Label is as below:

LookUp(Test,And(Text(BookDate,"dd/MM/yyyy")=Text(DatePicker2.SelectedDate,"dd/MM/yyyy"),Levels=ComboBox1_1.Selected.Result,MeetingRooms=ComboBox2.Selected.MeetingRooms),Dropdown1.SelectedText.Value)

For example, taking the values selected in my screenshot, the output is "8am" instead of the value under column "8am".

Any idea why is this happening? Hope I've explained the issue clear enough, will try to provide further details if necessary.

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (2)

Sort by

Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @bkkueh :

The point is that Canvas App does not support dynamic references to column names. An alternative is to use switch to list every possible case:

With( {TheRecord: LookUp(Test,And(Text(BookDate,"dd/MM/yyyy")=Text(DatePicker2.SelectedDate,"dd/MM/yyyy"),Levels=ComboBox1_1.Selected.Result,MeetingRooms=ComboBox2.Selected.MeetingRooms)) }, Switch( Dropdown1.SelectedText.Value, "8am", TheRecord.8am, "9am", TheRecord.9am ) )

Best Regards,

Bof

Was this reply helpful? Yes No
bkkueh 19 on at

Like
a
(0)

Report
Copy link

Link copied!
Good day Bof,

Really appreciated your reply and solution for the issue, now I am getting the content of the columns nicely, but not the correct row. It keeps outputting the value of the row I circled in red, instead of taking the row with the date+level+room chosen in the controls. Screenshots as below:

I have tried to change the Text() function from
Text(BookDate,"dd/MM/yyyy")=Text(DatePicker2.SelectedDate,"MM/dd/yyyy")
to
Text(Bookdate,"MM/dd/yyyy")=Text(DatePicker2.SelectedDate,"MM/dd/yyyy")
in order to follow the formatting of PowerApps, but to no avail as well. I thought it was giving me the most bottom row or the row with empty input in the 'Levels', 'MeetingRooms' and 'BookDate' columns, so I added another empty row on the bottom as shown in above screenshot, but it still outputs the results from circled row.

Any idea on this? 🙂

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

Like
a
(0)

Report
Copy link

Link copied!
Hi @bkkueh :

I'm not quite sure what is causing this problem, I suggest you to check a few values:

Text(First(BookDate),"MM/dd/yyyy")
Text(Last(FirstN(BookDate,2)),"MM/dd/yyyy")
Text(Last(FirstN(BookDate,3)),"MM/dd/yyyy")
Text(DatePicker2.SelectedDate,"MM/dd/yyyy")

You could check if their values are as expected.

Best Regards,

Bof

Was this reply helpful? Yes No
bkkueh 19 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi there Bof,

Below 3 lines are giving me errors, mentioning that there are invalid arguments in Text function and First function.
Text(First(BookDate),"MM/dd/yyyy")
Text(Last(FirstN(BookDate,2)),"MM/dd/yyyy")
Text(Last(FirstN(BookDate,3)),"MM/dd/yyyy")

And surprisingly, this line is returning blank.
Text(DatePicker2.SelectedDate,"MM/dd/yyyy")

Thanks.

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

Like
a
(0)

Report
Copy link

Link copied!
Hi @bkkueh :

Sorry it's my mistake ,the formula should be:

Text(First(YourSPList).BookDate,"mm/dd/yyyy")
Text(Last(FirstN(YourSPList,2)).BookDate,"mm/dd/yyyy")
Text(Last(FirstN(YourSPList,3)).BookDate,"mm/dd/yyyy")

Also since you mentioned that the return value of this formula

Text(DatePicker2.SelectedDate,"mm/dd/yyyy")

is empty, I suggest you delete DatePicker2 and recreate a DatePicker control.

In addition please try "mm/dd/yyyy" instead of "MM/dd/yyyy".

Best Regards,

Bof

Was this reply helpful? Yes No
bkkueh 19 on at

Like
a
(0)

Report
Copy link

Link copied!
hi @v-bofeng-msft,

Interesting, have recreated the DatePicker and now there's an output already. All the other 3 formula are giving the desired output as well.

But now I found out that if I try to get the output of the comboboxes using below formula in a label, they return me with an empty value. I tried deleting and recreating the comboboxes, but still returning an empty value.
ComboBox3.Selected.Result
where ComboBox3 is for the Level field;
ComboBox4.Selected.MeetingRooms
where ComboBox4 is for the Meeting Room field.

Thanks.

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

Like
a
(0)

Report
Copy link

Link copied!

Hi @bkkueh :

I'm not sure why you can't get the value of the record selected by the ComboBox, I suggest you consider recreating an APP or create a support ticket for professional assistance following the below procedure.

Get Help + Support - Power Platform | Microsoft Docs

Best Regards,

Bof

Was this reply helpful? Yes No
Verified answer

bkkueh 19 on at

Like
a
(0)

Report
Copy link

Link copied!
hi @v-bofeng-msft,

I think earlier when the controls were in the EditForm, I wasn't able to get the value selected in the Comboboxes. Now that I have deleted the EditForm and placed all the controls on a blank page, it all works! Except one thing, now I have a button below for submission purposes, and I would like to enable the button when the above formula returns "Available", and disable the button otherwise.

I've tried using below formula but the button remains "Edit" even though the formula has returned "N/A" instead of "Available".
If( With( {TheRecord:LookUp(MeetingRoomBooking,And(Text(BookDate,"dd/mm/yyyy")=Text(DatePicker3.SelectedDate,"dd/mm/yyyy") ,Levels=ComboBox5.Selected.Result,MeetingRooms=ComboBox6.Selected.MeetingRooms))}, Switch(Dropdown1.SelectedText.Value, "8am",TheRecord.'8am', "9am",TheRecord.'9am', "10am",TheRecord.'10am', "11am",TheRecord.'11am', "12pm",TheRecord.'12pm', "1pm",TheRecord.'1pm', "2pm",TheRecord.'2pm', "3pm",TheRecord.'3pm', "4pm",TheRecord.'4pm', "5pm",TheRecord.'5pm', "6pm",TheRecord.'6pm', "7pm",TheRecord.'7pm') )="Available", DisplayMode.Edit, DisplayMode.Disabled )

Thanks.

Was this reply helpful? Yes No
bkkueh 19 on at

Like
a
(0)

Report
Copy link

Link copied!

Oops, I know where I went wrong. I had the formula in the OnSelect property instead of the Display Mode property.

Thanks a lot to @v-bofeng-msft for solving my previous issues.

Was this reply helpful? Yes No