cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KevinHardy
Level 8

Run formula on button select if field is empty

Hi All,

Background:

I have a Sharepoint List called 'Observation Logs' with these columns:

Screenshot 2019-12-13 at 09.41.12.pngObservation Log columns

'Date' is a Date/Time column. 'Time' is a Single Line Text column.

There is a requirement for the 'Time' column to be pre-populated with data for a 24 hr period with 30 minutes intervals. The formula for this is:

UpdateContext({varVis:false}) & Set(timeFill,["07:00","07:30","08:00","08:30","09:00","09:30","10:00","10:30","11:00","11:30","12:00","12:30","13:00","13:30","14:00","14:30","15:00","15:30","16:00","16:30","17:00","17:30","18:00","18:30","19:00","19:30","20:00","20:30","21:00","21:30","22:00","22:30","23:00","23:30","00:00"]);
ForAll(timeFill,Patch('Observation Logs',Defaults('Observation Logs'),
{Time: timeFill[@Value], Title: "Observation Log", Date: Today(), SupportedPerson: "John Smith"
}))

This means the times can then be shown in a gallery with users able to see which ones need completing and edit each entry to include the required information.

All good and functioning as expected.

 

The Question: All the above works great and does what I need it to do. However, I would like this to happen everyday.

What I'm looking for is the best, most efficient way to do this.

I imagine it may be achievable using a flow to do this at 06:45 ever day - but I know zero about this.

I have a 'New Log' button which takes users to the EditForm screen, so I'm wondering if there is a formula that would look at the 'Time' column, and if the 'Time' fields were empty for today's 'Date' then run the formula to populate it with the times., without overwriting the previous day's rows.

Over to you clever people 🙂

Kev

1 ACCEPTED SOLUTION

Accepted Solutions
KevinHardy
Level 8

Re: Run formula on button select if field is empty

Hi All,

Just in case someone searches for a similar issue I have found a way to do this - not sure how elegant it is, but it does the job.

I filtered the Gallery of the Sharepoint list using a Datepicker that defaults to Today():

Filter([@'Observation Logs'], Text(Date,ShortDate) = Text(DatePicker1.SelectedDate,ShortDate))

I then put this formula in the OnVisible property of the page:

If(CountRows(Gallery3.AllItems) = 0, Set(timeFill, ["07:00","07:30","08:00","08:30","09:00","09:30","10:00","10:30","11:00","11:30","12:00","12:30","13:00","13:30","14:00","14:30","15:00","15:30","16:00","16:30","17:00","17:30","18:00","18:30","19:00","19:30","20:00","20:30","21:00","21:30","22:00","22:30","23:00","23:30","00:00"]);
ForAll(timeFill,Patch('Observation Logs',Defaults('Observation Logs'),
{Time: timeFill[@Value], Title: "Observation Log", Date: DatePicker1.SelectedDate,
})), UpdateContext({varPOP:"Don't Run"}))

So when a user opens the app and navigates to the screen, it checks to see if there are any entries in the gallery for today's date, and if it's empty runs the rest of the forumula to populate the column with the times. The varPOP "Don't Run" at the end was just to populate a text label so I knew what was happening.

I'm quite pleased with myself for working this out on my own (well, me, Google and Shane Young videos!). Shows how far I have come from being a total newbie 6 weeks ago 🙂

Kev

 

View solution in original post

1 REPLY 1
KevinHardy
Level 8

Re: Run formula on button select if field is empty

Hi All,

Just in case someone searches for a similar issue I have found a way to do this - not sure how elegant it is, but it does the job.

I filtered the Gallery of the Sharepoint list using a Datepicker that defaults to Today():

Filter([@'Observation Logs'], Text(Date,ShortDate) = Text(DatePicker1.SelectedDate,ShortDate))

I then put this formula in the OnVisible property of the page:

If(CountRows(Gallery3.AllItems) = 0, Set(timeFill, ["07:00","07:30","08:00","08:30","09:00","09:30","10:00","10:30","11:00","11:30","12:00","12:30","13:00","13:30","14:00","14:30","15:00","15:30","16:00","16:30","17:00","17:30","18:00","18:30","19:00","19:30","20:00","20:30","21:00","21:30","22:00","22:30","23:00","23:30","00:00"]);
ForAll(timeFill,Patch('Observation Logs',Defaults('Observation Logs'),
{Time: timeFill[@Value], Title: "Observation Log", Date: DatePicker1.SelectedDate,
})), UpdateContext({varPOP:"Don't Run"}))

So when a user opens the app and navigates to the screen, it checks to see if there are any entries in the gallery for today's date, and if it's empty runs the rest of the forumula to populate the column with the times. The varPOP "Don't Run" at the end was just to populate a text label so I knew what was happening.

I'm quite pleased with myself for working this out on my own (well, me, Google and Shane Young videos!). Shows how far I have come from being a total newbie 6 weeks ago 🙂

Kev

 

View solution in original post

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (5,514)