IfError - using with ForAll and Patch

[cccrosetti]

Hi, I just learned that UpdateContext wont work inside a ForAll loop, as my intention is to set a flag when an IfError is sorrounding a Patch call inside the ForAll loop.

I placed the IfError outside the ForAlll ->   IfError(ForAll(Patch(..)), UpdateContext()), and seems that is ignoring the failure I on purpose cause by disconnecting the laptop from the network to make Patch fail.

I would appreciate sending a link to an article showing how to detect Patch() exceptions inside a ForAll based iteration loop, thanks in advance, Carlos

[RandyHayes]

@cccrosetti 

So the thing to keep in mind is that a ForAll is not really a For...Loop.  It is something that returns a table based on what you "forAll".

So, for example, if you do a 

UpdateContext({results: ForAll(someSource, Patch(somePlace, Defaults(somePlace), {some:Data}).ID)})

You will then have a variable called results that will have a single column called ID that will be the ID of each patch record.

So, if you wanted a list of records with errors...let's say you have a collection (colX) with 3 rows in it that you want to update (ID's 1-3), and you want to check the error, you could do the following:

UpdateContext({patchResults: 
    ForAll(colX as source, 
        {rowID: source.ID,
         error: IfError(Patch(yourDataSource, yourDataSourceRecord, {yourDataColumn:"SomeValue"}), true)
        }
    )
})

This will give you a table of each rowID and error result (true or false) in the variable patchResults.

You can then use this to further process what you need.

I hope this is helpful for you.

[TickboxPhil]

Hey @RandyHayes thanks for this! Am trying to use your code on a Gallery (a very frequent place for ForAll to be enlisted with Patch, despite ForAll not being a true iterator). However, am struggling without the documentation - very close to yours is:

UpdateContext({patchResults: 
    ForAll(galTimeEdit.AllItems As source,
        {rowID: source.TimeID,
         error: IfError(Patch('[testing].[tbl_Time]', LookUp('[testing].[tbl_Time]' As T, T.TimeID = source.TimeID), {StartDate: DateValue(txtStartDate.Text)}), true)
        }
    )
})

It's just trying to update the StartDates for each TimeID. I hate the Patch Lookup but documentation is patchy (sorry!) here too and it seems one of the main ways to pin down the single record.

Unfortunately it gives an error:

The IfError has some invalid arguments - invalid argument type (Boolean). Expecting a Record value instead.

Have fiddled around with it, also realised IfError is apparently part of "Formula-level Error Management" Experimental Features so set that to allowed (and restarted). Still no joy. Any ideas? Thanks, Phil.

[RandyHayes]

@TickboxPhil 

Have you considered using IsError instead of IfError?  IsError will return true or false if the formula is in error.

UpdateContext({patchResults: 
    ForAll(galTimeEdit.AllItems As source,
        {rowID: source.TimeID,
         error: IsError(
                   UpdateIf('[testing].[tbl_Time]', 
                       TimeID = source.TimeID, 
                       {StartDate: DateValue(txtStartDate.Text)}
                   )
                )
        }
    )
})

[TickboxPhil]

Many thanks @RandyHayes - that indeed does work! I prefer the UpdateIf syntax too, but again, it's not clear from documentation when you should use it over a Patch.

Hopefullly @cccrosetti will mark you as an answer, although I'm pretty sure we would all like to pull in the actual error message returned from target, rather than just true or false, so we know why the rejection! I do appreciate every datasource is different, but that's the point of connectors, especially Premium ones we pay for. Would be great if you can confirm if you can do that for SQL (or even SP) or if it's on the cards? Because Microsoft support themselves referred me here, I'm afraid they're not being told either!

[RandyHayes]

@TickboxPhil 

There's no specific documentation on when to use one over the other.  I prefer the UpdateIf when dealing with a single record or a group of records with similar matching criteria. 

NOTE: UpdateIf returns a table of the datasource with the updated record.  For those that use collections, this is important to note as you can use the output of the UpdateIf to refresh the collection.  If you just want the record you updated from the UpdateIf, you can use a LookUp on the UpdateIf  ex. LookUp(UpdateIf(someDataSource, ID=5, {some record}), ID=5)  This will return the updated record only.

Patch puts records together from left to right.

Patch(record1, record2, record3)  will combine all three records into one record with overriding values replaced from left to right.  Patch also allows a datasource (table) to be used as the first parameter.  The concept though is still the same...patch left to right.  So, the reason you'll see Patch(dataSource, LookUp(...)  is because the LookUp is going to have to return a record that will be the next param and then it will combine the record as mentioned above, and, because it recognizes the dataSource, it will then also patch the identical record in the datasource.

This process takes two steps, one to lookup the record and then another to update it.  As opposed to UpdateIf that will do it in one shot based on the criteria of the UpdateIf.

One other distinction...you cannot use UpdateIf to create a record, you must do a Patch and supply the Defaults of the datasource (this will be a blank record with any default values in it).

As for the error...you can actually go about it another way:

UpdateContext({recordErrors:
    With({patchResults: 
        ForAll(galTimeEdit.AllItems As source,
            {rowID: source.TimeID,
             result: LookUp(
                       UpdateIf('[testing].[tbl_Time]', 
                           TimeID = source.TimeID, 
                           {StartDate: DateValue(txtStartDate.Text)}
                       ),
                       TimeID = source.TimeID
                    )
            }
        )
        },

        ForAll(patchResults, 
            {error: Errors('[testing].[tbl_Time]', result)}
        )
    )             
})

This will set a context variable with all the records and their individual table of errors (if any).  You would probably want to even further filter that for only records that have errors.

[TickboxPhil]

Excellent @RandyHayes - that works and gets right down to it! It ran straight on my table, but does suffer the bug ("by design" no doubt...) that it updates ALL records with a single field change (which is horrifically dangerous), not just the changed record in the Gallery. Have tested and this happens with Patch as well UpdateIf, so I believe it needs the same hacky trick: RenameColumns():

UpdateContext({recordErrors:
    With({patchResults: 
        ForAll(RenameColumns(galTimeEdit.AllItems, "TimeID", "X_TimeID"),
            {rowID: X_TimeID,
             result: LookUp(
                       UpdateIf('[testing].[tbl_Time]', 
                           TimeID = X_TimeID, 
                           {StartDate: DateValue(txtStartDate.Text)}
                       ),
                       TimeID = X_TimeID
                    )
            }
        )
        },

        ForAll(patchResults, 
            {error: Errors('[testing].[tbl_Time]', result)}
        )
    )             
})

It would be cleaner to use "galTimeEdit.AllItems As source" and then source.TimeID like you did originally, but again, that still updates ALL records (facepalm!). Would like to have all this addressed somewhere... but for now, your code as amended above does indeed load the errors into recordErrors, woohoo! Have tested by putting a Check Constraint on the SQL Table of StartDate < getdate() ie must be earlier than today.

One more thing though - it's not the full error only the first non-specified bit (I appreciate you'll roll your eyes now!):

Would be ideal to get the full error, even if all text, otherwise just the key part:

Actually, maybe the recordErrors does have the whole text but by chance only shows the first sentence - difficult to see as I don't (yet) have code to drill into Variable Table of nested Tables... will have a dig for that!

OK, I don't expect anymore, you have done an amazing job against all odds here frankly, and it is enough to trap individual ForAll() errors which is what @cccrosetti and any self-respecting Power Apper wants. You are what makes us stick with this!