none
How Can I Update Multiple Records in a Secondary List based on a Primary List that Shares a Common Key

    Question

  • I have two lists (Primary and Secondary) they share a common key value (CommonKey). The Primary list has a status field (Status). When the status is changed on the Primary I need to update all of the records in the secondary file with the status form the primary file. Notes - the lists share a one to many relationship and the common key is unique in the primary list. 
    vendredi 11 mai 2018 16:45

Toutes les réponses

  • We should be able to:

    • Use a REST call to identify the related records based on the CommonKey
    • Count the results
    • Loop through the results and update the status

    Refer to this thread Identify Duplicates with REST which will take you through the first two steps in detail. All we have left is loop and update.

    Here is some test data for our example:

    We need to create a list workflow based on the Primary list that triggers when the list is changed.

    Here is the workflow:

    The first stage is almost exactly the same as in the previous thread where we counted the items:

    However, we have added an additional stage to loop through and update the status in the secondary file:

    This is the workflow log:

    And this is the result in our lists when the workflow runs on the first primary record:

    Since we cover everything up to the loop in the previous thread, I think the only thing I have to add is the details of the "Get" and the update. To understand what we are "getting" this is the string that was returned to the dictionary variable WFResponse:

    {"d":{"results":[{"__metadata":{"id":"Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(1)","uri":"http:\/\/SITE.DOMAIN.com\/ExpenseReports\/_api\/Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(1)","etag":"\"18\"","type":"SP.Data.SecondaryListItem"},"Id":1,"CommonKey":"A123","Status":"Entered","ID":1},{"__metadata":{"id":"Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(2)","uri":"http:\/\/SITE.DOMAIN.com\/ExpenseReports\/_api\/Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(2)","etag":"\"7\"","type":"SP.Data.SecondaryListItem"},"Id":2,"CommonKey":"A123","Status":"Entered","ID":2},{"__metadata":{"id":"Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(3)","uri":"http:\/\/SITE.DOMAIN.com\/ExpenseReports\/_api\/Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(3)","etag":"\"7\"","type":"SP.Data.SecondaryListItem"},"Id":3,"CommonKey":"A123","Status":"Entered","ID":3},{"__metadata":{"id":"Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(10)","uri":"http:\/\/SITE.DOMAIN.com\/ExpenseReports\/_api\/Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(10)","etag":"\"7\"","type":"SP.Data.SecondaryListItem"},"Id":10,"CommonKey":"A123","Status":"Entered","ID":10}]}}

    Which is a bit difficult to read. However, if you paste the string into one of the online JSON parsers it becomes a bit clearer and looks like this:

    {

    • "d":{
      • "results":[
        1. {
          • "__metadata":{
            • "id":"Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(1)",
            • "uri":"http:\/\/SITE.DOMAIN.com\/ExpenseReports\/_api\/Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(1)",
            • "etag":"\"18\"",
            • "type":"SP.Data.SecondaryListItem"
            },
          • "Id":1,
          • "CommonKey":"A123",
          • "Status":"Entered",
          • "ID":1
          },
        2. {
          • "__metadata":{
            • "id":"Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(2)",
            • "uri":"http:\/\/SITE.DOMAIN.com\/ExpenseReports\/_api\/Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(2)",
            • "etag":"\"7\"",
            • "type":"SP.Data.SecondaryListItem"
            },
          • "Id":2,
          • "CommonKey":"A123",
          • "Status":"Entered",
          • "ID":2
          },
        3. {
          • "__metadata":{
            • "id":"Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(3)",
            • "uri":"http:\/\/SITE.DOMAIN.com\/ExpenseReports\/_api\/Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(3)",
            • "etag":"\"7\"",
            • "type":"SP.Data.SecondaryListItem"
            },
          • "Id":3,
          • "CommonKey":"A123",
          • "Status":"Entered",
          • "ID":3
          },
        4. {
          • "__metadata":{
            • "id":"Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(10)",
            • "uri":"http:\/\/SITE.DOMAIN.com\/ExpenseReports\/_api\/Web\/Lists(guid'912b08b8-18ad-4fce-9593-2ba37e2c5a0b')\/Items(10)",
            • "etag":"\"7\"",
            • "type":"SP.Data.SecondaryListItem"
            },
          • "Id":10,
          • "CommonKey":"A123",
          • "Status":"Entered",
          • "ID":10
          }
        ]
      }
    }

    You can see where the values returned for ID are 1,2,3,10 as we vary the index on the Get action from 0 to 3. Zero is always the first item.

    The update action just updates the status field in the secondary file to the status from the primary file for each of the IDs that share the common key.


    vendredi 11 mai 2018 17:04
  • Was Wondering about the  More details later.. 
    jeudi 17 mai 2018 13:25
  • ...sorry for the delayed response Cowboy.
    lundi 21 mai 2018 17:33