I managed to successfully create this flow. The only catch is in the last step (Create Item) at 8:33, you need to enter manual values instead of choosing from the dynamic builder. The manual value will look something like this @items('Apply_to_each_-_Add_new_and_changed_items')?['Title']
@@munfordable They are saying that you get the value of the object property in the last step by using a question mark. So it is OBJECT?['PropertyName']
very nice and good explanation. When to expect part 3, this for clarification for the compose and parse functionality as this is not clear in how to do that in the video
@@JamieMcA Thank, hope it because i had the same issue as james, just the title peace of the compose and parse functional is missing, to make it all work, best rgds arthur
Hi Jamie, i am new to all this, but i have created a create new / update flow to take data from SQL Table to Sharepoint list, and have a unique SQL ID for each row.. I am looking some guidance on carrying out the delete function in relation to the record being deleted in SQL and ensuring this is removed from Sharepoint on a recurring basis. Any tips on the flow steps would be really appreciated. Thanks
Thanks. I've got most of the way through but I'm struggling with the create item at the end. I don't get the options you have with the alarm clock symbol next to the field names. Please can you release part 3? I have contributed to your patreon.
Roz, thank you so much for joining us on Patreon. I have recorded part 3, editing has been tricky. Will post the completed solution file including dates and times there today. Also your question is answered in those posts in Patreon.
@@JamieMcA Thanks again Jamie. Got it working. Now trying to teach myself how to update the items instead of deleting them as I'm updating a List that is providing a lookup to another List. If I delete the rows that have changed and recreate them rather than updating them, I think I'll lose the link in the main List. I've learnt lots from following your tutorial though so I might be able to figure it out eventually :)
Awesome video, but i am getting some error, Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Condition_2' at line '1 and column '10985' is invalid. Action 'Apply_to_each_Delete_SP_Items_which_is_not_in_Intersection' must be a parent 'foreach' scope of action 'Condition_2' to be referenced by 'repeatItems' or 'items' functions.'.'.
First of all, a very nice video. However, I tried to replicate your steps and I cannot save because of an error. "The template validation failed: 'The inputs of template action 'Compose_DestinationDocs' at line '1 and column '4273' is invalid. Action 'Apply_to_each_MainDoc' must be a parent 'foreach' scope of action 'Compose_DestinationDocs' to be referenced by 'repeatItems' or 'items' functions.'." could you please help me?
It seems there's been some issue during the build. Start over and try again as it's not easy to fix a Flow that has gone awry. Will release the detailed build video shortly.
Hi Jamie, thank you for the video. For my compose and parse JSON actions with SharePointkeys, I am getting the error "Invalid Type. Expected string but got Null." I have various columsn that, depending on the scenario, may have no data in the column. Is there a workaround for this?
I need to update daily without any recurrence is it possible anytime any user to update or delete in excel & update to SP list . Can I just put manual trigger instead recurrence. Thx
How do you do this when the "Title" is a number? I have a form that people fill out and it includes their ID number, I then have an Excel Spreadsheet that I want Microsoft Flow to look at and pull their email, department, title from and put into the Sharepoint list. The ID number is a number, which seems to confuse Sharepoint Online and Flow, or at least I can't get it to work.
@@JamieMcA In the form the user fills out the ID number, which propagates to a SharePoint List. I then, once an hour, want to have a flow check the Sharepoint list and the spreadsheet, and if the ID Number matches fill in the title, department, and email address.
Totally doable. You can define the data as string in your JSON for the comparison and of course can populate the fields. From Excel you'll actually get a Serial Date, which is number of days since end of 1899. This can be converted to a date via formula addDays('1899-12-31',int(item()?['YourDate']),'yyyy-MM-dd'). Make sure to format the dates you get from SharePoint with the same mask so comparisons will work.
Thanks for the great video/content!! I'm so close to getting this to work but it looks like I'm missing a step - At 8:44 mark where you have the condition creating an Item, it's labeled "Create Item 2" I don't see anywhere that you used this function/Action before this point, so I'm guessing that's why I'm having issues. Is this something you are saving for your Patreon members? or am I just missing something altogether? Thanks in advance!
Howdy! Thanks for watching! Create Item 2 is the only Create Item action. I guess it got that name during the build as I switched things around. Renaming such things in a Flow can create problems so it still has the misleading name.
@@JamieMcA How did you manage to have the Create function in the end not being embedded in a 'Apply to all' function? Does this also come from switchting things around?
@@MrDeadbone - I have this same question. The 'create item' function continues to put itself into a new 'Apply to Each' function every time I save the flow. This has happened on a newly created flow, along with the existing flow - deleting and recreating any of the steps as new has the same results. The flow seems to work, except for this fact (loops through the rows and adds row one data the maxrows number of times. then moves on to row two and adds that data multiple times...and so on.)
@@hollis3597 @MrDeadbone I too was having this issue but I figured it out. In the create item action, when selecting values for fields, don't use dynamic builder. You are probably entering values from the previous get items step which is incorrect. Instead you need to manually enter the field value from current item which will look something like this @items('Apply_to_each_-_Add_new_and_changed_items')?['Title']
This flow has no update functionality, it replace it! The previous videos explained a flow which used Update items to update existing one. This one will delete the item and create a new item! That's bad for SharePoint list where filed will be changed manually and other update automatically.
Yes, it is a trade off, but we're back to needing unique keys for Update. I wrote 4 versions of the Flow to get this result, the true CRUD Flow gets very large and difficult to explain.
@@JamieMcA ah - wish I'd read this before spending a day trying to get it working. I was going to base another flow based on a new item being added, then send an email. However this is going to generate emails every time. Any other recommendations?
I like the video, and it's helping me, but that piece to describe the JSON, and the final creation of the records if they don't exist, or have been updated, is critical. Shame you missed it out, as without that I'm struggling, and I'm sure others are too
What if I want to do this with an excel with 20000 rows? Will it still be possible? Will it just take very long? They wont all be different each time, so they dont all have to be deleted and added Also, when is part 3 coming?
I have not tested to to that scale but am in the process of preparing something. There are a bunch of data limits for Flow which you could run into and some way of chunking is likely required.
@@JamieMcA i managed to get it down to 8000 rows, I will watch it as soon as it comes out. I have a project from my internship and I really need this done 🙃 Is there any date set for the release?
Hi there, I think that the information contained in this Video [ th-cam.com/video/QcWjAt7QVn4/w-d-xo.html ] will work around the need to parse the variables and the JSON build
Really hope you get the last video of this up soon, I have watched this now 3 times and I still cannot get my version of this flow to work.
Last video has been posted to Patreon and will be public later in the week.
This was exactly what I was looking for!!!! Thank you so much!
Thank you for watching!
I managed to successfully create this flow. The only catch is in the last step (Create Item) at 8:33, you need to enter manual values instead of choosing from the dynamic builder. The manual value will look something like this @items('Apply_to_each_-_Add_new_and_changed_items')?['Title']
You are totally correct. This is a key point. Glad you made it. Video 3 is recorded, but editing not finished.
Why do we have to do this? Thank you :)
Hi @solidheart07 can you please explain that last bit of code?
@@munfordable They are saying that you get the value of the object property in the last step by using a question mark. So it is OBJECT?['PropertyName']
@@JamieMcA Thanks, Jamie. Will that code above change if it is for a "choice" column/value in SharePoint when creating an item?
very nice and good explanation. When to expect part 3, this for clarification for the compose and parse functionality as this is not clear in how to do that in the video
Coming soon!
@@JamieMcA Thank, hope it because i had the same issue as james, just the title peace of the compose and parse functional is missing, to make it all work, best rgds arthur
This video is great! Do you have any plans to release part 3?
Yes, it's in progress. Keep watching.
Hi Jamie, i am new to all this, but i have created a create new / update flow to take data from SQL Table to Sharepoint list, and have a unique SQL ID for each row.. I am looking some guidance on carrying out the delete function in relation to the record being deleted in SQL and ensuring this is removed from Sharepoint on a recurring basis. Any tips on the flow steps would be really appreciated. Thanks
Thanks. I've got most of the way through but I'm struggling with the create item at the end. I don't get the options you have with the alarm clock symbol next to the field names. Please can you release part 3? I have contributed to your patreon.
Roz, thank you so much for joining us on Patreon. I have recorded part 3, editing has been tricky. Will post the completed solution file including dates and times there today. Also your question is answered in those posts in Patreon.
@@JamieMcA Thanks again Jamie. Got it working. Now trying to teach myself how to update the items instead of deleting them as I'm updating a List that is providing a lookup to another List. If I delete the rows that have changed and recreate them rather than updating them, I think I'll lose the link in the main List. I've learnt lots from following your tutorial though so I might be able to figure it out eventually :)
Do I need the Initialize variable? as mine is picking from SP
Hello, My flow is creating duplicates, I don't know how to fix it
It's usually a slight mistake in the formula.
If I want to update a sharepoint item using the response from a form how would I go about this? Love the content btw!
Awesome video, but i am getting some error,
Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Condition_2' at line '1 and column '10985' is invalid. Action 'Apply_to_each_Delete_SP_Items_which_is_not_in_Intersection' must be a parent 'foreach' scope of action 'Condition_2' to be referenced by 'repeatItems' or 'items' functions.'.'.
These messages are usually the result of typos in the JSON or other parts, or other misalignments
First of all, a very nice video. However, I tried to replicate your steps and I cannot save because of an error.
"The template validation failed: 'The inputs of template action 'Compose_DestinationDocs' at line '1 and column '4273' is invalid. Action 'Apply_to_each_MainDoc' must be a parent 'foreach' scope of action 'Compose_DestinationDocs' to be referenced by 'repeatItems' or 'items' functions.'."
could you please help me?
It seems there's been some issue during the build. Start over and try again as it's not easy to fix a Flow that has gone awry. Will release the detailed build video shortly.
@@JamieMcA I will wait for the 3rd vídeo.
thank you very much
Hi, Jamie, Thank you for this nice video! One question about Table_query at 3:00, why I don't have that choice?
You need to select those columns in Excel and set them as Filter columns. (Right hand side of menu)
Great video waiting for part 3, thank you
Coming soon!
Is it possible to use this function to create several Sharepoint lists from one excel sheet? All of the lists would have the same columns.
Yes it would be possible, but if you're doing the comparisons against each list it's getting complex.
Hi Jamie, thank you for the video. For my compose and parse JSON actions with SharePointkeys, I am getting the error "Invalid Type. Expected string but got Null." I have various columsn that, depending on the scenario, may have no data in the column. Is there a workaround for this?
I need to update daily without any recurrence is it possible anytime any user to update or delete in excel & update to SP list . Can I just put manual trigger instead recurrence. Thx
You could try to trigger on a change I guess.
How do you do this when the "Title" is a number? I have a form that
people fill out and it includes their ID number, I then have an Excel
Spreadsheet that I want Microsoft Flow to look at and pull their email,
department, title from and put into the Sharepoint list. The ID number
is a number, which seems to confuse Sharepoint Online and Flow, or at
least I can't get it to work.
I'd get the JSON lined up to accept that or convert the value to string. Depends what it will be in SP.
@@JamieMcA In the form the user fills out the ID number, which propagates to a SharePoint List. I then, once an hour, want to have a flow check the Sharepoint list and the spreadsheet, and if the ID Number matches fill in the title, department, and email address.
Awesome video! How easy do you think it would be to include dates too? We’d like to populate one of the columns with dates too
Totally doable. You can define the data as string in your JSON for the comparison and of course can populate the fields. From Excel you'll actually get a Serial Date, which is number of days since end of 1899. This can be converted to a date via formula addDays('1899-12-31',int(item()?['YourDate']),'yyyy-MM-dd'). Make sure to format the dates you get from SharePoint with the same mask so comparisons will work.
Hi when can we get the video part3
In production right now
Please release part 3.
This has now been released to Patreon community. Will become public a week later.
Thanks for the great video/content!! I'm so close to getting this to work but it looks like I'm missing a step - At 8:44 mark where you have the condition creating an Item, it's labeled "Create Item 2" I don't see anywhere that you used this function/Action before this point, so I'm guessing that's why I'm having issues. Is this something you are saving for your Patreon members? or am I just missing something altogether? Thanks in advance!
Howdy! Thanks for watching! Create Item 2 is the only Create Item action. I guess it got that name during the build as I switched things around. Renaming such things in a Flow can create problems so it still has the misleading name.
@@JamieMcA How did you manage to have the Create function in the end not being embedded in a 'Apply to all' function?
Does this also come from switchting things around?
@@MrDeadbone - I have this same question. The 'create item' function continues to put itself into a new 'Apply to Each' function every time I save the flow. This has happened on a newly created flow, along with the existing flow - deleting and recreating any of the steps as new has the same results. The flow seems to work, except for this fact (loops through the rows and adds row one data the maxrows number of times. then moves on to row two and adds that data multiple times...and so on.)
@@hollis3597 @MrDeadbone I too was having this issue but I figured it out. In the create item action, when selecting values for fields, don't use dynamic builder. You are probably entering values from the previous get items step which is incorrect. Instead you need to manually enter the field value from current item which will look something like this @items('Apply_to_each_-_Add_new_and_changed_items')?['Title']
Jamie, I think "Select" from Data Operations would of saved you so many steps.
I'm always happy to refactor, will check it out. Thanks! :)
This flow has no update functionality, it replace it! The previous videos explained a flow which used Update items to update existing one. This one will delete the item and create a new item! That's bad for SharePoint list where filed will be changed manually and other update automatically.
Yes, it is a trade off, but we're back to needing unique keys for Update. I wrote 4 versions of the Flow to get this result, the true CRUD Flow gets very large and difficult to explain.
@@JamieMcA ah - wish I'd read this before spending a day trying to get it working. I was going to base another flow based on a new item being added, then send an email. However this is going to generate emails every time. Any other recommendations?
I like the video, and it's helping me, but that piece to describe the JSON, and the final creation of the records if they don't exist, or have been updated, is critical. Shame you missed it out, as without that I'm struggling, and I'm sure others are too
I will post the step by step video shortly, production has been delayed slightly.
@@JamieMcA Any update on the follow-up video Jamie?
What if I want to do this with an excel with 20000 rows? Will it still be possible? Will it just take very long? They wont all be different each time, so they dont all have to be deleted and added
Also, when is part 3 coming?
I have not tested to to that scale but am in the process of preparing something. There are a bunch of data limits for Flow which you could run into and some way of chunking is likely required.
@@JamieMcA i managed to get it down to 8000 rows, I will watch it as soon as it comes out. I have a project from my internship and I really need this done 🙃
Is there any date set for the release?
Me too!
@@TrailersYT I was planning a couple of different videos before that one. :(
@@JamieMcA I figured it out myself, made 2 flows I found online into 1 flow
Hi there, I think that the information contained in this Video [ th-cam.com/video/QcWjAt7QVn4/w-d-xo.html ] will work around the need to parse the variables and the JSON build