Sunday, September 14, 2014

Practical Dynamo - Excel Linking 2: Keeping Track of Your Families

There is an advantage to living in a small city/country with a disproportionately large contingent of ADSK employees: I have great access to the development team!

In my last blog post, I pointed out how the list of values for the selected families were not in alphabetical order:

This was because the instances were sorted in order according the GUID:

Without taking the GUID into account, it was impossible to map data from excel back to the appropriate family instance.  If i sorted the data in Excel, I would end up with results like this one:

Not quite hitting the mark in terms of "Data Integrity".  What i needed was a way to export the data, taking the GUID into account, and then be able to map values to the right object on the return trip.

I got in touch with my friends Sharad and Ritesh at ADSK in Singapore and told them about my problem.  These guys love both a challenge and customer feedback; its a match made in heaven because i am a man with problems.

Explaining my dilemma to them, they knew exactly how to solve it and we developed these 2 scripts.


I included the UniqueID into the index stack:

Making my excel spreadsheet look like this:

Now when i bring the data back from Excel, there is the index for my elements included.  The order of the GUIDs will inherently match the order of the lists for the other data indices.

What the boys at ADSK told me about was a command ElementSelector.ByUniqueID.  They made me create a code block like you see above and then plug that into SetParameterByName node.  Now DYNAMO will pick my instances by name and in the correct sorted order.

And I am whole again!

The ElementSelector.ByUniqueID is a "hidden command" apparently.  It was in the user menu previously, but the team took it out because they couldn't think of a scenario where it would be useful!  BIMTroublemaker to the rescue!

This whole process makes me happy like ordering from the secret menu at In & Out!

Tuesday, September 2, 2014

Practical Dynamo - Excel Linking

At some time or another, I think we have all grumbled about Revit's lack of interest in communicating with Excel.  The minions of Bass implored us "LINK TO MS/ACCESS!!!"

yeah right....

In response many of us have expended resources on bi-directional excel links using the API.  Sometimes purchased, sometimes internally developed, but there was always a substantial cost involved.

Then along comes DYNAMO.  There is not shortage of pretty pictures of its iterative design capabilities but i'm more practically minded. I realized in an instant that this was the tool that could give me an on the fly, bi-directional excel link... if only i could figure it out.

I've taken some babysteps and i thought i would share.  There is a hardcore group of guys using it, but i am doing my part to spread the word to the mass market.

Here's what i did, starting with a field of orbs.

I've given all the orbs instance marks of AAA to KKK.  My goal is to export the data to excel, add a column in excel with new data, and finally port the new data it back into the "Comments" field.

Getting the Data Out

First thing was to make a new DYNAMO file that I called "Excel Out.dyn"

I'll work from Left to right through the steps

Family Types - Identifies the family type within the Revit model you would like to work with.
All Elements of Family Type - Tells DYNAMO to select them.

Element.GetParameterValueByName - Returns a list of the values for a given parameter within the selected families.
String - Literally a string of characters.  In this case, the name of the parameter i want DYNAMO to get for me.  This kind of work is case-sensitive.

Watch - These are the boxes that will let us see what DYNAMO is doing.  The box on the left shows me the list of values in the "Mark" parameter.  Its not in alphabetical order and I don't care.  On the right is the list of values for the "Comments" parameter.  Its empty, which is accurate.

List.Create - Now i am compiling the data i've culled. index0 refers to the list of values from the "Mark" parameter, index1 refers to the list of values from the "Comments" parameter.

List.Transpose - The previous lists were formatted as columns, however when Dynamo and Excel talk, the data should be formatted by row.  This button reorganizes the data.  Now i have a list for each family instance where item [0] = the "Mark" parameter and item[1] = the "Comments" parameter.

Excel.Write - This is the node which will open an excel file and write to it, but it needs some instructions
File Path - Tells DYNAMO where to find the .XLSX file
sheetName - Must indicate which sheet within the workbook is to be referenced.  I use a text string to indicate
startRow & startCol - Tells DYNAMO where to start reading the spreadsheet
data - Is looking for the lists we created earlier

I hit the RUN button in the bottom left corner.  It thinks for a second then i open my excel file and i have this:

Perfect.  My exported values are in the first column, the second column is blank as anticipated.  For now i am still ignoring the lack of alphabetical order.

Getting the Data In

Using the CONCATENATE function in excel, i went ahead and added the data in column b that i will pump back into my families.

For this part, I made a new DYNAMO file that I called "Excel In.dyn"

Again I'll work from left to right:

File Path - Gets the Excel file
String - text string to indicate which worksheet
Excel.Read - The node which tells DYNAMO to open the file and read it
Watch - Showing me what it comes up with.  Notice how it has made me a series of lists showing what was in each row.

List.Transpose - This is what turns the list of data by rows into a list of data by columns
List.GetItemAtIndex - This is an instruction to pull a particular list out of the list of lists.
Number - "1" pointed at the "index" slot tells it that i want list [1]
Watch - You can see that it has isolated the data i want

Element.SetParameterByName - This is where the magic happens.  This is the node which will write to data to the parameters in your families.  Of course it needs some information.
In the element slot we get:
Family Types - Indicates the families available in the model.
All Elements of Family Type - Tells it to select the indicated family.
In the parameterName slot i use a String node to indicate the parameter i want to write, "Comments".
The value slot links back to me previously isolated data.

I hit the RUN button again and success!

There is still some ways to go with this but i am really happy that i have made some headway.  More help is available here from guys who are lightyears ahead of me: