I need help using multiple functions, if statements and parent/child.

jambla

I have 3 columns that have information; Scene, Take and Revision.

The scene can be broken into sub-scene and sub-sub-scene etc... This column will always have a value.

Columns Take and Revision may or may not have a value.

I would like to add a fourth row called Full Name. This column will combine the information.

I was able to do this:

=LOWER("scn" + Scene1 + "_" + Take1 + "_" + Revision1)

The issue Im having there is the underscore ( _ ). If there isn't a value present I would need to omit the underscore. I was able to get IF(ISBLANK() working but not combines with my other statement: ( =LOWER("scn" + Scene1 + "_" + Take1 + "_" + Revision1) )

I was also not able to get the parent/child working as far as, if I have a scene 003 with sub-scene b and sub-sub-scene 5. I wasn't able to output scn003b5

Any guidance would be much appreciated!

Andrée Starå

Hi Jambla,

Try this.

This is for the parent

="scn" + LOWER(JOIN(COLLECT(Scene@row:Revision@row; Scene@row:Revision@row; NOT(ISBLANK(@cell))); "_"))    

The same version but with the below changes for your and others convenience.  

="scn" + LOWER(JOIN(COLLECT(Scene@row:Revision@row, Scene@row:Revision@row, NOT(ISBLANK(@cell))), "_"))

This is for the children

="scn" + PARENT(Scene@row) + LOWER(JOIN(COLLECT(Scene@row:Revision@row; Scene@row:Revision@row; NOT(ISBLANK(@cell))); "_"))    

The same version but with the below changes for your and others convenience.  

="scn" + PARENT(Scene@row) + LOWER(JOIN(COLLECT(Scene@row:Revision@row, Scene@row:Revision@row, NOT(ISBLANK(@cell))), "_"))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

The formula can most certainly be even more refined but this will get you started I think.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

jambla

Hi Andrée,

Just before you posted I tried this:

=LOWER("bas_scn" + JOIN(ANCESTORS(Scene1)) + Scene1 + IF(ISBLANK(Take1), "", "_" + Take1) + IF(ISBLANK(Revision1), "", "_" + Revision1))

It works perfectly with parent, children, children's children etc. but I think definitely not the most elegant way of doing it. :-p

I will play with yours a bit.

Thanks for help! Much appreciated!

Andrée Starå

Happy to help!

There are many solutions to the same problem 

I'll save yours!

Best,

Andrée