PowerApps Gallery to SharePoint list data submission using multiple rows.

[Anonymous]

Hello Everyone, I need your help in creating a power app to track resource's available for each week.

I have created a two list. One list called "Resource Details" where employee information I wanted to store from their user profile including Employee ID which I will use in the second list.

Second list called "Resource Available Hours"I have columns like Employee ID, date, available hours, comments and one drop down with values [full day, half day, leave]

Requirements:

In the new form, the user information will be filled in their respective columns auto populated and fields are disabled for edit and it is linked to list "Resource Details"

along with that from the second list all the columns should appear 7 times (Monday to Sunday) in form where date field have autopopulated current week dates based on a button or a field called "StartDate"

I want this data to be same saved in respective list . In the second list as every submission will have 7 items so we need to create a folder with Employee ID and save this 7 items inside that and when user opens in edit form they should see all data and they can again submit it.

I don't know how to use gallery and collection, please guide me from first step. If there are any other way, please suggest that too

I also need a reporting feature where I can generate a report concatenating fields of both the list together based on dates or employee name.

If you need more clarity, let me know, for field details you can refer the attachment

[WarrenBelz · Accepted solution]

Hi @Anonymous ,

As you have noted, what is missing in the middle of the two forms is a gallery showing all the Resource Hours Available for the item Resource Details. The user can then either select one of these to edit or create a new item (which will then be shown in the gallery).

One question I have not asked is how you select the Resource Details record as this can assist the reference in the second gallery. Assuming however for a moment that you have Employee ID in a control called EmpID (change to your name) on the top form  

you would firstly need to create a Gallery with the Items 

Filter(
   'Resource Details',
   ID = Value(EmpID.Text)
)

and this would display the list of Resource Details belonging to the Employee ID. You would then add Labels to the gallery with the Text property ThisItem.FieldName with this being the actual field name you want to display.

Calling this Gallery ResourceGallery (change to your name), the Item of the second form would be

ResourceGallery.Selected

The OnSelect of the Gallery would navigate to the screen the second form is on.

There is more to this for new records, etc, but see if you can get this structure working as far as seeing all the data you need.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-xida-msft · Accepted solution]

Hi @Anonymous ,

Do you want to submit 7 items into your second List ('Resource Available Hours') once your "Resource Details" form has been submitted successfully?

Based on the needs that you mentioned, I think the "Repeating Table" functionality using Gallery could achieve your needs. On your side, you could consider configure your Gallery as a "Repeating Table" with 7 items/rows.

Please check and see if the following video could help in your scenario:

https://www.youtube.com/watch?v=xgznk4XlPCo&t=33s

https://www.youtube.com/watch?v=DylxsXIUyDc&t=8s

https://www.youtube.com/watch?v=HUX_0AA4-Hs&t=1s

On your side, you could consider configure your Gallery as below:

Set the OnStart property of the App to following:

ClearCollect(
             TemporaryCollection,
             Defaults('Resource Available Hours List'),
             Defaults('Resource Available Hours List'),
             Defaults('Resource Available Hours List'),
             Defaults('Resource Available Hours List'),
             Defaults('Resource Available Hours List'),
             Defaults('Resource Available Hours List'),
             Defaults('Resource Available Hours List')
);
ForAll(
       TemporaryCollection,
       Collect(
               NewEntryCollection,
               {
                 Index: CountRows(NewEntryCollection),
                 'Employee ID': TemporaryCollection[@'Employee ID'],
                 Date: TemporaryCollection[@Date],
                 'Available Hours': TemporaryCollection[@'Available Hours'],
                 'Chargeable Hours': TemporaryCollection[@'Chargeable Hours']，
                 Duration: TemporaryCollection[@Duration],
                 Comments: TemporaryCollection[@Comments],
                 'Deviation Hours': TemporaryCollection[@'Deviation Hours']
               }
       )
)

Set the Items property of the Gallery to following:

If(
   EditForm1.Mode = FormMode.Edit,
   Filter('Resource Available Hours List', 'Employee ID' = BrowseGallery1.Selected.'Employee ID'),  // BrowserGallery1 represents the Gallery in your app to list all "Resource Details" records
   NewEntryCollection
)

Within the Gallery, set the DefaultDate property of the DatePicker control to following:

