Updating Parent list referencing custom column

Like (0) Share

Report

Posted on 29 Nov 2019 00:15:44 by AlexBS

Hi there,

I was wondering if you could assist me with a very specific requierement that I currently have. I´ll try to explain the scenario to the best of my abilities.

I have a parent list in a SharePoint´s Project template site that works as the company´s Project Central. Whenever an authorized user creates a new item with some basic information, a Flow that automatically creates a new subsite triggers. This I have managed to get to work perfectly.

This is the structure of the aforementioned Parent List, my apologies since it is in spanish. Although I tried, I wasn´t able to change the display language:

Project Parent List.png

Basically, the code (Código in spanish) column, is a URL type column that automatically writes the URL of the newly created subsite when a new item on this lists is created.

Project Parent List 2.png

Now, here is the thing, on the subsite I have a list called "Tasks", I want to be able to connect this Parent List (Project Central) to this list in the newly created subsite in order to retrieve the information pertaining to the earliest task´s Start Date, latest task´s EndDate, Overall Status, completed %, budget, along with other columns using as a reference the "Code" column in the Parent List that displays the URL of the new (related) project´s subsite:

Project Child List 1.png

Back to Flow, I have managed to get the columns of the Parent List and even call the secondary (Child) list but I have not been able to display the columns on the "tasks" list as a dynamic content and hence, I can´t update the Parent List with the aforementioned data. This is how my Flow looks so far:

Project Flow 1.png Project Flow 2.png

You´ll notice that within the "get elements 2" action, I have used the "Code" (código) field from the Parent List, and "Tasks" (Tareas) as the List Name.

Now, even though the columns for the second list (Tasks) within the subsite do not appear listed as dynamic content, I can tell that the Flow it´s been able to read them since they are displayed when I run the Flow, which makes me think I´m on the right path:

Project Flow 3.png

Notice how the Title, StartDate and DueDate are the ones shown in the Child List (third image) from top to bottom.

Finally, this is error that it´s been displayed whenever I run the Flow. I´ve not added the Update list action as of yet:

Project Flow error.png

Any and all help you could provide me with in order to get to work will be highly appreciated.

Thanks in advance.

I have the same question (0)

All responses (13)

Answers (0)

v-bacao-msft on 29 Nov 2019 at 02:46:17

Like (0)

Report

Re: Updating Parent list referencing custom column

Hi @AlexBS ,

Could you please provide more details about the error?

Best Regards,

Was this reply helpful? Yes No
efialttes 14,756 on 29 Nov 2019 at 04:58:45

Like (0)

Report

Re: Updating Parent list referencing custom column

@AlexBS

As Barry suggests,can you elaborate a bit more on the error you are getting? It seems at least one iteration of your loop is failing, you can inspect the failed ones one by one by clicking "Next Failed" as many times as needed

Ánimo!

Was this reply helpful? Yes No
AlexBS 49 on 29 Nov 2019 at 13:48:25

Like (1)

Report

Re: Updating Parent list referencing custom column

Yes of course.

Once I run the Flow, on the first and second results pages I just get "ActionFailed. An action failed. No dependent actions succeeded.".

Now, on the subsecuent result pages I get "BadGateway" and the following error on the Body:
{
"error": {
"code": 502,
"message": "Unable to connect to the remote server",
"source": "flow-apim-msmanaged-na-centralus-01.azure-apim.net",
"path": "forward-request",
"clientRequestId": "3af38deb-aaf1-4483-a227-1fd6cfebd4e9"
}
}

On th 4th page I get:
{
"statusCode": 404,
"message": "Resource not found"
}

On the last page no error is displayed and the OUTPUTS box clearly shows the data on the secondary list (Tasks) which I still think is a good sign. :It´s important to stress that this output, (with the information regarding the subsite Task list), is also shown in all the other pages:

