cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AVTS
Helper V
Helper V

Canvas App - Date Lookup

Hi,

 

My dataverse table is having following columns,

 

AVTS_0-1620808992329.png

 

Following design from Canvas App,

AVTS_4-1620809379422.png

Requirement,

Datepicker(pointer3) should change to  12/10/2013 (Since material no 10121900 BBD is 12 month as per table)

 

How to achieve this.

2 ACCEPTED SOLUTIONS

Accepted Solutions
AVTS
Helper V
Helper V

Finally following code gives the result. Partial input from @WarrenBelz  and other input from @ zaphod88

 

With({wMonths:LookUp(Dailyinventory,Materialno=Dropdown1.Selected.Materialno, BBD)}, DateAdd(DatePicker1.SelectedDate, Value(Dropdown1.Selected.BBD), Months))

 

 

Thank you both

View solution in original post

@AVTS ,

Why bother using the WIth() statement at the top - you are not using it in your statement - you just need

DateAdd(
   DatePicker1.SelectedDate, 
   Value(Dropdown1.Selected.BBD), 
   Months
)

but as I said some posts back, this only works if you actually select from the box before. I am not sure where the other input you are referring to comes from - there are only two of us on the thread. I also see you marked your own answer as the solution after all of that.

View solution in original post

10 REPLIES 10
WarrenBelz
Super User III
Super User III

Hi @AVTS ,

You will have to fill in the proper references, but the structure would be like this

With(
   {
      wMonths:
      LookUp(
         YourTableName,
         Materialno=YourTopDD.Selected.Materialno
      ).BBD
   },
   DateAdd(
      YourProdDatePicker.SelectedDate,
      wMonths,
      Months
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

wITH

Formula follows,

 

LookUp(
Dailyinventory,
Materialno=Dropdown1.Selected.Materialno
).BBD

AVTS_0-1620857695160.png

 

Hi @AVTS ,

I assume you are putting this in the DefaultDate of Date picker 2? Also are you resetting this on the OnChange of data picker 1?

With(
   {
      wMonths:
      LookUp(
         Dailyinventory,
         Materialno=Dropdown1.Selected.Materialno
      ).BBD
   },
   DateAdd(
      DatePicker1.SelectedDate,
      wMonths,
      Months
   )
)

also put this on a label and see if you get the number of months you are after

LookUp(
   Dailyinventory,
   Materialno=Dropdown1.Selected.Materialno
).BBD

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Tried this in label which gives no result,

 

LookUp(
Dailyinventory,
Materialno=Dropdown1.Selected.Materialno
).BBD

But tried following which given the results,

Dropdown1.Selected.BBD

AVTS_0-1620856235853.png

 

 

@AVTS ,

From the information you provided (which is all I can rely on), the code below

LookUp(
   Dailyinventory,
   Materialno=Dropdown1.Selected.Materialno
).BBD

is simply looking up the record in your Table DailyInventory where Materialno equals the value selected in the your drop-down and then displaying the field BBD from that record. As you have noted if you have the full record in the combo box Items (which I was unsure of), then you can also get the value from there, but beware that this only works if you select an item there and will not work on a value simply populated from a record (hence my suggestion of the LookUp).

So if you are happy to always select the drop-down item, this should work

DateAdd(
   DatePicker1.SelectedDate,
   Dropdown1.Selected.BBD,
   Months
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

 

This formula not providing the result as expected.

Following code is working with label result from table ,

Dropdown1.Selected.BBD

AVTS_0-1620907848379.png

 

 

But if i use your formula

LookUp(
Dailyinventory,
Materialno=Dropdown1.Selected.Materialno
).BBD

for same material(above result 8 )  it shows result with  date format  and some material no result.

AVTS_1-1620908030571.png

 

 I am not sure about this formula works.

 

Any one else can share your view

@AVTS ,

The one thing here that you did not tell me - is Materialno numeric or Text? If numeric, you need

LookUp(
   Dailyinventory,
   Materialno=Value(Dropdown1.Selected.Materialno)
).BBD

however you should get a type mismatch error with what you had. 

Other than that, this is the simplest of LookUps and if you get no result, then there is something wrong with the values being searched.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

most of the columns are Text only. surprised even with label also this is not working.

AVTS
Helper V
Helper V

Finally following code gives the result. Partial input from @WarrenBelz  and other input from @ zaphod88

 

With({wMonths:LookUp(Dailyinventory,Materialno=Dropdown1.Selected.Materialno, BBD)}, DateAdd(DatePicker1.SelectedDate, Value(Dropdown1.Selected.BBD), Months))

 

 

Thank you both

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,154)