Patching or updating a nested collection is not working

Question

ClearCollect(
    Coll_Products,
    {
        Id: 1,
        Title: "Apple",
        coll_types: [
            {
                Id: 1,
                color: "Red"
            },
            {
                Id: 2,
                color: "Green"
            }
        ]
    },
    {
        Id: 2,
        Title: "Grapes",
        coll_types: [
            {
                Id: 1,
                color: "Black"
            },
            {
                Id: 2,
                color: "Green"
            }
        ]
    }
);

Patch(Coll_Products,Lookup(Lookup(Coll_Products,Id=2).coll_types,Id=1),{color:"purple"});
//or
Patch(Coll_Products,Lookup(Lookup(Coll_Products,Id=2).coll_types,Id=1),{color:"purple"});
//or
Patch(Lookup(Coll_Products,Id=2).coll_types.Value,Lookup(Lookup(Coll_Products,Id=2).coll_types,Id=1),{color:"purple"});

We are trying to update nested collection color value based on Id but we are returning with an error (Patch has invalid arguments).

None of the statements are ain't working to update the nested collection value.

Can anybody help me with this?

score 3 · Answer 1 · answered Jun 06 '20 at 14:44

Since you have a collection inside a record of another collection, you'll need to do a few patches: from the nested collection all the way back to the outer collection. For the collection that you have (and take a look later in this answer for a better way of implementing this scenario), you can patch it with the expression below:

Set(recordToPatch, LookUp(Coll_Products, Id = 2)); // 1
ClearCollect(nestedCollectionToPatch, recordToPatch.coll_types); // 2
Set(nestedRecordToPatch, LookUp(nestedCollectionToPatch, Value.Id = 1)); // 3
Set(patchedNestedRecordValue, Patch(nestedRecordToPatch.Value, { color: "Purple" })); // 4
Set(patchedNestedRecord, Patch(nestedRecordToPatch, { Value: patchedNestedRecordValue })); // 5
Patch(nestedCollectionToPatch, nestedRecordToPatch, patchedNestedRecord); // 6
Patch(Coll_Products, recordToPatch, { coll_types: nestedCollectionToPatch }); // 7

We could use a couple of less lines, but this is easier (a little) to follow;

On line 1 we're retrieving the record that we want to patch from the outer collection
On line 2 we make a copy of the inner collection associated with that record
On line 3 we find the record inside that collection that we want to patch; notice that the Id property is inside a Value wrapper, so we will need to first patch the wrapped record
On line 4 we finally patch the wrapped record
On line 5 we wrap that record again in Value, so we have the new value of the record that we want to patch to the inner collection
On line 6 we patch the nested collection
On line 7 we finally patch the outer collection with the updated nested collection

Notice that on step 3 we had to unwrap the object from the Value property. This is because when you create a collection using the [...] syntax, it is equivalent to a table with a single column called Value. So if we display the value of your collection (using the JSON function, for example), you'll have this:

[
  {
    "Id": 1,
    "Title": "Apple",
    "coll_types": [
      {
        "Value": {
          "Id": 1,
          "color": "Red"
        }
      },
      {
        "Value": {
          "Id": 2,
          "color": "Green"
        }
      }
    ]
  },
  {
    "Id": 2,
    "Title": "Grapes",
    "coll_types": [
      {
        "Value": {
          "Id": 1,
          "color": "Black"
        }
      },
      {
        "Value": {
          "Id": 2,
          "color": "Green"
        }
      }
    ]
  }
]

To simplify this structure, you can use the Table function to prevent the nested collection from being wrapped in the record with a Value property:

ClearCollect(
    Coll_Products2,
    {
        Id: 1,
        Title: "Apple",
        coll_types: Table({ Id: 1, color: "Red" }, { Id: 2, color: "Green" })
    },
    {
        Id: 2,
        Title: "Grapes",
        coll_types: Table({ Id: 1, color: "Black" }, { Id: 2, color: "Green" })
    }
)

And that would be equivalent to this collection:

[
  {
    "Id": 1,
    "Title": "Apple",
    "coll_types": [
      {
        "Id": 1,
        "color": "Red"
      },
      {
        "Id": 2,
        "color": "Green"
      }
    ]
  },
  {
    "Id": 2,
    "Title": "Grapes",
    "coll_types": [
      {
        "Id": 1,
        "color": "Purple"
      },
      {
        "Id": 2,
        "color": "Green"
      }
    ]
  }
]

Without the wrapped object in the nested collection, patching it becomes a little easier:

Set(recordToPatch2, LookUp(Coll_Products2, Id = 2)); // 1
ClearCollect(nestedCollectionToPatch2, recordToPatch2.coll_types); // 2
Patch(nestedCollectionToPatch2, LookUp(nestedCollectionToPatch2, Id = 1), { color: "Purple" }); // 3
Patch(Coll_Products2, recordToPatch2, { coll_types: nestedCollectionToPatch2 }); // 4

The first two steps are the same. But we can now patch the nested collection directly on step 3, and then the outer collection on step 4.

Hope this helps!

Thanks for the answer, will try and update you on the same – TARUN Jun 08 '20 at 06:27 — TARUN, Jun 08 '20 at 06:27

Patching or updating a nested collection is not working

1 Answers1