Add records in bulk with one common column value

[KnowledgeSeeker]

Hi Community,

I am trying to create a canvas app to mark the people who attended an event, my data source is an Excel workbook described as below:
Table (1): List of existing groups and members of each, the same person can belong to different groups
Table (3): is the destination source where the attendance will be stored

The issue I am facing here is when I created a form based on Table3, and switched to New Form mode what I got is just the ability to mark the presence individually while what I am trying to do is to get the list of members according to the selected group in the dropdown list, then mark the presence by using the toggle and save the whole data in Table 3 (that data is what I am trying to get at the end)

Is there any way to achieve that ? Any help would be appreciated.

thank you in advance 

[mdevaney]

@KnowledgeSeeker 
Create a Submit button for your form and put this code inside the OnSelect property to insert all of the new rows into your Excel worksheet.

// create a collection having all the records you want to insert
ClearCollect(colEventAttendance,
    ShowColumns(
        AddColumns(
            Gallery1.AllItems,
            "Date",  DatePicker_Date.SelectedDate,
            "Group", Dropdown_Group.Selected.Value,
            "Name",  Label_Name.Text,
            "Attendance (Y/N)", Toggle_Attendance.Value
        ),
        "Date", "Group", "Name", "Attendance"
    )
);

// insert the new rows into the workbook
Collect(your_excel_workbook, colEventAttendance)

---
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."

[v-xida-msft]

Hi @KnowledgeSeeker ,

According to the needs you want to achieve, I think your second screenshot is in right direction.

On your side, you could consider add a Dropdown control to list available Group Names, and then display related Students based on the selected Group in a Gallery control.

I have made a test on my side, please consider take a try with the following workaround:

Add a Dropdown control (Dropdown1) in your app screen, set the Items property to following:

Distinct(Table1, ID_Group)

Add a DatePicker control in your app screen, to collect the date.

Add a Gallery control in your app screen, set the Items property to following:

Filter(Table1, ID_Group = Dropdown1.Selected.Result)

within this Gallery, add a Toggle control, set the OnCheck property of the Toggle1 to following:

Patch(
      Table3,
      Defaults(Table3),
      {
         Date: DatePicker1.SelectedDate,
         Group: Dropdown1.Selected.Result,
         Name: ThisItem.Student,
         'Attendance (Y/N)': "Y"
      }
)

set the OnUncheck property of the Toggle1 to following:

Patch(
      Table3,
      Defaults(Table3),
      {
         Date: DatePicker1.SelectedDate,
         Group: Dropdown1.Selected.Result,
         Name: ThisItem.Student,
         'Attendance (Y/N)': "N"
      }
)

set the Default property of the Toggle1 to following:

If(
   ThisItem.'Attendance (Y/N)' = "Y",
   true
   false
)

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

Best regards,.

[mdevaney]

@v-xida-msft 
I would like to ask: did you consider app performance in your answer?  Patching everytime a user clicks a toggle button is going to make the app really slow.

Also, did you consider the amount of API calls this requires?  Your method will consume 1 API call per person.  There are a limited number of PowerApps API calls allowed per day.

@KnowledgeSeeker 
For these reasons above, I highly suggest you write to the database only once using a button at the bottom of the form rather than once per person like @v-xida-msft is suggesting.

---
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."

[KnowledgeSeeker]

Hi Community,

Thank @mdevaney  and @v-xida-msft  for answering my question.

I combined the both solutions to create the application, below the schema I am using

I used a gallery to display the members (Table1) according to the selected Group (Table1) in the dropdown list.

I altered the gallery by adding a toggle which will be used to mark the presence.

I created a button (add) which will create a collection and gather all data (the selected date, selected group, the list of members and the attendance status) then stored in Table(2) (Table in right).

Patch function saves data every time I check or uncheck the toggle, Instead I used the collection method by I am running through the below error messages:

Any help would be appreciated.

Thank you in advance

[mdevaney]

@KnowledgeSeeker 
Change the attendance line to this code.  It will likely solve both problems. 

