cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Christiaan1981
Frequent Visitor

Check if item in collection is the same as in datasource

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

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User II
Super User II

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._1.png

_2.jpg

View solution in original post

6 REPLIES 6
Spamrancher
Helper II
Helper II

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?

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: 

Christiaan1981_0-1615045884826.png

 

Christiaan1981
Frequent Visitor

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. 

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
Super User II
Super User II

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._1.png

_2.jpg

View solution in original post

Christiaan1981
Frequent Visitor

@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
)

 

 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (93,132)