[
{
"@odata.etag": "\"3\"",
"ItemInternalId": "1",
"ID": 1,
"Title": "Planificación",
"Checkmark": "0",
"StartDate": "2019-11-12",
"DueDate": "2019-11-23",
"AssignedTo": [
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
"Claims": "i:0#.f|membership|soportebellsoft@acorde.or.cr",
"DisplayName": "Soporte bellsoft",
"Email": "Soportebellsoft@Acorde.or.cr",
"Picture": "https://acordeor.sharepoint.com/sites/proyectos/8/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Soportebellsoft@Acorde.or.cr",
"Department": "Tecnología de Información",
"JobTitle": "Administrador de Redes y Soporte Técnico"
}
],
"AssignedTo@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser)",
"AssignedTo#Claims": [
"i:0#.f|membership|soportebellsoft@acorde.or.cr"
],
"AssignedTo#Claims@odata.type": "#Collection(String)",
"PercentComplete": 0.85,
"Modified": "2019-11-28T21:19:58Z",
"Created": "2019-11-28T21:18:24Z",
"Author": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
"Claims": "i:0#.f|membership|soportebellsoft@acorde.or.cr",
"DisplayName": "Soporte bellsoft",
"Email": "Soportebellsoft@Acorde.or.cr",
"Picture": "https://acordeor.sharepoint.com/sites/proyectos/8/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Soportebellsoft@Acorde.or.cr",
"Department": "Tecnología de Información",
"JobTitle": "Administrador de Redes y Soporte Técnico"
},
"Author#Claims": "i:0#.f|membership|soportebellsoft@acorde.or.cr",
"Editor": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
"Claims": "i:0#.f|membership|soportebellsoft@acorde.or.cr",
"DisplayName": "Soporte bellsoft",
"Email": "Soportebellsoft@Acorde.or.cr",
"Picture": "https://acordeor.sharepoint.com/sites/proyectos/8/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Soportebellsoft@Acorde.or.cr",
"Department": "Tecnología de Información",
"JobTitle": "Administrador de Redes y Soporte Técnico"
},
"Editor#Claims": "i:0#.f|membership|soportebellsoft@acorde.or.cr",
"Predecessors": [],
"Predecessors@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
"Predecessors#Id": [],
"Predecessors#Id@odata.type": "#Collection(Int64)",
"Priority": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "(2) Normal"
},
"Priority#Id": 1,
"Status": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "En curso"
},
"Status#Id": 1,
"{Identifier}": "Lists%252fTareas%252f1_.000",
"{IsFolder}": false,
"{Thumbnail}": {
"Large": null,
"Medium": null,
"Small": null
},
"{Link}": "https://acordeor.sharepoint.com/sites/proyectos/8/_layouts/15/listform.aspx?PageType=4&ListId=f5998748-2476-4c79-9acb-b475de831f7f&ID=1&ContentTypeID=0x010800EF0C78CD7E3E6B409E658BF8AD7451FA",
"{Name}": "Planificación",
"{FilenameWithExtension}": "Planificación",
"{Path}": "Lists/Tareas/",
"{ContentType}": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedContentType",
"Id": "0x010800EF0C78CD7E3E6B409E658BF8AD7451FA",
"Name": "Tarea"
},
"{ContentType}#Id": "0x010800EF0C78CD7E3E6B409E658BF8AD7451FA",
"{HasAttachments}": false,
"{VersionNumber}": "1.0"
},
{
"@odata.etag": "\"4\"",
"ItemInternalId": "2",
"ID": 2,
"Title": "Implementación",
"Checkmark": "0",
"StartDate": "2019-11-19",
"DueDate": "2019-12-06",
"AssignedTo": [
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
"Claims": "i:0#.f|membership|soportebellsoft@acorde.or.cr",
"DisplayName": "Soporte bellsoft",
"Email": "Soportebellsoft@Acorde.or.cr",
"Picture": "https://acordeor.sharepoint.com/sites/proyectos/8/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Soportebellsoft@Acorde.or.cr",
"Department": "Tecnología de Información",
"JobTitle": "Administrador de Redes y Soporte Técnico"
}
],
"AssignedTo@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser)",
"AssignedTo#Claims": [
"i:0#.f|membership|soportebellsoft@acorde.or.cr"
],
"AssignedTo#Claims@odata.type": "#Collection(String)",
"PercentComplete": 0.25,
"Modified": "2019-11-28T21:20:03Z",
"Created": "2019-11-28T21:18:32Z",
"Author": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
"Claims": "i:0#.f|membership|soportebellsoft@acorde.or.cr",
"DisplayName": "Soporte bellsoft",
"Email": "Soportebellsoft@Acorde.or.cr",
"Picture": "https://acordeor.sharepoint.com/sites/proyectos/8/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Soportebellsoft@Acorde.or.cr",
"Department": "Tecnología de Información",
"JobTitle": "Administrador de Redes y Soporte Técnico"
},
"Author#Claims": "i:0#.f|membership|soportebellsoft@acorde.or.cr",
"Editor": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
"Claims": "i:0#.f|membership|soportebellsoft@acorde.or.cr",
"DisplayName": "Soporte bellsoft",
"Email": "Soportebellsoft@Acorde.or.cr",
"Picture": "https://acordeor.sharepoint.com/sites/proyectos/8/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Soportebellsoft@Acorde.or.cr",
"Department": "Tecnología de Información",
"JobTitle": "Administrador de Redes y Soporte Técnico"
},
"Editor#Claims": "i:0#.f|membership|soportebellsoft@acorde.or.cr",
"Predecessors": [],
"Predecessors@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
"Predecessors#Id": [],
"Predecessors#Id@odata.type": "#Collection(Int64)",
"Priority": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "(2) Normal"
},
"Priority#Id": 1,
"Status": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "En curso"
},
"Status#Id": 1,
"{Identifier}": "Lists%252fTareas%252f2_.000",
"{IsFolder}": false,
"{Thumbnail}": {
"Large": null,
"Medium": null,
"Small": null
},
"{Link}": "https://acordeor.sharepoint.com/sites/proyectos/8/_layouts/15/listform.aspx?PageType=4&ListId=f5998748-2476-4c79-9acb-b475de831f7f&ID=2&ContentTypeID=0x010800EF0C78CD7E3E6B409E658BF8AD7451FA",
"{Name}": "Implementación",
"{FilenameWithExtension}": "Implementación",
"{Path}": "Lists/Tareas/",
"{ContentType}": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedContentType",
"Id": "0x010800EF0C78CD7E3E6B409E658BF8AD7451FA",
"Name": "Tarea"
},
"{ContentType}#Id": "0x010800EF0C78CD7E3E6B409E658BF8AD7451FA",
"{HasAttachments}": false,
"{VersionNumber}": "1.0"
}
]

