cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AVTS
Post Patron
Post Patron

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
Post Patron
Post Patron

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
Super User

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
Post Patron
Post Patron

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,662)