"Attendance (Y/N)", If(Toggle_Attendance.Value, "Yes", "No")

---
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."

[v-xida-msft]

Hi @KnowledgeSeeker ,

@mdevaney  for your solution, do you take the "Delegation" limits into consideration? Currently, the AddColumns function is not a Delegable function in PowerApps, it would be subject to the Delegation limit -- 2000 records. If the customer's data records is more than 2000, I think your solution would not work, right?

@KnowledgeSeeker If you want to save all Gallery Items back to your Excel table via pressing a button once time, I think the ForAll function and Patch function could achieve your needs directly instead of using AddColumns function. Please consider set the OnSelect property of the "Submit" button to following:

ForAll(
       Gallery1.AllItems,
       Patch(
             Table2,
             Defaults(Table2),
             {
               Date: DatePicker1.SelectedDate,
               Group: Dropdown1.Selected.Result,
               Name: Student_Name_Label.Text, // Student_Name_Label represents the Label in your Gallery1 to display the Student Name
               Attendance: If(
                                 Toggle1.Value = true,  // Toggle1 represents the Toggle inside your Gallery1
                                 "Y",
                                 "N"
                           )
            }
       )
)

Note: The ForAll function is not a Delegable function, it could only process 2000 records once time. But the data source you used is a Excel table, which is a Non-delegable data source in PowerApps app, you could only load first 2000 records into your canvas app from your Excel table, so you could ignore the Delegation issue when you use ForAll function. Make sure you have set the "Data row limit for non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

Please consider take a try with above solution, then check if the issue is solved. If the solutions I provided above could help in your scenario, please consider go ahead to click "Accept as Solution" to identify my reply as helpful.

Best regards,

[KnowledgeSeeker]

Hi Community,

I was looking for a way to view the collection items before storing them, once verified I would store all of them at once as @mdevaney mentioned before, but unfortunately errors kept popping up when I used the collection combined with ShowColumns and AddColumns.

Using Path and AllFor allows me to store the gallery items to Excel file, even though is seems to be slowing down, is there any way to enhance it ?

Thank you @v-xida-msft  and @mdevaney I do appreciate your time.

[v-xida-msft · Accepted solution]

Hi @KnowledgeSeeker ,

Firstly, the Excel table itself is essentially a "Slow" data source, which does not have any advantages in data processing than SharePoint List, CDS Entity, SQL Server, .... in PowerApps app.

Same ForAll formula applied to a SP List would be faster than Excel table in data processing. If you want to improve the data processing speed in your canvas app, please consider use other "faster" data source (e.g. SP List) to replace the Excel table data source.

More details about the performance tips in PowerApps, please check the following article:

https://baizini-it.com/blog/index.php/2017/09/13/powerapps-improving-app-performance/

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/performance-tips

https://powerapps.microsoft.com/en-us/blog/performance-considerations-with-powerapps/

Regarding to the needs that you mentioned, you could consider collect the entries into a collection in your canvas app firstly, then you could review the collected items in your app. Please try the following formula to collect items in your app:

Collect(
        TemporaryRecords,
        {
               Date: DatePicker1.SelectedDate,
               Group: Dropdown1.Selected.Result,
               Name: Student_Name_Label.Text, // Student_Name_Label represents the Label in your Gallery1 to display the Student Name
               Attendance: If(
                                 Toggle1.Value = true,  // Toggle1 represents the Toggle inside your Gallery1
                                 "Y",
                                 "N"
                           )
      }
)

then you could review the collected items through the TemporaryRecords collection directly.

Then If you want to patch the collected items back to your data source, please try the following formula:

ForAll(
       TemporaryRecords,
       Patch(
             Table2,
             Defaults(Table2),
             {
               Date: TemporaryRecords[@Date],
               Group: TemporaryRecords[@Group],
               Name: TemporaryRecords[@Name], // Student_Name_Label represents the Label in your Gallery1 to display the Student Name
               Attendance: TemporaryRecords[@Attendance]
            }
       )
)

Please consider take a try with above solution, check if it could help in your scenario.

Best regards,