Check if a record already exists

[common_user]

Hi to all,

I have a gallery with checkboxes in every row, I have a selectedRow collection.
Outside the gallery I have a button, I want to Patch the record in my list if the record don't exist or modify the specific record, I'm trying to use this code

ForAll(
    selectedRow;
    If(
        IsBlank(LookUp(IDShadowList; IDshadow = ID));
        Patch(
            SPlist;
            {
                Title: ThisRecord.title;
                IDshadow: ThisRecord.ID
            }
        );
        Notify("Record already exists"; NotificationType.Error)
    )
)

it don't work, how can I modify it?

[Ethan_R]

Hi @common_user ,

You can try the following code:

ForAll(
    selectedRow;
    With(
      {
        CurrentRecord: LookUp(IDShadowList; IDshadow = ThisRecord.ID; ThisRecord)
      },
      If(
        IsBlank(CurrentRecord);
        Patch(
            SPlist;
            Defaults(SPlist);
            {
                Title: CurrentRecord[@title];
                IDshadow: CurrentRecord[@ID]
            }
        );
        ,
        Patch(
            SPlist;
            CurrentRecord;
            {
                Title: CurrentRecord[@title];
                IDshadow: CurrentRecord[@ID]
            }
        );
      )
    )
)

Does this help?

[timl]

@common_user 

You're missing a call to Defaults in the section where you're adding a new record.

ForAll(
    selectedRow;
    If(
        IsBlank(LookUp(IDShadowList; IDshadow = ID));
        Patch(
            SPlist;
            Defaults(SPlist);
            {
                Title: ThisRecord.title;
                IDshadow: ThisRecord.ID
            }
        );
        Notify("Record already exists"; NotificationType.Error)
    )
)

This is how you'd adapt the formula to modify an existing record.

ForAll(
    selectedRow;
    If(
        IsBlank(LookUp(IDShadowList; IDshadow = ID));
        Patch(
            SPlist;
            Defaults(SPlist);
            {
                Title: ThisRecord.title;
                IDshadow: ThisRecord.ID
            }
        );
        Notify("Record already exists"; NotificationType.Error);;
       Patch(
            SPlist;
            LookUp(IDShadowList; IDshadow = ID);
            {
                Title: ThisRecord.title
            }
        )
    )
)

[common_user]

Thank you @Ethan_R, @timl.

@Ethan_R your code don't work because I can't use ThisItem, my button is outside gallery.

@timl I tryied something as like your code but I have this error

Fetching items failed. Possible invalid string in filter query.  Create collection based on the column values in a sharepoint list

in the LookUp function, do you have an idea?

[common_user]

@WarrenBelz can you help me please

[WarrenBelz]

@common_user ,

You already have two highly knowledgeable people helping you - please look at their advice.

[common_user]

I don't have the previous problem. I use the follow code:

ForAll(
    selectedRow;
    If(
        CountIf(
            SPList;
            IDshadow = LookUp(selectedRow; IDshadow = ID).ID
        ) > 0;
        Patch(
            SPList;
            LookUp(SPList; IDshadow = LookUp(selectedRow; IDshadow = ID).ID);
            {
                Scelta: "DELETE"
            }
        );
        Patch(
            SPList;
            Defaults(SPList);
            {
                Title: ThisRecord.Title;

                Choice: "DELETE";

            }
        )
    )
)

so it work but I have a little problem on the true if value. If I have a record that already exists, If I selected 3 already exists rows, my code change only first row.
How can I change this?

Thank you all

[timl · Accepted solution]

@common_user 

You can update multiple rows by calling UpdateIf

ForAll(
    selectedRow;
    If(
        CountIf(
            SPList;
            IDshadow = LookUp(selectedRow; IDshadow = ID).ID
        ) > 0;
        UpdateIf(
            SPList;
            IDshadow = ID;
            {
                Scelta: "DELETE"
            }
        );
        Patch(
            SPList;
            Defaults(SPList);
            {
                Title: ThisRecord.Title;

                Choice: "DELETE";

            }
        )
    )
)

[common_user]

Hi @timl, it works correctly!

I changed the condition in UpdateIf because with your code it don't find any ID.

ForAll(
    selectedRow;
    If(
        CountIf(
            SPList;
            IDshadow = LookUp(selectedRow; IDshadow = ID).ID
        ) > 0;
       UpdateIf(
            SPList;
            IDshadow = LookUp(selectedRow; IDshadow = ID).ID;
            {
                Choice: "DELETE";
            }
        );
        Patch(
            SPList;
            Defaults(SPList);
            {
                 Title: ThisRecord.Title;
                Choice: "DELETE";

            }
        )
    )
)

with the new condition it works and change the already exists record.

Are you agree?
Thank you for your support

[common_user]

@timl I have last question.
Do you know if it is possible to use a not delegable function to replace countIf?

Thank you