Tutorial: Getting values from previous row with the help of automation rules

To get the value from a previous row is most of the time only possible by using javascript or python.

Here is one option to connect two or more rows via linked column and automation rules.

  1. Create a table with a date column.
    For example, if you have monthly records you will have entries like 2023-01, 2023-02, 2023-03 and so on.

  2. Create a new „Link to other records column“, select the same table and give it the name „Previous row“

  3. Now you have to create two other formula rows for comparison. The first row creates a string with the year and month of the current date and the other row contains the string of the previous month.
    First column:
    Name: Current month
    Formula: 'Y' & text(year({Date})) & 'M' & text(month({Date})) << will create Y2023M1
    Second column:
    Name: Previous month
    Formula: 'Y' & text(year({Date}) - if(month({Date}) = 1, 1, 0)) & 'M' & text(if(month({Date}) = 1, 12, month({Date}) - 1)) << will create Y2022M12

  4. Now you can create an automation rule that can compare the values and find the related previous month link
    Event: Records meet specific conditions after modification
    Automated action:
    Add links
    Select link column in the table: Previous row
    if column: Previous month
    column: Current month
    Save the rule


  5. Every new row after selecting a date in the Date column will search for the previous month row.
    And you can create Link formula columns to get previous values.

3 Likes

Hi! I developed another approach. Here it is, free of charge and full of risk :slight_smile:

Adapt the following Script with the appropriate names:

const tname = 'your_table_name';
const prename = 'your_link_column_for_outgoing_link';
const postname = 'your_link_column_for_reverse_link';
const table = base.getTableByName(tname);
const rows = base.getRows(table,'you_view_name');
const vlinkid = base.getColumnLinkId(tname, prename);
const nlinkid = base.getColumnLinkId(tname, postname);
slinks = new Map();

function onlyUnique(value, index, self) {
  return self.indexOf(value) === index;
}

rows.forEach(function(row){
  if (undefined != row[prename] && 0 < row[prename].length){
    row[prename].forEach(function(id){
      if (undefined == slinks.get(id)){
        slinks.set(id,new Map([[prename,new Array()],[postname,new Array()]]));
      }
      slinks.get(id).get(postname).push( row['_id'] );
      if (undefined == slinks.get(row['_id'])){
        slinks.set(row['_id'],new Map([[prename,new Array()],[postname,new Array()]]));
      }
      slinks.get(row['_id']).get(prename).push( id );
    });
  }
  if (undefined != row[postname] && 0 < row[postname].length){
    row[postname].forEach(function(id){
      if (undefined == slinks.get(id)){
        slinks.set(id,new Map([[prename,new Array()],[postname,new Array()]]));
      }
      slinks.get(id).get(prename).push( row['_id'] );
      if (undefined == slinks.get(row['_id'])){
        slinks.set(row['_id'],new Map([[prename,new Array()],[postname,new Array()]]));
      }
      slinks.get(row['_id']).get(postname).push( id );
    });
  }
});

for (let [key, value] of slinks) {
  let nach=value.get(postname).filter(onlyUnique);
  let vor=value.get(prename).filter(onlyUnique);
  if(nach.length > 0){
    if (nach.indexOf(key) > -1) {
      nach.splice(nach.indexOf(key),1);
    }
    base.updateLinks(nlinkid, tname, tname, key, nach);
//    output.text("To: " + nach.filter(onlyUnique));
  }
  if(vor.length > 0){
    if (vor.indexOf(key) > -1) {
      vor.splice(vor.indexOf(key),1);
    }
    base.updateLinks(vlinkid, tname, tname, key, vor);
//    output.text("From: " + vor.filter(onlyUnique));
  }
  
}
output.text("** done **");

In Short, this seeks to complement incoming with outgoing links, and vice versa.

I use this in a table where I create a chain to entries following each other, with a predecessor link from one record to the other (even multiple). The script complements these links: If there is a link pointing to a predecessor entry, the script adds the link to the follow-up item to the said predecessor, and vice versa.

Of course, the initial predecessor link has to be set. I do this automatically in the following way:

  • I don’t create a new entry, I just duplicate an existing one. It’s automatically gets added “(copy)” to the primary text column.
  • There is a hidden column formula which cuts the “(copy)” part from the name column, so the formula column holds the exact same content as the record that has just been duplicated => predecessor
  • The following automation links the newly created entry to its predecessor on the basis of identical texts. Note that there is a bug preventing you from selecting the formula column in the automation rule. See here

After that, run the script periodically to complement the other link direction

1 Like