Unable to update a record based on a lookup in another table (using ForAll)

[dBrand]

I am looking to update a field in a collection (coll_Web_Service_Connections) based upon the value looked up in another table (coll_webServices). I am trying to do this for all records in the coll_web_Service_Connections table. 

I thought using ForAll would work. It said I could not update a record in the ForAll table being referenced. So, I made a copy of the collection (coll_Temp_Web_Connections). But I am still not able to properly reference the field to update in the coll_WebServices (bolded below).

Maybe should be using the 'With' statement, but I don't really understand how to use it. This should be a lot easier that it appears to be. I've spent all day trying to figure this out. Any help would be appreciated! Thanks.

ForAll(
  coll_Temp_Web_Connections As wsc,
  Patch(
    coll_Web_Service_Connections, 
    LookUp(
      coll_WebServices As ws,
      ws.Object_ID = wsc.'Oject ID' &&
      ws.ObjectName = wsc.'Object Name' &&
      ws.Object_Type = "Page"
    ),
    {
       'Service Name': Service_Name
    }
    )
)

the Patch doesn't seem to know how to reference the field from the For

[Ethan_R]

Hi @dBrand ,

Is your 'coll_webServices' a Table with 1 record always? 

Since you are trying to fetch the record to be patched using lookup which will always return a single value. If your collection contains only 1 value always then your formula will be something like:

ForAll(
    coll_Temp_Web_Connections As wsc,
    With(
        {
            CurrentRecord: ThisRecord,
            LookupRecord: LookUp(First(coll_WebServices) As ws, ws.Object_ID = wsc.'Oject ID' && ws.ObjectName = wsc.'Object Name' && ws.Object_Type = "Page", ThisRecord)
        },
        {
            Patch(
                coll_Web_Service_Connections,
                LookupRecord,
                {
                    'Service Name': Service_Name
                }
            )
        }
    )
)

I hope this helps

[dBrand]

coll_webServices is a table with many records, but should only match to one record based on my lookup conditions.

[Ethan_R]

Okay, so remove the First() from the lookup and try the code. 
Let me know if there's error

[dBrand]

Getting error on: CurrentRecord: ThisRecord,

Says Name isn;t valid. 'ThisRecord' isn't recognized

[Ethan_R]

Ohh, 
My bad, Replace ThisRecord to wsc and check
Since I don't use As operator, I tend to write ThisRecord everywhere. 

[dBrand]

Seems close. Now getting:

[dBrand]

I believe this is happening when the lookup fails to find a record. What is the best way to check if this is the case and then only do the Patch if a record is found? I have tried adding code in a few places to check for this, but no luck.

[Ethan_R]

Ohh @dBrand , 

I guess this should work,

ForAll(
    coll_Temp_Web_Connections As wsc,
    With(
        {
            LookupRecord: LookUp(coll_WebServices, ws.Object_ID = wsc.'Oject ID' && ws.ObjectName = wsc.'Object Name' && ws.Object_Type = "Page", ThisRecord)
        },
        if(!IsBlank(LookupRecord),
            Patch(
                coll_Web_Service_Connections,
                LookupRecord,
                {
                    'Service Name': Service_Name
                }
            )
        )
    )
)

[dBrand]

had tried adding that, but was getting Name isn't valid. IsBlank isn't recognized. Seems odd to me, but likely due to where it is or something in the With statement?