Selected date in datepicker lookup sharepoint list and return another date

[Newbie2019]

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. 

Within the editform I have the following:

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:

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.

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

[timl]

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?

[Newbie2019]

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.

[Newbie2019]

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

Cheers

[v-xida-msft]

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,

[Newbie2019 · Accepted solution]

Thank you. I'll try out your suggest.

[Newbie2019 · Accepted solution]

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

[v-xida-msft · Accepted solution]

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,