Setting a Date Picker 10 Day based on Drop down value

[LyonsBI_BRL]

Hey Everyone, 

I'm really close to having this one figured out. Scenario is, I have a Dropdown called DropDown1. Based on Dropdown1s value, I need it to Patch the new value in the drop down to the DB (that's accomplished) and be able to change the value in DatePicker1 by 10 days. 

On my Dropdown1 I have 

Patch('Customers',ThisItem,{'Status':Customer.Selected.'Status'});

If(ThisItem.'Status' = "Checkd-In", DateAdd(CheckOutDate.SelectedDate,10,Days)

On the DatePicker1 I'm unsure what to set here. I was thinking a variable, though I'm unsure how to accomplish this. Any help would be greatly appreciated. 

Thanks!

[LyonsBI_BRL]

UPDATE - Figured out the first part! 

In the DatePicker1 it would need to read - 

If(Customer.SelectedText.Value = "Checked-In", DateAdd(CheckOutDate.SelectedDate,10,Days),Today()) - Question how would I Patch this then?

Last part of the equation here is getting my DatePicker2 (The day they checked in) to stay put as I need to be able to see what day they checked in. Right now I have the default date as Today() though that's not working. I need to be able to keep the date as to what day they checked in and have it patch back to SQL.

Thanks! At least one part of the solution is fixed 🙂 

[v-xiaochen-msft]

Hi @LyonsBI_BRL ,

Sorry, I cannot understand your needs.

Could you explain it again ...?

Best Regards,
Wearsky

[LyonsBI_BRL]

@v-xiaochen-msft thanks for responding back. 

Below is my Customer Check part. 

First part that I have solved was getting DatePicker2 on the right to change when the Dropdown = "Checked Out" increases by 10 days.

If(CheckInStatus.SelectedText.Value = "Checked Out", DateAdd(CheckOutDate_1.SelectedDate,10,Days),Today())

Then to patch that back to the DB wouldn't it be

Patch('Customers', ThisItem,{'CheckOutDate':CheckOutDate_1.SelectedDate})

Finally the first DatePicker (next to the A14 drop down), What would be the correct formula to Set the Date and have it write back to the DB?

[v-xiaochen-msft]

Hi @LyonsBI_BRL ,

Please provide the following information.

What do you want to do when the user check in?

What do you want to do when the user check out?

Which control value do you want to write back to the data source?

I currently only know that the value of the second datepicker is the date of the first datepicker +10 when the dropdown 's status is "Check out".

Best Regards,
Wearsky

[LyonsBI_BRL]

When the User Checks In I want DatePicker1 to show Today's Date and write back to the SQL datasource called Customers with that date

When the User Checks Out I want DatePicker2 to show 10 Days ahead of their check in date and write back to the SQL data source with that date

[v-xiaochen-msft]

Hi @LyonsBI_BRL ,

Please try the following formula on the Onchange property of you 'check in/check out' dropdown control:

If(Customer.SelectedText.Value = "Checked In",
   Patch('Customers', ThisItem,{'CheckInDate':Today()}),
   Customer.SelectedText.Value = "Checked Out",
   Patch('Customers', ThisItem,{'CheckOutDate':DateAdd(ThisItem.CheckInDate,10,Days)})
)

Set the first datepicker's DefaultDate property to:

ThisItem.CheckInDate

Set the second datepicker's DefaultDate property to:

ThisItem.CheckOutDate

Next I will explain the formula:

1. When the user selects 'check in', today's date will be patched to the CheckInDate column.
2. When the user selects'check out', the value of the CheckInDate column will be added for 10 days and patched to the CheckOutDate column.
3. The first datepicker always displays the value of the CheckInDate column.
4. The second datepicker always displays the value of the CheckOutDate column.

Is this what you want?

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

[LyonsBI_BRL]

@v-xiaochen-msft 

Shoot this is super close! 

I changed both defaults for the DatePicker to ThisItem.CheckInDate and ThisItem.CheckoutDate

Formula went in with no errors. 

However when I change the status from Not-Started to Check-In or Check-Out it'll automatically revert back to Not-Started. That and if I attempt to change it again, it'll keep adding on 10 more days. 

[v-xiaochen-msft]

Hi @LyonsBI_BRL ,

This is because when you change the status of the dropdown from "not started" to "checked in" or "checked out", you did not update the value of the column in the table at the same time.

So, I assume your Customers table has a Status column . It is a Text column.

You could modify your formula like this :

If(Customer.SelectedText.Value = "Checked In",
   Patch('Customers', ThisItem,{'CheckInDate':Today(),'Status':Customer.Selected.'Status'}),
   Customer.SelectedText.Value = "Checked Out",
   Patch('Customers', ThisItem,{'CheckOutDate':DateAdd(ThisItem.CheckInDate,10,Days),'Status':Customer.Selected.'Status'})
)

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

[LyonsBI_BRL]

Okay well getting closer

Now it's not doing anything

Using this 

If(Status.SelectedText.Value ="Checked-In",
Patch(Customers,ThisItem,{CheckInDate:Today(),Status:Status.Selected.Result}),
NICS2.SelectedText.Value = "Checked-Out",
Patch(Customers, ThisItem,{CheckOutDate:DateAdd(ThisItem.CheckOutDate,10,Days),
Status:Status.Selected.Result}))

If I try it like this now, That will throw an error and it's giving me an error of 

Now it's not even attempting to change the record at all :(. 

For the Dropdown, I do have the Items set at Distinct(Status.Status, Status)

And the Default is set to ThisItem.Status