Check if item in collection is the same as in datasource

[Anonymous]

Hi, 

I am trying to show a check icon in a gallery if a value in a collection or textbox matches a value in an excel datasource (Table4), or if it doesn't match, a cancel icon. The goal is to show the user if there are any unsaved changes. I am still a bit new to powerapps so I don't understand some of the functions that well yet. I hope someone can help as I think this should not be a very difficult function.

- The gallery Items property is colShifts.

- texShiftInput.Text patches to the collection colShifts.Shift, which is saved to Excel Table4 collumn Shift.

- It would be nice if this formula also only applies if the texShiftInput.Text is not blank. 

What I think I need would be: 

If(And(!IsBlank(texShiftInput.Text),ThisItem.Shift = X),Icon.Check,Icon.Cancel) where X is the value of the shift in Excel Table 4.

What is the correct function for that? 

Here are some functions I tried to no avail... 

If(colShifts.Shift=ThisItem.Shift, Icon.Check, Icon.Cancel)

If(LookUp(Table4, Shift = texShiftInput.Text)), Icon.Check, Icon.Cancel)

If(And(!IsBlank(texShiftInput.Text),(LookUp(Table4, Shift = texShiftInput.Text))), Icon.Check, Icon.Cancel)

If(ThisItem.Shift=(!IsBlank(LookUp(Table4, ThisItem.Shift = colShifts.Shift, Icon.Check, Icon.Cancel))))

This one works, but as soon as the selection of a textinput box changes, it patches the collection so it matches again. 

If(And(!IsBlank(texShiftInput.Text),ThisItem.Shift = texShiftInput.Text),Icon.Check,Icon.Cancel)

Any help would be greatly appreciated. 

Christiaan

[Spamrancher]

If I'm understanding things correctly, you're trying to validate that the text in your textbox a) is not blank and b) matches the shift in the Excel table, not the collection (colShifts). Where are you applying this formula? Is it the OnChange of the TextInput? Also, where is your Patch that it is constantly updating your collection? Can the Excel table be updated externally after you Collect colShifts; meaning that Table4 and colShifts may not be in sync?

[Anonymous]

Yes your assumtion is correct, as the textinput box patches automatically onchange to the collection. Once the user then clicks a save button it patches the entire collection to the excel table. So it would also be fine if the function validates that the text in the collection matches the shift in the excel table.

I am applying this formula in an icon in the same gallery row. The icon property of the icon should change to a check icon if the shift matches, or a cancel icon if it does not match. 

This would let the user know that they forgot to save their shift. 

Something like this: 

[Anonymous]

Can the Excel table be updated externally after you Collect colShifts; meaning that Table4 and colShifts may not be in sync?

Normally it will only not be in sync if the user changes the textinput (which is autopatched to collection) but has not yet saved the collection to be patched to Excel. 

[Spamrancher]

Since your collection is always being patched, what you need to to is validate that your ThisItem.Shift record is or is not different than what your Excel table shows. In your example above, if someone changes 9 to 7, that will immediately patch your collection. What I would suggest is when you create the collection that you patch, you also create a read-only collection that you can then compare against for differences. 

[Drrickryp · Accepted solution]

Hi @Spamrancher 

First, add an ID column to your table.  It could be a sequence of unique numbers that are not reused if a row is deleted.  Then do a ClearCollect(colShifts,Table4) to restore your collection,

Add a checkbox in your gallery change the name from "option" to "Match" and set the default property to 

LookUp(Table4,ID=ThisItem.ID,Shift)=ThisItem.Shift

In the example below, I used a fictitious contact list called FakeNames and created a collection for the gallery.  I wanted to correct misspellings in the first name field.  The default of the TextInput box is ThisItem.first_name.  The other functions for the controls are shown.  The first name for Penny Weight was misspelled and I corrected it but didn't save it yet.  The save icon saves the corrected name to the datasource and the checkbox becomes true reflecting the collection and the datasource matches.

[Anonymous]

@Spamrancher @Drrickryp Thanks for both of your replies, they both helped. I managed to get it to work! 

I did collect 2 collections as Spamrancher suggested, so that I could validate against a read-only collection, since it could be that the excel is externally edited and I do not want that to confuse the user that they didn't save. 

And I used Drrickryp's function to check the read-only collection after adding a ShiftID: 

LookUp(colShiftReadOnly,ShiftID=ThisItem.ShiftID,Shift)=ThisItem.Shift

And I set the icon's icon property to check if the shift input field is blank, and to validate 2 lookup values:

If(
And(
!IsBlank(texShiftInput.Text),
And(
chkShiftSaved.Value = true,
chkMusthaveSaved.Value = true
)
),
Icon.Check,
Icon.Cancel
)