I have a challenge for you… I have created a nested-projects Coda doc (using this brilliant post) that I hope to eventually use for my main project management sheet. Before that, I want to make sure some basic things are possible.
Can anyone figure out the formula to produce the desired sort order? It is logical, so I think there is a formula that outputs the numbers in the “DesiredSortOrder” column.
Sort Rules:
Children must be (nested) under their parents.
Projects on the same level are sorted alphabetically
I have updated original document to include effective Sort Order
The formula is if(thisRow.[Parent Project].Count() != 0, thisRow.[Parent Project].[EffectiveSort Order]+"."+Projects.Filter([Parent Project]=thisRow.[Parent Project]).Sort().Find(thisRow), Projects.Filter([Parent Project].Count() = 0).Sort().Find(thisRow))
the if condition checks if its a root project. For root projects (else condition above), it takes all root projects, sorts them and finds a position of “thisRow” in sorted list. For non-root projects, it does the similar thing but first filters to projects under same parent project and then appends its position to parents project position. so effectively, if root project is 1, first level sub-project under it would be 1.1.
So that returns a string, and sorting on that string is supposed to sort the items properly?
I entered that formula into my table and it did not produce the correct results. When I view your table and sort the table on that column, it does not sort correctly either. For some reason, 1.1.1 is sorted after 2.2.
I have created what I think is the solution, but it is not sorting properly. I think sorting strings that are actually numbers may be getting us into trouble. But also, check out the doc to see some really strange sorting rules when you sort your solution or mine.
For example, when you sort the “Solution” column, why is 1.2.1 sorted after 4.2?
yes, you are right. Based on our current design- it is sorting numbers first and then sorts all text values which leads to this behavior.
to workaround this, I have changed the formula to include “SO:” (sort order - you can use any prefix) as prefix - this forces all values to be text, and i think sorts correctly. so now formula is
and yes, a bug is logged to reconsider this sort behavior. if anyone else reading has a feedback on this aspect of sorting, please post it here or send it to us at support@coda.io.
I was afraid of what was going to happen once Coda interpreted it as a string: Now SO:10.9.1 sorts before SO:6 because 1 is smaller than 6. To get around this, I have used LeftPad(string,3,0). (Perhaps change your document with LeftPad so future people get the right solution). This assumes you will never have more than 999 projects on the same level, but I am okay with that assumption for me.
Here is the updated formula, simplified. Unfortunately, due to the submitted but, the Concatenate("S", ... is necessary. But once that is fixed, this formula is quite nice.
You managed to solve it with 1 column, which is super nice. I just simplified the lookups into one. I now understand why it appears to start at 6. Clever way to work with the numbers.
Thank you for your help! Others are welcome to play with the doc and try solutions of their own!
Alright, it’s my bed time over here in Germany, so when I get up perhaps we will know more.
The best two solutions (in my opinion) are listed in the doc. I will change them if you find anything wrong with them.
It seems unlikely that sorting on the names themselves would cause issues, since that is exactly what Sort() is doing anyways…? I’m interested to hear what cases you have in mind.
if you have a project like MAST with a subproject called Teaching - and then another project MASTA - i think what you want is MAST, TEACHING, MASTA, but what it would produce is MAST, MASTA, TEACHING.
Perhaps a more detailed example could work out the edge-case you are trying to sort out. I have added MASTA as a root-level project and it sorted properly given the rules I layed out in the original post:
For now I believe this issue has been solved. Thank you again for the quick and good work. Your solutions have changed the way I think about programming in Coda for the better. All those lectures on recursion and I still can’t see it as obviously as you posted. I will definitely be looking harder at this style of functions in the future.
can you share the screenshot of what you are seeing? I see that Doctoral Seminar is listed under MASTA instead of MAST which seems to not match your rule of 'Children must be (nested) under their parents` as i would expect Doctoral Seminar to nest under MAST.
You are completely right… We need to insert a character() between each project name that sorts above all other characters that might be used.
Perhaps a silly question, does Coda use ASCII sorting or Unicode sorting? I’m guess since Coda supports Unicode, that it sorts based on Unicode order. In that case, inserting a tab between each project name would probably be safe.
