Power Platform Community Forum Thread Details

Good morning all,

Hope you're all keeping healthy and safe.

Got a conundrum that I believe I'm about 90% of the way to solving, but the last bit is baffling me.

The task:

I want to create a log for any changes made to records in a gallery. This exists just for this session, to provide a reference of all changes made before someone presses Save Edits. The entries will look like this (ignore the error in the middle, just a pesky hidden button):

When I change an entry, i.e. change the name, or select a different department from the dropdown, it logs it in a collection with a category that shows its either a department or name change (changeCat):

The code for this, which sits in both the name field and dropdown field, and is it set to trigger OnChange, is this:

If(
 Dropdown3.Selected.Department = ThisItem.Department,
 RemoveIf(
 changeLog,
 ThisItem.ID in ID && "department" in changeCat
 ),
 If(
 CountRows(Filter(changeLog,ID = ThisItem.ID)) > 0 && "department" in changeLog.changeCat,
 UpdateIf(
 changeLog,
 "department" in changeCat && ThisItem.ID in ID,
 {change: ThisItem.'Staff Name' & " - Department updated to " & Dropdown3.Selected.Department}
 ),
 Collect(
 changeLog,
 {
 change: ThisItem.'Staff Name' & " - Department updated to " & Dropdown3.Selected.Department,
 changeCat: "department",
 ID: ThisItem.ID
 }
 )
 )
)

In the other field, it's pretty much the same, but fields changed to affect department instead.

So, if the department selected is the same as the one currently saved within the database, remove the field. If a different department has been selected, and if their currently isn't a record in there for a "department" change (changeCat) against this person's ID number, add a new row, else update the one that's already there.

I was previoiusly having massive issues with the remove and update statements affecting every record, but I figured out that if I reference the fields in, for example, the UpdateIf statement, as changeLog.changeCat, rather than just changeCat, it seems to to affect the whole collection rather than just the record in question. Still not sure why that's the case, but in the event you have the same issue, try removing the reference to the collection from before the field name in any function that requires you to state the collection as the first argument.

The problem:

Now, if I enter in two records of any one type of change into the collection, it stops logging. For example, I have changed two departments, and one name, and now the second name change (Claire to Blaire on the second row of the gallery) is not being saved:

Now, its does log a change to any record that has not had any changes yet, like below, but not any record that already has a single change logged:

Does anyone have any ideas why this is happening? I know I'm not doing something very obvious, but, for the life of me, I can't see what it is!

I hope that's enough information. Let me know if you need anymore.

Thanks

Matt

Categories:

General Questions

Using Apps Using Formulas

I found the solution! It was conditions I was using for the If statements. I don't think it was precise enough. So I created a set of hidden labels in the gallery over each field that concatenated the person's ID, and a letter to denote whether it was a department (d), name (n), or FTE (f) change. I then made sure that when a new record was added to the changeLog collection, it added in this new Unique ID. I then used this as the condition to search for in the If statements. My final code looked like this:

If(
 Dropdown3.Selected.Department = ThisItem.Department,
 RemoveIf(
 changeLog,
 DeptID.Text = UID
 ),
 If(
 DeptID.Text in changeLog.UID,
 UpdateIf(
 changeLog,
 DeptID.Text = UID,
 {change: ThisItem.'Staff Name' & " - Department updated to " & Dropdown3.Selected.Department}
 ),
 Collect(
 changeLog,
 {
 change: ThisItem.'Staff Name' & " - Department updated to " & Dropdown3.Selected.Department,
 changeCat: "department",
 ID: ThisItem.ID,
 UID: DeptID.Text
 }
 )
 )
)

I hope this helps someone else!