cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Newbie2019
Resolver I
Resolver I

Selected date in datepicker lookup sharepoint list and return another date

Hi,

I am trying to create 2 date fields. The first date is the one selected by the user. In my case its the day a detention is issued to a student. I would then like a date generated for the detention. As the days of detentions vary depending on the issue day / public holidays etc, I created a sharepoint list in TEXT format. Detentiondates.JPG

Within the editform I have the following:

Form Date.JPG

I have linked it with a lookup:

LookUp(DetentionDates, Start = Text(DatePicker1_1.SelectedDate),End)

 

When I select a new date it automatically fills in the corresponding detention date. Which is exactly what i want.

 

The issue is it will not work when published. All I get is this:

Date 2.JPG

It wont populate the date no matter the day I select.

When I test it in the create mode it populates the sharepoint list, but wont even show a date when used in a browser or mobile.

Sharep.JPG

 

Any ideas on where I am going wrong.........?

 

3 ACCEPTED SOLUTIONS

Accepted Solutions

Thank you. I'll try out your suggest.

View solution in original post

Thank you so much Kris Dai. It works perfectly in browser and on mobile. The date format was the issue. Cheers

View solution in original post

Hi @Newbie2019 ,

Please check if my solution could help in your scenario. If yes, please go ahead to click "Accept as Solution" to identify my solution is helpful.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
timl
Super User
Super User

Hi @Newbie2019 

In terms of building a reliable app, the best approach would be to store your dates in a date field, rather than a text field.

To help clarify my understanding of this, could you explain why the issue date/public holidays would prevent you from storing the offence and detention dates in a date column?

Hi timl,,

I had them stored as text as I have had trouble importing them from excel into sharepoint with flow. When I ran the flow the dates turned up as blank in the columns and only the Days of the week .. Monday etc would populate in the list.

 

 

I have just chnaged the columns to date format. Could you advise the best course of action from here please.

 

Cheers

 

v-xida-msft
Community Support
Community Support

Hi @Newbie2019 ,

I have made a test on my side, and don't have the issue that you mentioned.

 

Based on the issue that you mentioned, I think there is something wrong with your formula. Please check if your LookUp formula could return proper value within your app.

You could add a Label control in your app, set the Text property to following:

LookUp(DetentionDates, Start = Text(DatePicker1_1.SelectedDate),End)

check if the Label control could display proper value.

Please also consider take a try to modify your formula as below:

LookUp(
DetentionDates,
Start = Text(DatePicker1_1.SelectedDate, "dd/mm/yyyy"),
End
)

check if the issue is solved.

 

In addition, I agree with @timl 's thought almost, please consider take a try to change your Start and End column into Date type column. Then within your app, you could take a try with the following formula to retrieve corresponding End column value:

LookUp(
DetentionDates,
Text(Start, "dd/mm/yyyy") = Text(DatePicker1_1.SelectedDate, "dd/mm/yyyy"), /* <-- Start column is a Date type column*/
End
)

Above formula may cause a Delegation warning issue, if the amount of your data source records is not more than 2000, you could ignore this warning issue.

More details about Delegation in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

In order to get rid of this Delegation warning issue, please take a try with the following workaround:

Set the OnStart property of the App control or OnVsible property of the first screen of your app to following:

ClearCollect(RecordsCollection, DetentionDates)

Modify your formula as below (set the Default property of the DetentionDate Datepicker box to following😞

LookUp(
       RecordsCollection, 
       Text(Start, "dd/mm/yyyy") = Text(DatePicker1_1.SelectedDate, "dd/mm/yyyy"),   /* <-- Start column is a Date type column*/
       End
)

Note: Please make sure the time zone settings of your SP list (SP Site) is the same as your local time zone.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you. I'll try out your suggest.

View solution in original post

Thank you so much Kris Dai. It works perfectly in browser and on mobile. The date format was the issue. Cheers

View solution in original post

Hi @Newbie2019 ,

Please check if my solution could help in your scenario. If yes, please go ahead to click "Accept as Solution" to identify my solution is helpful.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,478)