cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

link sharepoint document library columns to list - to create charts/dashboard

Hi everyone, 

 

I'm trying to create a dashboard on a Sharepoint site, containing column information from a document library. 

 

Unfortunately my organization doesn't allow custom scripting so I need to (I think) create a list which gathers (almost duplicates) the info from the columns next to files in a document library as the build in 'quick chart' feature can apparently only retrieve info from a sharepoint list.

 

How can I do this and/or is there another solution which doesn't require me to create a list which is basically a duplicate from the info found in the document library? Thanks in advance!

 

Best regards,

Laura

1 ACCEPTED SOLUTION

Accepted Solutions
Jay-Encodian
Community Champion
Community Champion

Hey  @Anonymous 

Correct, the quick chart web part will only pull data from a SharePoint list... or manually entered data.

To sync data between a library and list try this:

1.png

The trigger is firing on the SharePoint document library... it then queries the SharePoint list to see if an item exists where the 'Document ID' (a column I added) is equal to the ID of the file the trigger fired on. If there isn't an item then create one, if there is an item then update it. Does that make sense?

Here's the example execution data where the item in the list didn't exist:

b.png

 and where an item does exist:

b.png

 

The Flow uses the 'Document ID' field in the list to correlate the item 'ID' in the library... creates if an item doesn't exist an then updates after.

HTH

Jay

-------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved.

If you like my response, please give it a Thumbs Up

View solution in original post

25 REPLIES 25
Jay-Encodian
Community Champion
Community Champion

Hey  @Anonymous 

Correct, the quick chart web part will only pull data from a SharePoint list... or manually entered data.

To sync data between a library and list try this:

1.png

The trigger is firing on the SharePoint document library... it then queries the SharePoint list to see if an item exists where the 'Document ID' (a column I added) is equal to the ID of the file the trigger fired on. If there isn't an item then create one, if there is an item then update it. Does that make sense?

Here's the example execution data where the item in the list didn't exist:

b.png

 and where an item does exist:

b.png

 

The Flow uses the 'Document ID' field in the list to correlate the item 'ID' in the library... creates if an item doesn't exist an then updates after.

HTH

Jay

-------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved.

If you like my response, please give it a Thumbs Up

Anonymous
Not applicable

Hi @Jay-Encodian

 

Unfortunately I do not know how to add a custom column to the actions in the flow. Would you be able to advise on how you did this?

Also, does this flow also copy all column information from the document library with it, or does it only copy the file name in the list?

 

Thanks!

Laura

Hey @Anonymous 

The column is added to the SharePoint list, not Flow itself 🙂

1.png

 

You can choose what data you wish to copy... the 'Create Item' and 'Update Item' actions will display the available columns on the target list... you just need to pass data to which ever columns you want to set a value for.

HTH

Jay

Anonymous
Not applicable

Hi @Jay-Encodian 

 

Unfortunately I keep getting an error when testing (see picture), with as error, that the column 'DocumentID' does not exist. Even though I have created the column in both the document library as sharepoint list.

But, I believe once I'm able to sort this, the flow should be able to work 🙂

 

Capture8456.PNG

 

Thank you for your help!

Best regards,

Laura

Hey,

You only need to create the Document ID field on the list. Every item in any list document library has a ID field... this Flow associates document in the document library with items in the list by writing the documents ‘ID’ value into the items ‘Document ID’ custom column... that way you can use the current documents ID value to find the related item in the list by querying the Document ID column.

You may have an encoded space in name of your column... traveling at the moment so can’t send a screen shot... but, go to the list settings and click the Document ID column... there should be parameter at the end of the pages URL called Field which contains the internal name... you need to use that name in your Odata query.
Cheers J
Anonymous
Not applicable

Hi @Jay-Encodian 

 

Unfortunately I have not been able to get the flow working as the flow says my expression in the condition action is not correct.

