Feb 27 2020 11:42 AM
I've been trying to 'automate' a property column in a document library by having its value populated by each document added.
To try to explain a little clearer:
User adds document to the library (always the same Excel workbook format - just different info):
Cell SheetX!E11 will always be a value that we want to reflect the value of the column for that document. i.e.
So I am trying to get cell SheetX!E11 in the workbook to populate the specific column in the library.
What I've tried:
Options I've thought 'might' work, but still looking to find out if they are possible solutions and if anyone can help:
Does this somewhat make sense what I'm trying to accomplish? Logically it seems like a simple task - but some limitations of the SharePoint Doc Library might have added some complexity (or impossibility...).
Does anyone have any experience with something like this? Or is there somewhere else I should be looking for this answer? Regardless, I appreciate ANY direction anyone might be able to provide!
Thank you!
Feb 27 2020 08:58 PM
Feb 28 2020 05:23 AM - edited Feb 28 2020 05:28 AM
@Chris Webb Thanks a million! I appreciate your response. I've looked at Power Automate, as I mentioned, but I only noticed being able to reference to 'rows' - although I'm sure there's a way. I intend to educate myself further with PA given its usefulness, but with the pressure/timeline on this one, I'm left to 'reach out'.
** Sidenote: As I was doing a little more digging this morn, I came across an extensive BI blog that seems to belong to you - ironic, lol. You can add another 'follower' :)
Again, thank you!
Feb 28 2020 08:46 AM
I've been messing around with Flow/PA a little, but my roadblocks tend to be:
a) How to get the value of a single cell from the document.
b) Flow seems to want a specific workbook/file in the steps, where I am wanting to reference the 'current file' (the one that is modified/added.)
I figured I'd draw a simple picture of what I'm trying to get out of it:
Of course, the trigger can even be scheduled to 'ripple through' each doc and set the property - Ideally it would be on add/modify though...
Feb 28 2020 01:09 PM
Feb 28 2020 01:28 PM
Might still work :)
Looks like the cell I'm referencing is actually "Dashboard!X2"
It DOES exist within a table (yay) named "KPIData"
The table's column header is "BaselineTotal" (if that's something I/you would need to make this work?)
Feb 28 2020 01:37 PM
Feb 28 2020 01:43 PM
what is Dashboard!X2?
The cell reference:
Workbook Sheet Name = "Dashboard"
Cell = X2
is there anything static on the Row you are trying to pull? Like the row next to it, does it have a value always in that spot or anything like that?
I think I see what you mean here... If there I were to add a column before/after (left or right of - i.e. Y2 or W2) the cell containing the value that could just be a static label, this would be useful...? I can perhaps drop something in...
Feb 28 2020 01:47 PM
Feb 28 2020 01:53 PM
@Jamie Milliken basically it would work like this. you would point to library, with the create or modified trigger. Then point to static location / library and use the Filename with ext. from the first step. (might require the /) in front of it. Then you can take Key Column / Value from the column before the one you need, or really it can be anywhere on the sheet as long as it gets you the row the value is on. Put in the static match values since those should be the same on every sheet for mapping. Then you can return the value you need in a Update File properties step at the end in place of my Email example.
If we can figure out how to just pull the row out via an array reference or something you can do away with the extra column and value static map and just refer direct to the row / value, assuming every single file is the same.
Feb 28 2020 01:53 PM
Here's a quick snip of the table...
(highlighted number is the one I'm trying to pull for the property)
So column would be 'BaselineTotal'
Feb 28 2020 01:56 PM
Feb 28 2020 01:57 PM
Feb 28 2020 01:59 PM
Feb 28 2020 02:02 PM
Quick snip from the SharePoint library:
Feb 28 2020 02:19 PM
Ok, was thinking this wouldn't allow dynamic file names, but I figured it out, you actually have to use the Identifier ID from the trigger action as the file name, then you can static the table name and the key values and this works.
That said, if your Filename begins with the ID, then all you need to do is for the Column is use the static ProjectID or whatever that first column is can't see it right now :p, and then for the row value, use an expression substring(filename, 0, length) basically, start typing in that, then for filename with cursor there, find the "Filename with extension" from the SharePoint trigger, then 0 to start at the left then length will be the projectID length, hopefully static. This should always then give you the value you need. Then at the last action you put in the "Update file properties" action, and you can use the Column from the Get Rows where you value you need to update a column in SharePoint.
Feb 28 2020 02:21 PM
Feb 28 2020 02:29 PM
Feb 28 2020 02:42 PM
SolutionMar 02 2020 05:00 AM
Feb 28 2020 02:42 PM
Solution