If you need more information please let me know.
Thanks.

Was this reply helpful? Yes No
AlexBS 49 on 02 Dec 2019 at 22:13:02

Like (0)

Report

Re: Updating Parent list referencing custom column

Hi,

Just checking if you have had the opportunity to review my response to your last message and see if you have any suggestions.

Gracias!

Was this reply helpful? Yes No
efialttes 14,756 on 03 Dec 2019 at 10:06:34

Like (0)

Report

Re: Updating Parent list referencing custom column

@AlexBS Thanx for the details! Did you configure 'Apply to each' to apply sequentially or in paralel?

https://flow.microsoft.com/en-us/blog/gateway-ha-increased-apply-to-each/

Was this reply helpful? Yes No
AlexBS 49 on 03 Dec 2019 at 16:01:18

Like (0)

Report

Re: Updating Parent list referencing custom column

Thanks for your response @efialttes

It is configured to run sequentially as it´s the default setting:

Please notice that the results of the flow clearly show the contents of the Subsite´s child (Tasks) list. I just need to be able to pull those results into the Parent List so I can update it.

I´ll be looking forward to your next recommendations.

Muchas gracias.
Alex.

Was this reply helpful? Yes No
efialttes 14,756 on 03 Dec 2019 at 21:49:31

Like (0)

Report
Re: Updating Parent list referencing custom column

@AlexBS

In order to get more info, did you try to add a Compose action block as first step inside your Apply to each with the following expression?

items('Apply_to_each_1')?['Código']

Please note accent would probably need to be translated into the corresponding set of characters

If the output of Compose action block in each iteration is the expected one (i.e. the URL for the subsite), then it looks like either there is a bug in the implementation of SP "Get items" when the site URL provided is dynamic, or from some reason, some of the subsites are not accesible from your Flow account 😞

