Auto trim fields in database

1

Hi Team,

I was thinking of writing an automation to loop over every row and every column and ‘TRIM()’ to remove trailing white space. This just keeps the system a little cleaner.

My best case scenario is something like a button that I click once a week that goes

  1. Loop over ‘Every Table in Doc’ and
  2. Loop over ‘Every Row in Table’ and
  3. For every column TRIM() any whitespace out.

Does anyone know a good way to do this?

1 Like
2

Hi Logan,

You can create such a button, but you have to specify each table and column manually. It would be something like this:

RunActions(
  Table_1.forEach(
    currentValue.modifyRows(
      Column_1, currentValue.Column_1.trim(),
      [...], 
      Column_n, currentValue.Column_n.trim()
    )
  ),

  [...],

  Table_n.forEach(
    currentValue.modifyRows(
      Column_1, currentValue.Column_1.trim(),
      [...], 
      Column_n, currentValue.Column_n.trim()
    )
  )
)

Let me know if this helps,

Pablo

1 Like
3

Hi @Pablo_DV ,
Thank you for your suggestion. It works but unfortunately, this would be a long formula that would need constant updating.

I am definitely open to more suggestions!

A possible solution could exists if there was a coda formula like the excel Indirect() formula. This would enable us to use a variable as the name of a column or table.
E.g.

// Example only, this code does not work in coda
Withname(
  List("Table 1, "Table 2", "Table 3"),
  listOfTableNames,

  listOfTableNames.ForEach(
    // E.g. currentValue = "Table 1" as text
    Indirect(currentValue).ForEach(
       // E.g. currentValue = [Table 1] as table reference
      currentValue.ForEach(
        // E.g. currentValue = A row in the current table
        currentValue.ModifyRows(
          // Still not sure how to make it loop through each column programmatically.
)))))

If we had that, we could used a pack like this which generates a synctable of all tables in my doc and also get a syncable of all columns.

But enough dreaming for now. Anyone got any suggestions?

4

Hi Logan,

@Scott_Collier-Weir has a pack called Doc Explorer, and one of the things it does is to give you a list of the tables in your doc.

See if you cannot use forEach() to cycle through the entries in this table.

P

1 Like
5

That would definetly open interesting possibilities! But I’m afraid there’s nothing like that at the moment.

I have not tested it, but I highly doubt that you can use the list of tables returned by Piet’s suggestion to iterate through all the rows of each table.

Also, for a given table, there’s no way to iterate through all the columns without specifying them, though some have already suggested it: New formulas for columns - Suggestion Box - Coda Maker Community

1 Like