Update record from a gallery - substitute User().Email with blank

[Gellai_Tamas]

Hi All,

Issue is as follows:

I have a Sharepoint List multiple line plain text column (UserFavoritesProduct) where user emails are added/appended by clicking on a product's heart icon - a simple "save to favorites" feature in other words. It works. 

I then created a gallery to show the saved products and placed an unfav icon to be able to remove previously saved products. The idea is to update the record and substitute the User().Email with "" but it does nothing.

Here is the latest code I use (I tried basically everything with and without filters, lookups in the curly braces section etc.):

Update(
    'Sharepoint List',
    First(Filter('Sharepoint List',ID = FavProductsGallery.Selected.ProductID)),
    {
        UserFavoritesProduct: Substitute(
            ThisItem.UserFavoritesProduct,
            User().Email,
            ""
        )
    }
)

It doesn't throw an error, it just does nothing at all.

Any idea how I could remove the current user's email address from the UserFavoritesProduct column?

Thanks,

Tamás

[Kosenurm]

Try Replace instead of Substitute...

Update(
    'Sharepoint List',
    First(Filter('Sharepoint List', ID = FavProductsGallery.Selected.ProductID)),
    {
        UserFavoritesProduct: Replace(
            ThisItem.UserFavoritesProduct,
            Find(User().Email, ThisItem.UserFavoritesProduct),
            Len(User().Email),
            ""
        )
    }
)

In this formula, the Find function is used to locate the position of the current user's email address in the "UserFavoritesProduct" column, and the Len function is used to determine the length of the email address. The Replace function is then used to remove the email address from the column by replacing it with an empty string.

Note that the Replace function will only remove the current user's email address from the column if it is present in the "UserFavoritesProduct" field. If the email address is not present, the formula will not make any changes to the column.

[Gellai_Tamas]

Hi @Kosenurm ,

Thanks for your suggestion - and a special thanks for the lightspeed response! 🙂 Highly appreciated!

I'm pretty sure your code should work - but it doesn't. It runs but does nothing eventually.

The code throws an odd error and that might be the explanation: it requires some field called 'ID'. There are 2 problems with that:

- there is no such a field name (I checked in Sharepoint --> List settings --> Column settings --> URL: "Field=xxx")

- none of the list columns are required

The primary mistake was that I imported the data from an Excel file into the SP list and that always messes up field names. Yet, it's still strange that it wants me to add a field which is not there...

Any suggestion to tackle this?

Cheers!  

[iAm_ManCat · Accepted solution]

Hey @Gellai_Tamas 

I can explain what's going wrong here, I don't think the chatGPT answer you got before actually solves anything

Ok, so first of all, your question regarding the ID column - this is auto-generated by SharePoint, it exists in every list and is not a column listed in the settings - you can show/hide it from the list view when looking at a list. It is the identifier of that row number, and is considered the primary key for the list.

Secondly, you are using Update as a function, which is for updating WHOLE records, not for modifying individual properties, so as you are not including all other columns it won't do anything as the Update function requires their schema to match (all columns listed) but you want to only update one property, so you need to use Patch instead

Thirdly, don't use Replace like @Kosenurm suggested - this only replaces a single instance of text, whereas Substitute replaces any instances - this will be important as you mentioned you are only appending to a multi-line which could potentially have a name appended to more than once if anything goes awry.

So your code should now look like this - could you try this and let me know if that's resolved it for you?

Patch(
    'Sharepoint List',
    LookUp('Sharepoint List',ID = ThisItem.ID),
    {
        UserFavoritesProduct: Substitute(
            ThisItem.UserFavoritesProduct,
            User().Email,
            ""
        )
    }
)

[Gellai_Tamas]

Hi @iAm_ManCat ,

Bang! It works!

Hm, I didn't think the auto-generated SP 'ID' field would interfere.

What I did think is that Patch could/should be the option instead of Update (just like I used Patch with writing in the email into the field) - not sure though why I haven't tried...

Anyway, thanks a bunch, your solution was a pill for a 3-day headache. 🙂 

[iAm_ManCat]

Happy to help, I know how difficult it can be to get out of a stuck position when coding - always good to have a second or third pair of eyes on something 🙂

[Gellai_Tamas]

Hi @iAm_ManCat ,

I'm back to bother you again. 🙂

The above solution works inside a gallery - but I'm wondering whether you have an idea on how to solve this outside a gallery.

The scenario: the user chooses a product from a gallery, then navigates to the product's detail screen, where there are a bunch of ProductGallery.Selected.FieldName controls (texts and images).

I've placed the fav/unfav icons not only into the gallery but into this product detail screen as well so that the user can save/unsave the product into his/her favorites. Unfortunately I cannot make it work in this context.

I've tried with variables and with the With() function but the only thing I achieved with the latter is that it adds a new, blank record into the SP list with my email address in the respective column.

With(
   {
      wFavProduct:
      LookUp(
         'Sharepoint List',
         ProductName = ProductGallery.Selected.ProductName
      ).UserFavoritesProduct
   },
Patch(
    'Sharepoint List',
    {UserFavoritesProduct: wFavProd & User().Email & Char(13)}
)
)

Solving this, I'll owe you a big glass of beer. 🙂

[Gellai_Tamas]

Nevermind, it's been solved with a second lookup.

Patch(
    'Sharepoint List',
    LookUp('Sharepoint List', ProductName = ProductGallery.Selected.ProductName),
    {
        UserFavoritesProduct: Substitute(
            LookUp('Sharepoint List', ProductName = ProductGallery.Selected.ProductName).UserFavoritesProduct,
            User().Email & Char(13),
            ""
        )
    }
)

[iAm_ManCat]

Ah nice, I've just seen you solved that - I've been offline today as had some things to do so only just saw your messages - glad to see you got it sorted!