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

[Anonymous]

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

[Jay-Encodian · Accepted solution]

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:

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:

 and where an item does exist:

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]

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

[Jay-Encodian]

Hey @Anonymous 

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

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]

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 🙂

Thank you for your help!

Best regards,

Laura

[Jay-Encodian]

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]

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

[Jay-Encodian]

Hey @Anonymous 

1st part of the expression:

2nd part

HTH

Jay

[Anonymous]

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!

[Anonymous]

@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

[Jay-Encodian]

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

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!

[Jay-Encodian]

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]

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

[Jay-Encodian]

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

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

[Jay-Encodian]

@Anonymous 

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

[Anonymous]

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

[Jay-Encodian]

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

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.