I wrote it like your comment: empty(body('Get_items')?['value'] ==> did I make a mistake somewhere?

 

Or is there another solution for this?

 

Furthermore if I for example change the condition to if dynamic data (documentID) is not equal to null, it tells me still DocumentID column cannot be found

 

Thank you in advance!

Best regards,

Laura

Hey @Anonymous 

1st part of the expression:

A.png

2nd part

B.png

HTH

Jay

Anonymous
Not applicable

Hi @Jay-Encodian 

 

I have made the flow as you have demonstrated but I still notice the following errors.

The condition action always returns as false and also an item is not created in the list, the list stays empty.

 

Any suggestions? Thanks in advance!

 

Capture+656.PNGCapture5424.PNG

 

Anonymous
Not applicable

@Jay-Encodian 

So I have been able to make it create items using file name with extension as dynamic data instead of Title.

However,  if I edit the properties it keeps creating a new item in the list, so it is not picking up on the ID.

 

Best regards,
Laura

@Anonymous 

Have you checked the 'Get Items' query result? The query attempts to retrieve an existing item where the 'Document ID' value in List B is equal to the 'ID' of the item in List A which triggered the Flow... if it doesn't find an existing item it will create a new one.

Is the query logic working for you?

Anonymous
Not applicable

Hi @Jay-Encodian 

 

I don't believe the same ID is been given to the file in get items and create item if not exisiting. However, as a novice I might be misreading. The fitler query says as input DocumentID q '51', while the condition/create item output the ID is 12.

 

Thanks for your help!

 

Capturea.PNGCaptureb.PNG

Hi @Anonymous 

First things first 🙂

Every item list item / document library in SharePoint has a column called 'ID'... this column stores a unique ID for every item added to the list and is sequential and unique... the first item added would have an 'ID' value of '1', the second '2' so on and so forth... lets say I delete the first item and then add a third item... the third items ID value would be '3'.

Considering the above;

You have two Lists; List A which is the source and List B the destination.

Your Flow is copying data from List A to List B... and logic had been added to check if an item has been previously copied, and it it has update it.

To do this we've added a 'Document ID' column to List B which stores the ID value of the correlating item in List A... this basically creates a one to one relationship.

To check if an item exists;  we query List B to ask whether it contains any items where the 'Document ID' field is equal to the triggered items 'ID' field.. if it does then update if not then we need to create a new item.

When you create a new item in List B... the ID value will be different to the 'Document ID' value as the 'ID' the unique ID for the list item and the 'Document ID' value is the ID Value of the List A item which you have added.

Does this make sense?

HTH

Jay

Anonymous
Not applicable

Hi @Jay-Encodian 

 

That makes sense, so it doesnt matter if lets say DocumentID for list A = same documentID for list B. As long as the flow looks if the documentID for list B already exists. But for some reason it doesn't recognise the documentID as being the same when I change the properties. 

 

So for example I update a file in list A, and the ID = 52 (same as when uploaded), but then in the flow it doesn't recognise it as being the same? Am I perhaps using the wrong dynamic data (from which action does the ID have to be in each step?)

 

Thanks for your help!

Best regards,

Laura

 

 

@Anonymous 

So you don't need a 'Document ID' column on List A, list B has the 'Document ID' column to allow you to store the correlating List A item ID value... for example:

List A Item ID = 10;

I add a new Item to List B and set the 'Document ID' to '10', the resulting List B's value could be:

ID = 8 (if there had already been 7 items added to the list)

Document ID = 10

Now, lets say the item in List A (ID = 10) is updated... this triggers the Flow (the trigger item will be ID='10')

You then query List B for any items where 'Document ID' is equal to '10'... which should return the item from List B with an ID of '8'.

Then when you either update or create a new item, make sure you always write the 'ID' value of the triggered action (The changed item in List A) in the 'Document ID' field of the updated field.

That should have you covered

HTH

Jay

Anonymous
Not applicable

Hi @Jay-Encodian 

 

I still can't get the flow to work and for some reason after it ran I cannot look into the details of the output of the create new item (see picture), so I can't see what the documentID is for list B.

 

So I use the action get items with as filterquery ID of the action when a file is created or modified (proprties only) in list A. Then the condition where in both create item and update item I use the ID dynamic data from the action 'when a file is created or modified'. So this should match no? As it would look at the DocumentID of list A to see if it already exists in the column DocumentID in list B.

 

I went to look in my list B and noticed that while the column claim amount ( a column I added) is filled out, the column DocumentID, which should contain the ID information of List A, does not contain anything. So I'm assuming this is where my problem is coming from. Also list B is not updating with new files even though it says the flow ran succesfully. I will try to do some more testing to see why it's not populating the column in List B.

 

Thanks for your help!

Best regards,

Laura

 

 

Capturec.PNG

@Anonymous 

Referring to the original Flow config... is your configured as follows?

1.png

 

Anonymous
Not applicable

Hi @Jay-Encodian 

 

Yes my flow is configured as on your picture with the exceptions that I added an extra column 'claim amount' so this is visible in the condition action as well and that I changed the dynamic data for title to file name with extension instead of title as when I used Title dynamic data nothing appeared.

When I test my condition output is always false as it doesn't find a match. 

But then I noticed the list also doesn't populate the DocumentID column for some reason, even though it populates the 'claim amount' column.

So it's never able to find a match, which of course makes the flow not work 🙂 Sorry for only noticing now.

However, I'm still confused as to why it's not populating the list or only partly.

Any idea?

 

Thanks in advance!

Best regards,

Laura

@Anonymous 

If the Document ID column is not populated with a value it suggests you're not passing the correct 'ID' field... you need to make sure you've selected the 'ID' column from the trigger action, can you check?

Anonymous
Not applicable

Hi @Jay-Encodian 

 

Yes I checked and it should be correct.

Now when testing, list B is just not populating at all, so not sure what's going wrong.

 

 

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (813)