Welcome to the community, @Eric_Bryant1! :grinning_face_with_big_eyes: This is a very interesting problem, and I’m neck deep in playing with a solution, but more info would be helpful.

When a letter prefix is added—e.g. A5, B5, etc.—what’s the greatest number of these that you’ve seen? Has there ever been a case where a Z5 was added for example? I’m trying to figure out how many letters I need to account for, and I’m hoping it won’t need to go as far as Z.

Ignore my earlier question. I worked it out. It’s going to look like a mess I’m sure, but here’s the logic behind it:

• Start with the raw value of the scene number, ignoring any letter prefix/suffix and any part numbers


• If there’s a letter prefix, subtract the inverted position of that letter in the alphabet multiplied by .01; e.g. Z subtracts .01, Y subtracts .02, etc. This will put earlier letters before later ones, and put all such pre-scenes before their whole-scene-number equivalent, so that A4-?4 are all in order before 4.


• If there’s a letter suffix, do the same thing, only add .01 instead of subtracting it based on the letter. This ensures that 4A-4? are all after 4.


• Finally, if there’s a part number, multiply that by .01 and add that value

The result is a numeric sequence that uses these prefaces/suffixes to either add or subtract from the base scene number in a similar fashion to how they relate to each other based on your description (I hope!). Sorting by that formula field will put things in the proper order. Using your example, it appears to be working:

While I’ve set the formula field formatting to display the decimal values, you can leave it on integer display mode and it won’t affect the actual sorting. Still, seeing the values is helpful to ensure that the order is correct.

Here’s the formula, which relies heavily on regular expressions to find and extract specific pieces. Replace all instances of “Scene” with your actual scene name field.

IF(Scene,

    VALUE(REGEX_EXTRACT(Scene, "RA-Z]?\\d*"))

    - IF(REGEX_MATCH(Scene, "^(A-Z]"), FIND(REGEX_EXTRACT(Scene, "^EA-Z]"), "ZYXWVUTSRQPONMLKJIHGFECDBA") * .01)

    + IF(REGEX_MATCH(Scene, "(?:E^A-Z]\\d*)(cA-Z])(?:.*)"), FIND(REGEX_EXTRACT(Scene, "(?:E^A-Z]\\d*)(cA-Z])(?:.*)"), "ABCDEFGHIJKLMNOPQRSTUVWXYZ") * .01)

    + IF(REGEX_MATCH(Scene, "pt.*"), VALUE(REGEX_EXTRACT(Scene, "(?:pt)(\\d*)")) * .01)

)

IF(Scene,

    VALUE(REGEX_EXTRACT(Scene, "RA-Z]?\\d*"))

    - IF(REGEX_MATCH(Scene, "^(A-Z]"), FIND(REGEX_EXTRACT(Scene, "^EA-Z]"), "ZYXWVUTSRQPONMLKJIHGFECDBA") * .01)

    + IF(REGEX_MATCH(Scene, "(?:E^A-Z]\\d*)(cA-Z])(?:.*)"), FIND(REGEX_EXTRACT(Scene, "(?:E^A-Z]\\d*)(cA-Z])(?:.*)"), "ABCDEFGHIJKLMNOPQRSTUVWXYZ") * .01)

    + IF(REGEX_MATCH(Scene, "pt.*"), VALUE(REGEX_EXTRACT(Scene, "(?:pt)(\\d*)")) * .01)

)

That is incredible, @Justin_Barrett! I have no idea how you figured that out! :smiling_face_with_sunglasses:

Note that there is a small typo in your last RegEx function on the second line, where the letter “R” is mistakenly listed as a “U”.

Thanks, Scott! I blasted my way through typing that string out, so I’m not surprised that there’s a typo. I was a lot more careful in proofreading the other one (which, BTW, doesn’t need to be reversed if I change the math slightly, but that’s how I pictured it, so that’s how I wrote it).

Maybe I’ll add this to my video production list to break it down in greater detail. I just need to figure out when to tackle that list…

In theory it could go as far as Z, but i’ve never seen it. If a screenplay needed that many revisions, I’d say it’s back to the drawing board.

Thank you so much @Justin_Barrett ! This is such an elegant solution.

I’m going to try to better understand your formula in the hopes of furthering my own knowledge.

After I saw this I realized that there is a scenario that I had forgot to mention. And that is SOMETIME, they might insert a Scene between A10 and 10. And they would call that AA10. Another scene inserted would be callee AAA10.

I’m going to try to use the logic if your formula to figure out how to accommodate that scenario. It’s rare, but I feel like it would be wise to cover that possibility as well.

Thanks again for the great solution.

@Eric_Bryant1 It’s possible to extend the logic that I used to cover those AA and AAA scenarios, but it would make the formula even more unwieldy than it already is. In short, working with a single alpha prefix only requires one of those long lines (currently line 3 of the above formula). Working with two or three means first counting the letters, and then executing a different branch depending on that count, with the calculation logic repeated (with slight changes) based on the count; i.e. once for a single letter, twice for two letters, and three times for three letters. That kind of extrapolation would be a lot easier if formulas were as flexible as scripts, which support recursive logic, iteration, etc.

In that light, another way to pull this off would be to use a manually-triggered Scripting app script (I wouldn’t recommend doing this via automation) that you run whenever a new scene is added, which could do all the math and output the proper number into a {Sort} field. Such a script could use the advantages mentioned above to support any length of prefix, suffix, etc., and do it much more efficiently.

You could also just disable the sort if those edge cases come along and drag the record into position among the previously-auto-sorted scenes.

And neither will Justin two years from now when he searches for answers and discovers his own solution is the answer.