Text/Value substitution across lists

I’ve been working on a occupancy calculation script that dumps to excel. I’ve run into an issue with Excel not liking spaces or “-”, so I need to do a name substitution on export based on NFPA Occupancy Use names. So using ‘ReplaceByCondition’ I can pair a list of aliases, but what I can’t seem to do is get them to align to the correct names that come from the actual project.
I think I need to pair my new names with the names available from the project, but I’m missing something to do this. It seems like the ‘replaceBycondition’ needs an additional input instead of a function, but it won’t function.
This whole process has been kicking my butt. The exported names link to excel images to display the hatch patterns next to the occupany use type in EXCEL, I need to translate the names to be compatible with EXCEL’s indirect function to a named cell, hence I need to drop the “-”'s and spaces.

You could use the “String.Replace+” node from the clockwork package

and by the way, you can create a list in your code block with [ and ]

I’ll give that a try. I try to avoid custom nodes whenever I can, but I’m just striking out. I swear there’s an easy way to do this with design script, but I just can’t find anything to reference in the documentation I have for it.

I had the list in EXCEL, so I just did a quick concat to throw the quotes on it and the semicolon at the end to copy paste to my code blocks in Dynamo. I’ve probably typed this list 100 times by now!

Maybe you could extract the python script within the string.replace+ node, then you dont need custom packages, but it is not as easy/ clear what is happening in the script.

String.Replace+ requires this list to be paired already. My inputs from Revit aren’t paired to their aliases, since it will rarely be a 1 for 1 match on the input length vs. the output options. Seems less workable than the OOTB replacebycondition?

Could you then maybe re-explain your question? I do not understand from where to where you want to go in your script.

Sorry i see now that i did not have the right node in mind: this is the node i meant:

1 Like

Yeah, sorry, stuck in my head trying to figure this out…
I have a list of unique area names coming from Revit, this list can be 1 item long or up to 18 items, depending on the building occupancy types.
I want the architect to be able to enter “Business - Concentrated” for the Occupancy Use type.
Dynamo needs to translate this name from “Business - Concentrated” “Business_Concentrated” (provide both names to EXCEL actually)
Since my input list won’t be in any particular order, (Dynamo assigns AREAs by Item ID, so the Area names coming from Revit won’t be in the same place in the list each time, the resolve needs to be somewhat dynamic because of this.)
Hopefully the screenshot of excel helps too.

Maybe you could try sorting both lists in alfabetic order, or also export the area ID to the excel to use as reference


1 Like

I’m unable to sort it at this point. I have 4 compiled lists that get written to EXCEL in order for the calculations to work, if I sort by name I’ll unsort the area square footage, occupant load factors, etc.
It’s a many to one relationship on the area names. They’re filtered for unplaced areas, too small of areas, then sorted by occupancy use to get accurate totals. Had to do this thru Dynamo, since Revit won’t allow formulas off totals, I may have to work in some alpha sorting somewhere, but really hoping not to at this point.

Could you maybe share this Dynamo file? I am having a hard time understanding what your input is and export :slight_smile:

Occ code V3.20.dyn (222.3 KB) Occupant_Load_Schedule_2018_NFPA_101TEMP.xlsx (28.0 KB)

The image links aren’t on this version of EXCEL, and there will be massive cover page alignment issues.
(I’ve added a row from Dynamo, and haven’t updated the cover page cell references yet.

Dynamo probably has the write to excel broken too at this point, the final list going to excel is just broken from my monkeying around, should be easy to spot where I’m working and where it’s disabled.

It leverages various shared parameters in a project for filling in data, but most of it should be documented somewhat decent.

Could you explain why this wouldn’t work for you?

And what is your input in the select model elements node? Is it areas?

Trying it out now with that node! I have a slight name change in excel, but I should be able to resolve that easier I think.

Selection set input. That way they could exclude any areas they want left out of the calcs.

This will work!!! Thank you!!!

1 Like

Glad too hear!

Ended up cutting the python code out of the CW node and pasted into my script to keep it all local with no dependencies. What was odd he was using a convert to string in Python on the name inputs I had to remove since I was already providing strings to the node.
In the end it works… Occupant Use gets ‘excel’ friendly, then the images in excel indirect to the image based on the Excel friendly name. Next challenge will be to get the Revit .PAT images to be dynamic to Excel, but not sure that’s going to be doable with my knowledge base.