Since it looks you have 'just' 8 subsites, one possible way to proceed for troubleshooting is by adding 8 "Get items" action blocks, one per subsite, and select the target subsite from the dropdown menu instead of using the dynamic input. This recommendation is not for the final implementation, just for troubleshooting purposes.

Hope this helps

Was this reply helpful? Yes No
AlexBS 49 on 08 Dec 2019 at 18:13:54

Like (1)

Report

Re: Updating Parent list referencing custom column

Hi @efialttes, @v-bacao-msft and team,

As per your recommendation I have added the compose action as the first action within the apply to all container. Furthermore, in order to facilitate the troubleshooting process, I have deleted all but the first 2 items from the parent list and made sure the URL on the "code" (código) column is correct:

Apparently this was part of the reason for the multiple errors since it seems that some of the URLs on the other items code´s columns were incorrect. No errors are shown when the flow was ran this time:
These are the overall results of the flow after the first "Get element" action calling up for the parent list.

Now, regarding the remaining items, this is the Output from both, the compose and the Get elements 2 actions:

These are the results from the compose action you´´ve suggested. It seems I may be doing something wrong since it is not showing the subsites URLs as you mentioned it would.Finally, these are the results of the get elements 2 action which is indeed showing the values in the subsite´s task list.

Please note that I have added some caption to the pictures above.

As you can see the "get elements 2" action is indeed pulling the data from the task lists within each subsite. No errors are shown this time around:

Now I just need to be able to pull that information into the parent list in order to populate and update its items. An example just for the sake of transparency would be:

Update the first item on the parent list called "Portafolio de Proyectos" (Project portfolio) (first picture above) with the information shown in the last picture. The trick here is that "Fecha de inicio" (start date) has to be updated with the FIRST task´s start date, and "Fecha de finalización" (End date) has to be updated with the LAST task´s end date, both from the child (Subsite´s task list).

I just don´t know how to do this.

I hope I was able to explain myself, my apologies if I haven´t.

I´ll be looking forward to your suggestions.
Thanks in advance.
Alex.

Was this reply helpful? Yes No
AlexBS 49 on 08 Dec 2019 at 19:49:16

Like (1)

Report
Re: Updating Parent list referencing custom column

Hello again,

As per your second suggestion "one possible way to proceed for troubleshooting is by adding 8 "Get items" action blocks, one per subsite, and select the target subsite from the dropdown menu instead of using the dynamic input". I´ve added a get items to manually get the first subsite´s tasks list at the begging of the Flow:

And then at the end, I´ve added a Update Parent List action as follows:

I´m NOW able to select the Task (child) list data from the dynamic content dropdown list:

Thanks! 🙂

The Flow now runs successfully BUT it updates BOTH items in the Parent List with the last item´s dates from the subsite Task list:

Subsite A - Child List Items:
Questions remain:

How to pull each subsite´s task list data dynamically using the parent list´s code column that contains each subsite´s URL and...
How to get just the first task´s start date and the last task´s end date from each subsite´s Tasks List (i.e. Project´s start and end date respectivilly).

Please advise.
Thanks.

Was this reply helpful? Yes No
efialttes 14,756 on 09 Dec 2019 at 08:05:53

Like (0)

Report

Re: Updating Parent list referencing custom column

@AlexBS

I understood that the initial issue getting subtasks was related to the URL format from some of them, right? Does it mean you can currently retreive all of them successfully by using the content of column "Código" as "Site Address" URL dynamic input?

If so, my suggestion to solve the second issue is to use the dynamic input approach:

Get Parent List items

Apply to each (using Get Parent List items output as the input for the Apply To Each)

Inside Apply to each:

-insert "Get Project X items"using column "Código" as "Site Address" URL dynamic input

-identify first task´s start date and store it in a variable (please remember to initialize it before the Apply to Each).

-identify last task´s end date and store it in a second variable (please remember to initialize it before the Apply to Each)

-update parent task with both variables values

AS per my little experience, Accents are not good friends of Microsoft Power Automate (aka Flow). Let's assume you add a Compose action block inside the suggested 'Apply to Each', then inside the Compose itself you add 'Código' as dynamic content. Once added, please hover the mouse over it to verify how Flow replaces vowels with accents.

Hope this helps

Was this reply helpful? Yes No