If(
   !IsBlank(ThisItem.Date),
   ThisItem.Date,
   DateAdd(StartDatePicker.SelectdDate, Index)
)

Set the OnSuccess property of your Edit form (connected to your "Resource Details" List) to following:

If(
    IsBlank(
             LookUp('Resource Available Hours', 'Employee ID' = EditForm1.LastSubmit.'Employee ID')
    ),
    ForAll(              // Add 7 new items into your second list
       RepeatingTableGallery.AllItems,
       Patch(
             'Resource Available Hours',
             Defaults('Resource Available Hours'),
             {
               Date: DatePickerInRepeatingTable.SelectedDate,
               'Available Hours': AvailableHoursTextInputBox.Text,
               'Chargeable Hours': ChargeableHoursTextInputBox.Text,
               Duration: DurationDropdownBox.Selected,
               Comments: CommentsTextInputBox.Text,
               'Deviation Hours': DeviationHoursTextInputBox.Text
             }
       )
   ),
   ForAll(          // edit existing 7 items related to current Employee Id in your seocnd List
       RepeatingTableGallery.AllItems,
       Patch(
             'Resource Available Hours',
             LookUp('Resource Available Hours', 'Employee ID'= EditForm1.LastSubmit.'Employee ID' && Date = DatePickerInRepeatingTable.SelectedDate),
             {
               Date: DatePickerInRepeatingTable.SelectedDate,
               'Available Hours': AvailableHoursTextInputBox.Text,
               'Chargeable Hours': ChargeableHoursTextInputBox.Text,
               Duration: DurationDropdownBox.Selected,
               Comments: CommentsTextInputBox.Text,
               'Deviation Hours': DeviationHoursTextInputBox.Text
             }
       )
   )
)

Please take a try with above solution, then check if the issue is solved.

Best regards,

[WarrenBelz]

Hi @Anonymous ,

A couple of options here - I am happy to continue as per your private message request that triggered my response to you.

[Anonymous]

Thank you so much for your help. The youtube links have helped me a lot to clarify my doubts and fix my silly mistakes here and there.

Also the way you have explained by creating form is great and helped me visualize along with the script it gave me understanding of how to write and where to write the script.

Thank you so much @v-xida-msft 

[Anonymous]

@WarrenBelz  Thank you for your guidance. I have created multiple screens, added newform to one and gallery to another screen. Linked two SP list data sources with Newform and as you said navigated to different screen for the gallery and added submit to it.

I learnt many things from ShaneYoung https://www.youtube.com/watch?v=xgznk4XlPCo&t=33s channel which was shared by @v-xida-msft and this helped me a lot to add repeated rows. 

I have fixed all issues, form is ready and working fine. It saved data in both the list.

@WarrenBelz , @v-xida-msft  and @Shanescows  I need little more help.

1) requirement is no. of rows are fixed and all 7 rows to be shown at the time whenever a new form or edit form is opened. I don't need a save button in each row, I can write the save button patch function in submit button also  same as shown by @Shanescows in the above video.

2) I want to assign some blank value to Date field in the ClearCollect, please let me know how to do, when I try to make it blank, it becomes text datatype and it causes issue. I don't understand this error "invalid ."

3) I  have written below script on Start Date field which shows current week's monday date or next week's monday date based on selection of the radio button "If(SelectWeekDD.Selected.Value = "Current Week", Today()- Weekday(Today(),MondayZero),Today()+7- Weekday(Today(),MondayZero))" I got stuck after this scenario that based on this the 7 rows date field in the gallery should change accordingly, example first row's date field should show Monday date, next row Tuesday date and so on last row date field show Sunday date.

I am not getting where should I add the functionality as when I try do some change , the ForAll starts showing error or Patch shows error , let me know the correct script or way to do it based on Start Date field.

4) I need a search button which filters data and shows data combining both the list in a dashboard, how to add filter. If any video link for it.

5) I need a report functionality to be build, so I though of using DataTable in power apps from both the resources, Am I right? How do I export this to excel in the desired way?

Please find attachment of NewForm for more clarity.

Thank in advance for your help. 

[WarrenBelz]

Hi @Anonymous ,

Firstly if my guidance also assisted, you have the option of also accepting this as a Solution (you can accept more than one) - it helps others find the parts more easily.

Secondly, your additional requirements would better be placed on a new thread to better categorize it. If you tag me on this, I will respond.