All Revisions per sheet written to Excel

Hmm at a glance, I think you may not want that List.Flatten node after your List.Create

Hi @awilliams

Which List.Create are you talking about? I have a List.Flatten after each List.Create and after the List.Combine node. You can see the actual graph in my previous post 22/27 on May 15th.

Thanks,
Des

@desmond.bolton well done!

You still need a lit more list management though.

Have a look below.

image

This will write the data like this:

image.

You need to do the following now:

  1. Manipulate and export the rest of the data so it looks like this:

image

  1. Combine that data.

Hi @salvatoredragotta

You have lost me completely now, even though what you say does make sense and I can see what you want me to do, I just have no idea how to do this in Dynamo.

:frowning: :thinking: :tired_face:

Hi Jacob

You are right- I should put this in as a formal request rather than just complaining about it.
I’ll get onto it.

However, I would see it as a part of an essential overhaul of the scheduling tool in Revit- which is pretty much unchanged as far back as I can remember (Revit version 8?). I am fairly certain that there must already be requests in the system for this.

Andrew

Apologies @desmond.bolton I hadn’t looked closely enough at the entirety of your graph when I made the recommendation to remove a flatten node.

@salvatoredragotta has gotten you almost there :slight_smile: you’re just missing the last bit on how to get those revision rows below the first revision and sheet number/name - sometimes it helps to learn to make a small sample Excel file structured the way you’d like it, and use the Excel.ReadFromFile node to see exactly how you need to structure your data to write it properly. If you read this way you will see that each of the cells with blank values for columns A and B read as nulls

I had a look at your .dyn file and I think there were a lot of unnecessary List.Create and List.Flattens in there after each bit of data. For the sake of clarity and concision I put notes in the graph below to try and explain the list manipulation that’s going on with each node, but overall I think it’d help best to make sense of it by first reading a sample xls file in Dynamo, otherwise my notes might sound like gibberish

The result in Excel looks like this (I believe this is what you’re after, and if I’m mistaken I apologize for the wasted reading)

Hope this helps

1 Like

Hi @awilliams

You are an absolute legend…:star_struck:

Thanks for taking the time to help me out on this. I have recreated your graph and it works perfectly on the sample project, I even added more revisions and the graph picked up everything with no errors.

I then ran the graph on a couple of our live projects that have multiple sheets and multiple revisions and both times the graph runs with warnings at the same node, also the information is not complete in Excel (but I am supposing this is due to the erroneous node) and another thing I noticed is that the sheets are no longer in order in Excel (probably due to the error as well), I have attached the Excel output so that you can see. I have also attached an image of the node that is struggling, can you maybe have a look and see why it is giving an error. If you look at the total items in each node, you can see that the erroneous node is different and I am assuming it should be the same (414, 414, 439)

Here is the Excel output…

Revisions.xlsx (29.4 KB)

And here is the graph…

Revisions on Sheet 6.dyn (35.0 KB)

Thanks heaps,
Des

Ah there are two conditions that the current graph will fail by the List.Deconstruct node:

  1. Sheets with no revisions, because they produce an Empty List
  2. Sheets with only one revision, because they produce a list with only one item

The graph I shared above failed to account for these conditions because, well, my sample file didn’t have them, oops! :sweat_smile:

I think the best way to handle this would be to separate sheets with no revisions, sheets with only one revision, and sheets with multiple revisions, then join their data back together before writing to Excel. See the below graph (the non-green colored groups are the new additions since the last graph, and I colored the groups by the conditions they’re handling)

Additionally - the original graph only appeared to output the sheets sorted by their sheet number, because our sample files more than likely had each sheet element created in the same order as their sheet number. In actuality, the graph was by default sorted by element ID which follows order of creation. I added a bit towards the end just before flattening to write to Excel that uses Sort By Key to sort the lists by sheet number. I definitely suggest going through and doing all of the exercises in the Dynamo Primer once you’ve finished this graph :slight_smile:

7 Likes

I qa/qc’ed your script (all the i’s & t’s then p’s & q’s) to make sure it was identical. I tested it with a small renovation project of mine near completion. It seems to only offset the Revs for Sheet A.0.02, . See images…
Sample%20Export
Then here is the progression listed out in Dynamo…

