Specifying multiple conditional potential parent pages for the duplicatepage() function

[Aaron_Moritz]

I’m pretty new to coding, even low-code versions of coding, so i might be missing something obvious here.

I have many docs where I use a button within a row to create a page associated with that row. It names the page after the row display value, and inserts a link to the page into the row as well. This all works well.

When selecting the ‘parent page’, for most of my docs this is based on some category, like current status, or type. There’s usually 2-5 different ‘parent pages’ I might want to use, and so I code the button with a switchif() function, just duplicating the entire page creation formula but directing it to a different parent page. Works great.

The issue has come up where I’ve create a doc where I have 26 parent pages (A, B, C, D, ). The table is a list of films, and I’d like each page to be put in an alphabetical bin based on the film title.

So doing my usual thing would require not just duplicating the formula within a switchif() 26 times (for each letter), but closer to 79 times (specifying rules for each letter, then again for each letter starting with A or The).

I won’t paste the whole long code, but this is the basic idea of how it’s working:

`Switchif( 
  
   Left(thisRow.Name, 5)="The A",
   
    ModifyRows(thisRow,thisRow.Page, DuplicatePage([Single Film Template], name: thisRow.Name, parentPage: A)), 
  
  Left(thisRow.Name, 3)="A B",
   
      ModifyRows(thisRow,thisRow.Page, DuplicatePage([Single Film Template], name: thisRow.Name, parentPage: B)), 

  Left(thisRow.Name, 1)="C",
   
      ModifyRows(thisRow,thisRow.Page, DuplicatePage([Single Film Template], name: thisRow.Name, parentPage: B)), 
  
)`

I created a version of this, and while it makes the formula editor freeze a lot, it does actually work when I press the button.

However I thought there needed to be a more efficient way, so I tried to do it with a formulamap() function. I created a table with a row for each potential (A, B, The A, The B, An A, etc) as well as the associated page in a different column.

This almost works, but the duplicatepage > parentpage function will not accept row/column data when it’s expecting a page. Even if that row/column is returning a single page, it doesn’t parse right.

Any ideas? I can keep using my janky extremely long formula, but I’m not looking forward to going back to edit it if I need to! lol

edit: I was able to reduce it significantly (and make it case insensitive) by stacking the conditions pointing to the same page with OR, but it’s still really long.

Switchif( 

Upper(Left(thisRow.Name, 3))="A C" OR upper(Left(thisRow.Name, 5))="THE C" OR Upper(Left(thisRow.Name, 1))="C",
   
      ModifyRows(thisRow,thisRow.Page, DuplicatePage([Single Film Template], name: thisRow.Name, parentPage: C)),

[Daniel_Stieber]

Hey @Aaron_Moritz ,
when possible, you can also share your doc so it is easier to understand the whole case

Anyway, I hope I get it right and be able to help.

“Dynamic” ParentPages are not a thing yet as far as I know and I have just tried a few ways without a result. So you will still need switchifs. But with some tweaks, you can reduce them back to 26 and also make them way faster!

Some general tips:

A) Reduce the number of recurring formulas
B) Reduce the number of formulas on a button click by caching results in columns

In your current solution, you require to execute the “Upper” and “Left” formula about 70+ times each on button click. So it would be good to reduce this to only one time (A) and even store it in a column (B). On button click, you ONLY want switchif, no more upper/left …

How to do that:
As I understand, movies starting with “A” (Anger) should be handled the same as movies starting with “The A” (The Agency) and same with “A A”, so let’s “flatten” that to the actual letter you want. A handy way is to use Regex, which is a cool, but (also sometimes complicated) way to parse strings.

For your case, you might go with this

thisRow.Name.Upper().RegexReplace("^(A |THE |AN )","").Left(1)

In plain english:

• Take the name
• Make it uppercase (you can also ignore case in regex, but we want the result uppercase anyway, so I just made everything uppercase already here)
• Look if the name starts with "A ", "THE ", or "AN " and replace with an empty string
• Take the first letter from the left of what is left after replacement

In general, Regex is a time-costly operation, especially if you perform it 70-140 times on one button click. But with the Parent Page column, we already store the result in the column and don’t need to execute the regex & upper formulas on every button click.

Now in your switchif, you just need 26 ifs and run 140+ less operations than before.

swithif(
thisrow.[parent Page] = "A",
   *modifyrow -> duplicate page in parent a*,
thisrow.[parent Page] = "B", 
   *modifyrow -> duplicate page in parent b*
...

Hope that helps and makes your doc significantly faster!

[Aaron_Moritz]

This is great, thanks. The regex formulas are ones I hadn’t come across yet, and I’m seeing how powerful this can be. I just implemented your idea and I could feel the formula editor getting less laggy as I removed all those repeating upper()'s and left()'s.

It works great, and I’m already seeing that having this ‘First Letter’ column will also be useful for grouping one of my main views, so it solves two problems at once!

I also like this solution because I was already thinking it would be nice to be able to add stuff like " L’ " and "LE " for films with French titles, and potentially other language variation of these rules, but also how difficult that would be under my other set-up. This version makes it easy to update those rules.

Thanks for the overall coding advice as well, and next time I’ll remember to share a version of the doc for people if I ask a question.

[Daniel_Stieber]

That is great to hear @Aaron_Moritz !

A playful add-on if you want:
If you have lots of “ignore” parts, you could manage them in a table as well. It shouldn’t change much regarding performance, because of cached values again.

The new table just holds the start words without space like “A”, “The”…, and the named formula somewhere directly on the canvas makes the Regex out of it.

format("^({1} )",
      [DB Ignore Words].value.totext().regexreplace(", ", " |").Upper()
)

In plain english:

• change {1} with the following formula result
• take the list of values from the Ignore Words table (will be “A, The, Le, Un”) and make it a textstring
• replace the “, " with " |” in the textstring
• make it uppercase

This formula is just placed on the canvas and named, so you can reference the result it in the Movies table.