cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Updating Parent list referencing custom column

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.pngProject 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.

 

13 REPLIES 13
Community Support
Community Support

 

Hi @AlexBS ,

 

Could you please provide more details about the error?

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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

Flow_FirstIterationFailing.png

Ánimo!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



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",
"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",
"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",
"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
},
"{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",
"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",
"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",
"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
},
"{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.
 
Frequent Visitor

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!

@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/ 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Thanks for your response @efialttes 

 

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

 

Project Flow error 1.png

 

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.

@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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



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:

 

Flow Results 2 (2).png

 

 

 

 

 

 

 

 

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.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.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.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:

 

Subsite a task list 1.png

 

 

 

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.

 

 

Frequent Visitor

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:

 

Flow updated 1.png

 

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

 

Flow updated 2.png

 

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

 

Flow updated 3.png

 

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:

 

Updated Parent List.png

Subsite A - Child List Items:

Subsite 1 Tasks List.png

Questions remain:

 

  1. How to pull each subsite´s task list data dynamically using the parent list´s code column that contains each subsite´s URL and...
  2. 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.

 

@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

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Thanks for the follow-up @efialttes 

 

Don´t worry about the accent, I´ve just changed the column name to "Codigo" so it doesn´t have it anymore.

 

In regards to your question, no, I´m still not able to retrieve the task list values using the dynamic approach. I´m only able to view that information by either running the flow (as part of the flow resutls) or entering the Task list URL statically into the "Get elements" action.

 

I was trying to follow your steps but I don´t see any output if I use an "Apply to all" action right after the "Get Parent List items" action at the beggining of the Flow:

 

Flow Results 6.png

 

Do you mean "value" as output?

 

Please clarify.

 

Thanks.

Frequent Visitor

Hi again,

 

I forgot to mention but I´m not very familiar with variables in Flow as of yet, therefore I´m having trouble understanding the following instructions:

 

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

 

Could you please guide me thru the process of initializing and adding the variables?

 

I´d really appreciate it.

Thanks.

@AlexBS 

Here you are an excellent post explaining how to use variables in Microsoft Power Automate .

In my opinion, the main issue you will have to solve is how to identify first task start date and last task end date. I guess start and end dates will be stored in different columns, if so you can include a couple of get items applying an ODATA filter to order by the corresponding column in the order you need, and get the first element from the result array by means of an expression.

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (20,574)