Is there an outlier maybe?
Here is that Excel file it produced…
Export Rev Test.xlsx (12.6 KB)

@cftrevizo Hmm, thats a strange output. Would you please upload your .dyn file and a screen capture of the whole graph with previews enabled (use the “Export Workspace as Image” button on the top right of Dynamo) It looks as though there’s something off with the some part of the list manipulation but I can’t tell where without seeing the rest :slight_smile:

Here it is…


Revisions on Sheet_v7.dyn (80.2 KB)

What I’m looking into doing now any way is to just export all the data, then have an Excel Pivot Table report the way we are wanting to show it here. When I get that set up I’ll sharer it here.

The red line is where the problem is - you were filtering the sheets with revisions using the revision information from all of the sheets, so sheets with only one revision were producing data for multiple revisions

image

1 Like

Hi @awilliams,

Sorry for my late response but I have been away for a few days.

Just so you know, I am revoking your previous legendary status because it is beneath you. You have excelled beyond that measly status… :rainbow:

I have had a test run of your graph and it works like a dream, all sheets are now listed and in order and all the revisions for each sheet are showing.

I can not thank you enough.

I will mark your post as the solution.

Thanks Heaps,
Des

1 Like

kindly post the final dyn file also if its not too much to ask for others to follow…

Thanks!

Here is the Final iteration as @awilliams has defined it…
Revisions on Sheet - Final.dyn (82.7 KB)
Also, here is a version I’m preferring (for Pivot Table analysis in Excel) provided by Aaron Maller, but tweaked a little…I’m on v1.3.3 & for some reason works best for me when I set it to Automatic instead of a Manual Run…
Issue Tracking Report_Prlx.dyn (51.1 KB)

2 Likes

Thank you for the script. I added some headers to the script, but I’d like to also add some parameters e.g. Sheet Order and Discipline and sort by Sheet Order. I’ve tried a number of things, but it didn’t work. Essentially, I’d like it to export to Excel like this schedule format below and include a list of all the revisions. Where would be the best place to add additional parameters? Thanks in advance.

Hi @awilliams

The script works great, but I´m struggling with what I thought should be a simple tweak. I wish to repeat the Sheet number & name for each Revision:

I tried the “Multi-Rev” Sortings 1 & 2 but the final result isn´t the expected.

Also, is it possible to rearrange the final output so it´s sorted by latest revisions and not by sheet?

I´m new in Dynamo so any help would be greatly appreciated

1 Like

Hi @F_Vela, forgive me for replying to this so late. Have you gotten this sorted yet? I’d felt the annotations I included in the graph described what the function/purpose of the nodes/groups decently well, and you had the right idea by eliminating the List.Deconstruct. It seems what you’re missing is having done a List.OfRepeatedItem for each line to extend the sheet names and numbers.

As for your sorting question, the area of the graph towards the end is annotated to indicate where it is sorting by sheet. Simply change the index input for the List.GetItemAtIndex node that is connected to the “keys” input on List.SortByKey.

Hope this helps if you haven’t already solved your own inquiry. If you’re still having difficulty share your graph here indicating your changes and I can help you out more.

Hi, this script is very useful, thank you for sharing!

@F_Vela you’re going to heaven and if you’re new in Dynamo, I’m a monkey on a tree scratching my head, lol.

I’m trying to audit the revisions of a project by creating a list to compare it with past issued sheets, just to confirm we haven’t lost clouded items in old issued revisions, without publishing those changes (I have the theory we have).

Thing is, I couldn’t figure out where to attach the List.OfRepeatedItem described by @awilliams to create an excel file that doesn’t require to do a manual copy/paste. Where should this item be attached to to make it work?

Currently, I can be only thankful for sharing this tool on the internet and I’m doing a manual copy/paste in excel to keep the progress I need.

Thank you!

Forgive my beginnerness.

Been trying to reverse engineer this with my very limited free time using the direct-out-of-the-box dynamo to better understand the logic while learning.

What packages am I missing so the Node all work?
Is it possible to use non custom node/ scripts to achieve the same results.

Thanks