Populate Sharepoint Column with a range of default values

[Anonymous]

Morning Gurus 🙂

I work for a charity that supports vulnerable autistic adults. One of the apps I am trying to build is to record 30 minutes observations.

The current paper form has a column with times 0700, 0730, 0800 etc and workers record some details next to each time row for a 24 hours period each day. It is easy to see which rows need to be completed.

I have been struggling with how best to translate this to a power app.

Is there a way to pre-fill a Sharepoint column with the time (0700, 0730, 0800 etc) using a PowerApps formula?

I could then display these in a Gallery and users could select the appropriate time, and then add the other details before saving to the Observation Sharepoint list. And they would be able to see in the Gallery which time slots still needing something recorded.

Kev

[mdevaney]

@Anonymous 

Suppose you have a SharePoint list with a column called ObservationTime (number type) .  To fill SharePoint with all the possible times 

// list of all possible times to record an observation
Set(timeOptions,[0,30,100,130,200,230,300,330,400,430,300,530,600,630,700,730,800,830,900,930,1000,1030,1100,1130,1200,1230,1300,1330,1400,1430,1300,1530,1600,1630,1700,1730,1800,1830,1900,1930,2000,2030,2100,2130,2200,2230,2300,2330]);

// create a new line in SharePoint for each time
ForAll(
    timeOptions,    
    Patch(
        your_SPlist_name,
        Defaults(your_SPlist_name),
        {ObservationPeriod: timeOptions.Value}   
    )
)

To display the Observation time in your desired format of "0000" you could use the Text formula in your gallery label.

Text(ThisItem.ObservationTime,"0000")  // displays 130 as 0130

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

Hi @mdevaney ,

Forgive my ignorance but where do I put the first 2 formulas? I understand the Gallery reference but not the first lot.

Kev

[mdevaney]

@Anonymous 

Looks like I forgot to provide you with a place to put that code!  I would suggest creating a button and placing my code in the OnSelect property.  The effect will be to create several new entries in SharePoint: one for each time period.

Note: my code only addresses the immediate problem of "how do I create one entry for each time period in SharePoint by clicking a button."  To improve upon my work you will likely want to relate each line to a survey ID to tie them all together or a unique combination of calendar date and observation subject.  Of course this would require additional columns.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

Hi @mdevaney 

The Gallery works fine (I put in the first row to test the formatting). But there is something amiss with the second part of the formula. I promise that the 'Time' column is set up as in Sharepoint as a number column - Although it says 'Time' does not match teh expected type 'number' ?

[mdevaney]

@Anonymous 

It was my mistake.  I had the code timeOptions.Value but it should have been timeOptions[@Value]

// list of all possible times to record an observation
Set(timeOptions,[0,30,100,130,200,230,300,330,400,430,300,530,600,630,700,730,800,830,900,930,1000,1030,1100,1130,1200,1230,1300,1330,1400,1430,1300,1530,1600,1630,1700,1730,1800,1830,1900,1930,2000,2030,2100,2130,2200,2230,2300,2330]);

// create a new line in SharePoint for each time
ForAll(
    timeOptions,    
    Patch(
        NamesList,
        Defaults(NamesList),
        {Time: timeOptions[@Value]}   
    )
)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

Hi @mdevaney 

I thought it was there as the x disappeared but then came back after I clicked the button!. It says Title: Field Required?

Set(timeOptions,[700,730,800,830,900,930,1000,1030,1100,1130,1200,1230,1300,1330,1400,1430,1300,1530,1600,1630,1700,1730,1800,1830,1900,1930,2000,2030,2100,2130,2200,2230,2300,2330,0,30,100,130,200,230,300,330,400,430,300,530,600,630]);
ForAll(timeOptions,Patch('Observation Logs',Defaults('Observation Logs'),{Time: timeOptions[@Value]}))

Kev

[mdevaney · Accepted solution]

@Anonymous 

Fortunately, your next issue is an easy fix.  It means 'Title' is a required field in your SharePoint list settings.  You can either go into List settings and turn off the required checkbox or add a Title to your record being patched.

{Time: timeOptions[@Value], Title: "Placeholder"}

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

@mdevaney  As ever you are a hero!

I had searched on Title: required and realised it was something to do with the dreaded Title field in Sharepoint. Up to this point I had been removing the 'required' element and ignoring them for the most part, but I think now I will just default them to the title of the Sharepoint list (Observation Logs in this example).

Thanks again 🙂

Kev