Offline App - Removing items from a SharePoint list on a timer control which checks for connectivity

[ShawnPOWER]

I currently have an offline app with a timer control and on the timer control end it does a check on all my offline collection items for specific status like OfflineEdit, OfflineNew, or OfflineDelete. Then I do the related action so New would be a Patch with defaults, Edit would be a Patch filtering on the ID of the item I want to update, which all works great! The delete path of my if statement seems to be the problem. When I go to Remove from the SharePoint Online list filter based on the ID, it doesn't seem to want to function. To test it out today I just put a button out there with an OnSelect of the ForAll portion I want to test and I can't even get the formula to save in my editor. What am I doing wrong here (code for the button below)? I appreciate the help in advance.

colLocalChanges was built from spoList via a ClearCollect(colLocalChanges, spoList) which contains a single line of text column called SyncStatus. The confusing part I have always struggled in this formula is the ID and ID are the same column name for both data sources which makes this extra confusing, not sure if I can define them more so it makes more sense looking at it or not?

ForAll(
    colLocalChanges,
    If(
        "Delete" in SyncStatus,
        Remove(
            spoList,
            First (
                Filter(
                    spoList,
                    ID = ID
                )
            )
        )
    )
)

[PowerAddict]

One thing you can do is when you are populating your colLocalChanges, you can add a new column called something like LocalID and then drop the ID column. Your below formula will then become clearer. Let me know if you are still having issues. 

Here are examples of Add and Drop column. 

AddColumns( IceCreamSales, "Revenue", UnitPrice * QuantitySold )

DropColumns( IceCreamSales, "UnitPrice" )

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

[ShawnPOWER]

Hi @PowerAddict, great idea on making it look better. I haven't actually ever added a column before. Do you have to remove it before you patch the row back into the data source? I am guessing that is true just wanted to confirm.

I was successfully able to do the below which seems to work today and reads better I think. You can do that in a First(Filter( type situation also it looks like. https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/How-does-the-quot-quot-notation-wor... (explains the "@" symbol in the formula, hopefully I am using it correctly here).

ForAll(
    colLocalChanges,
    If(
        "Delete" in SyncStatus,
        RemoveIf(
            spoList,
            ID = colLocalChanges[@ID]
        )
    )
)

[PowerAddict]

You don't necessarily have to drop the column. It just makes it cleaner. 

The rest of it looks good. Do you have any other questions? 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

[ShawnPOWER]

Hi @PowerAddict, I am only perplexed now by the fact I can't seem to put the remove section of my If statement on another collection I have. It is odd it is the same process but one collection works I have and not another. Same if statements and everything, same basic process and all. I can put another If statement outside of my existing If statement and all works great but within it on this other collection doesn't work for some reason. On my If statement where I have 2 other logic tests and true values, when I add this third logic test and true statement to remove the item from the collection I get a message about "Invalid argument type (Table). Expecting a Record value instead." and in my other 3 logic test and true values it all worked out great as I mentioned same remove statement and all. Not sure what is going on there as I am taking the same approach.

[PowerAddict]

Can you share the code for both the situations? It might be easy to point out the issue then.

[ShawnPOWER]

Hi @PowerAddict, code would be good, sorry about that. I simplified the code a bit here and changes some variable names to keep with the thread but it is a good representation of my code in the app I think.

If(
    Connection.Connected,
    ForAll(
        colLocalChanges,
        If(
            "New" in SyncStatus,
            Collect(
                colPatchedChanges,
                Patch(
                    spoList,
                    Defaults(spoList),
                    {
                        ProjectNumber: ProjectNumber,
                        ProjectName: ProjectName,
                        SyncStatus: Blank()
                    }
                )
            ),
            "Edit" in SyncStatus,
            Patch(
                spoList,
                First(
                    Filter(
                        spoList,
                        ID = colLocalChanges[@ID]
                    )
                ),
                {
                    ProjectNumber: ProjectNumber,
                    ProjectName: ProjectName,
                    SyncStatus: Blank()
                }
            ),
            "Delete" in SyncStatus,
            RemoveIf(
                spoList,
                ID = colLocalChanges[@ID]
            )
        )
    );
//Above here works awesome today for some reason, didn't work the other day. The below is where I took the Delete logic test of of my larger if statement and it works fine. In the larger If statement I get the not a record error.
    ForAll(
        colLocalChangesUI,
        If(
            "Delete" in stillLocal,
            RemoveIf(
                spoList2,
                ID = colLocalChangesUI[@ID]
            )
        )
    );
    ForAll(
        colLocalChangesUI,
        If(
            "New" in stillLocal,
            Patch(
                spoList2,
                Defaults(spoList2),
                {
                    Title: Title,
                    DateOpened: DateOpened,
                    DateClosed: DateClosed,
                    stillLocal: Blank()
                }
            ),
            "Edit" in stillLocal,
            Patch(
                spoList2,
                First(
                    Filter(
                        spoList2,
                        ID = colLocalChangesUI[@ID]
                    )
                ),
                {
                    Title: Title,
                    DateOpened: DateOpened,
                    DateClosed: DateClosed,
                    stillLocal: Blank()
                }
            )
            /*
            ,
            //Had to move this outisde of the if statement for some reason as it stopped working. Unsure why it works outside the larger If statement here.
            "Delete" in stillLocal,
            RemoveIf(spoList2, ID = colLocalChangesUI[@ID])
            */
        )
    );
)

[Delid4ve]

ForAll(
Filter(AddColumns(colLocalChanges,"DeleteID",ID),SyncStatus = "OfflineDelete"),
RemoveIf(spoList,ID = DeleteID))

You can use similar for the updates -
ForAll(
Filter(AddColumns(colLocalChanges,"UpdateID",ID),SyncStatus = "OfflineEdit"),
UpdateIf(spoList,ID = UpdateID,{field1:field1,field2:field2...etc etc}))

Patch would be:
ForAll(
Filter(colLocalChanges,SyncStatus = "OfflineNew"),
Patch(spoList,Defaults(spoList),{field1:field1,field2:field2...etc etc}))

[ShawnPOWER]

Thank you @Delid4ve for the examples on the add columns approach.

I am still having issues with the If statements for some reason, haven't figured that out yet.