Create checkbox in gallery based on current month

[venka91]

Hi there,

I have a share point list where 10 employees records stored. 

I would like to create a gallery items for each employee based on current month.

Each row in gallery i would like display the checkbox based number of days for the month selected from drop down.

Lastly I will patch the gallery data to a new sharepoint list.

Any ideas please. I  not sure how to dynamically populate check box based on number of days for a month.

[metsshan]

Hey @venka91, there's a good blog article I found which covers "How to populate a collection of consecutive days with additional fields" http://powerappsguide.com/blog/post/how-to-display-all-days-between-two-dates 

You can perform an " SQL JOIN" style operation with your SharePoint list by using the PowerApps Ungroup function.

I have mocked up a very basic implementation, which I hope helps you get started. You may need to do some nested galleries to show it the way you have described, but please let me know if it helps.

My example: for each person in a list of people in SharePoint, for each date in the selected month, show a Checkbox in a Gallery:

Gallery Items:

SortByColumns(
   Ungroup(
      AddColumns(
         People,
         "Dates",
         With(
             {
                StartDate:Date(Year(Now()), drpMonths.Selected.MonthNumber, 1),
                DaysInMonth: Sequence(Day(Date(Year(Now()), drpMonths.Selected.MonthNumber + 1, 0)))
             },
             AddColumns(
                DaysInMonth,
                "Date",
                DateAdd(StartDate, Value - 1, Days)
             )
          )
       ),
       "Dates"
   ),
   "Title", "Date"
)

Dropdown (drpMonths) Items:

With(
   {
      Months: Sequence(12)
   },
   AddColumns(
      Months,
      "MonthName",
      Text(Date(Year(Now()), Value, 1), "mmmm"),
      "MonthNumber",
      Value
   )
)

Note: You will need to consider which year you generate the list of months for, in case it needs to be a leap year.

Let us know if you need any help with the Patch() back to SharePoint.

[venka91]

Thank you so much. I am very thankful to you for taking this effort and helping me out.

I need further help here.

My Gallery looks as below.

1. I want to know if i can wrap the dates something like Horizontally, instead of Vertically.

2. I am just wondering how would i patch this checked items with dates on my sharepoint list ? Since days of month are dynamic, how would i create this dynamic columns ?

Since this is used for attendance app, i am just wondering if this could be done in powerapps and share point list...

i thought this is just getting checkbox in my gallery, but now this is getting deeper and deeper. Please help.

[metsshan]

Hey, I think we are on the right track.

1. You can use a Vertical Gallery with Wrap Count to wrap horizontally instead

2. I think you only need three columns, "Name", "Date" and "Attended" (Yes/No). Continuing with my example, this is how I patched to SharePoint, to create new Attendance records:

btnSubmit OnSelect:

ForAll(
    galAttendance.AllItems As NewAttended,
    Patch(
        Attendance,
        Defaults(Attendance),
        {
            Title: NewAttended.Title,
            Date: NewAttended.Date,
            Attended: NewAttended.chkAttended.Value
        }
    )
)

'chkAttended' is the name of the Checkbox in my gallery item

This is a Creation-only function, though. Will you need to edit these records in PowerApps?

[venka91]

Thanks.

But as i am using nested gallery, i am not able to patch my sharepoint. i could not able to fetch the nested gallery items when i say galAttandance.AllItems

[metsshan · Accepted solution]

I was having issues too, but I found a reference to this bug (? feature? not sure): https://powerusers.microsoft.com/t5/Building-Power-Apps/ForAll-on-Nested-Gallery/td-p/204900 

I put a label in my Parent Gallery counting all the checked boxes in my nested gallery, and it seems to 'force' PowerApps to recognise the nested AllItems.

Anyways, this is what I got working:

galParentAttendance Data source:

SortByColumns(
    AddColumns(
        People,
        "Dates",
        With(
            {
                StartDate:Date(Year(Now()), drpMonths_2.Selected.MonthNumber, 1),
                DaysInMonth: Sequence(Day(Date(Year(Now()), drpMonths_2.Selected.MonthNumber + 1, 0)))
            },
            AddColumns(
                DaysInMonth,
                "Date",
                DateAdd(StartDate, Value - 1, Days)
            )
        )
    ),
    "Title"
)

 galNestedAttendance Data source:

SortByColumns(
    ThisItem.Dates,
    "Date"
)

Label to force the nested gallery .AllItems to work Text:

"Attended count: " & CountIf(galNestedAttendance.AllItems, chkAttended_1.Value = true)

Submit button OnSelect:

ForAll(
    galParentAttendance.AllItems As NewAttended,
    ForAll(
        NewAttended.galNestedAttendance.AllItems As NestedAttendance,
        Patch(
            Attendance,
            Defaults(Attendance),
            {
                Title: NewAttended.Title,
                Date: NestedAttendance.Date,
                Attended: NestedAttendance.chkAttended_1.Value
            }
        )
    )
)

Two loops to patch all records.

Let me know if this helps you figure out your own implementation?

[venka91]

Once again, i thank you so much for working for me and working with me.

I will test this tonight and let you know by tomorrow... no doubt it should work. i will confirm the same.

[venka91]

This is working well, and i helped me to learn and further develop according to my environment. 

I thank you so much for your contribution and helping on time.

[metsshan]

I learned something, too 🥳 We're all winners today.

[Adkallday]

@metsshan this is very helpful. I think I have a variation of what you found here and was hoping you could help. I'm trying to use a nested gallery as a multi entry form but am having a hard time bringing in everything into a singular collection. So far I can get the first row of the parent compiled but am having a hard time gathering the next rows in the parent with associated nested info. 

Parent Gallery:

Sort(
        Filter(
            DistColTask,
            !IsBlank(Value)
        ),
    Text(Value),
    SortOrder.Ascending
)

Nested Gallery: 

Sort(
    Filter(colWeekInfo, Month = Dropdown1_1.Selected.Value && Year = Dropdown1_2.Selected.Value),
    DateValue(StartOfWeek),
    SortOrder.Ascending
)

Count Function that was recommended:

ThisItem.Value & " " & CountIf(WeekAndInputGallery.AllItems, !IsBlank(StartofWeekGalleryDate))

Submit > Hours Button

ClearCollect(
    colEntryHours,
    ForAll(
        PotentialEntryGal.AllItems As NewEntry,
        ForAll(
            NewEntry.WeekAndInputGallery.AllItems As NestedEntry,
            {
                Project: Project_Gallery.Selected.ProjectID,
                ProjectManager: Project_Gallery.Selected.ProjectManager,
                Task: TaskName.Text,
                Date: NestedEntry.StartofWeekGalleryDate.Text, // Adjust this field name accordingly
                Entry: NestedEntry.HoursInput.Text
            }
        )
    )
)

UPDATE:

I have the collection  gathering all correct information. New Image below. 

Updated Button: 

Clear(colEntryHours); 
ForAll(
    PotentialEntryGal.AllItems As NewEntry,
    ForAll(
        NewEntry.WeekAndInputGallery.AllItems As NestedEntry,
        Collect( // Use Collect instead of ClearCollect
            colEntryHours,
            {
                Project: Project_Gallery.Selected.ProjectID,
                ProjectManager: Project_Gallery.Selected.ProjectManager,
                Task: NewEntry.TaskName.Text,
                Date: NestedEntry.StartofWeekGalleryDate.Text,
                Entry: NestedEntry.HoursInput.Text
            }
        )
    )
)