Display only the last linked record

[Ben]

Hello,
I have a base with two tables
one lists items, the other one lists the person that I’ve lent this item to.
i made a linked column in the item table to link the people from the second table.
the linked colum displays all the person in order that have been added, that is ok.
but I would also a column showing only the latest person that was linked.
I did not manage to create that. Any help ?

[fsa]

Hi Ben,

I assume that you enter the date as well as the borrower on your item list.

Then you can work on the table with the link formula, findmax and select the name and date. This will show you the borrower with the latest date.

[Ben]

Nope, no date, only the person.
Therefore, I can’t use the findmax …

[fsa]

Then I would enter the date of the rental and you have solved the Issue

Just kidding, I unfortunately don’t have a no-code solution.

[bennhatton]

Hi @Ben,
Can you tell us more about your general context ?
As @fsa said, I think there is no no-code solution (I tried to fiddle with the text returned by a simple {AllPeople} formula, but the “search” function of the formula misses a “from the end to the beginning” feature to find the last item) but you can eventually, depending on your context/needs:

• use a script (launched by a button for a specific row, or dealing with every row at once)
• use an automation workflow (Zapier or n8n)

Let us know.
Bests,
Benjamin

[Ben]

Ok, I’ll go a bit more in details.
I oversimplified the question to avoid losing people in details.
We are a charity program and we are placing pinball machines in hospital freely, to enlight the days of the sick people.
We have a dozen of pinball machines, listed in a “table 1”
We have a dozen of places were we put the machines in ‘table 2’

When we move one pinball machine from one place to the other, I create a new link to the new place. That allows me to keep a track of all machines that were placed in this place, and the story of each machine.
I don’t need dates as it’s really sporadic and I’m not creating a new entry as ‘installation of this machine there’, I just create a new link.
But at the end, I would like to have a column that recapitulates only the last place where the machine is placed.

[bennhatton]

Ok, I understand better…
As you have only a dozen of pinball machines and moving them is sporadic, maybe clicking a button each time you move a machine to a new place is acceptable (you tell me)…
If this is acceptable, here is what you can do :

• Create a new text column (let’s call it currentPlace) in your machines table
• Create a new JS script with the following code (please not that in the following code, I assumed that your link column in the Machines table is called ‘Places’. If it’s not the case, you’ll have to change it 2 times on line 3) :

const machinesTable = base.context.currentTable;
const currentMachine = base.context.currentRow;
const lastPlaceQuery = currentMachine['Places'][currentMachine['Places'].length-1];
const lastPlace = lastPlaceQuery['display_value'];
base.updateRow(machinesTable, currentMachine, {'currentPlace': lastPlace});

• Create a new button column and choose Run script/Select the above script as the button action

Now, each time you move a machine, add it in the link column as you did before and then click on the button in the machine’s row : this will (should ) update your currentPlace column.

Bests,
Benjamin

[Ben]

Thanks, I’ll give a try.

[Ben]

That works !!
I don’t know if a Python script may update all lines in one shot, but currently, that’s already enough
Thanks a lot @bennhatton

[bennhatton]

@Ben

I don’t know if a Python script may update all lines in one shot

Try this, it should do the trick :

from seatable_api import Base, context
base = Base(context.api_token, context.server_url)
base.auth()
machines = base.query('select * from Machines') # assuming your machines' table is called Machines
machines_data = []
for machine in machines :
    last_place = ''
    if 'Places' in machine and len(machine['Places'])>0:
        last_place  = machine['Places'][-1]['display_value']
    machines_data.append({
      "row_id" : machine['_id'],
      "row" : {"currentPlace" : last_place}
    })
base.batch_update_rows('Machines', machines_data)  # once again, assuming your machines' table is called Machines

Bests,
Benjamin

[Ben]

The script has an issue, but it fills all the lines.
When there are 1 or 3 places recorded, it give the correct one aka the last one
When 2 places are recorded, it gives the first one instead of the last one

[bennhatton]

Ho, sorry to hear that ! It’s weird, I tested it with 1, 2 and 3 places and I got the good result each time
And I don’t understand how it can returns the first one as machine['Places'][-1] explicitly means

the last item of machine['Places']

[bennhatton]

Ok, I just realized the problem is more complex than we thought : actually I think it actually returns the one of your linked places that was added last in your places table (not depending on how many places you’ve got in your list)… Can you try to confirm that ?

I tried to come back to my first idea which was to use a very simple formula like {Places} to get a list of the places as a unique string, but here again, I’m stuck : if my {Places} formula gives me Place 2, Place 1 (assuming than Place 2 as been entered later in the Places table), the result of the python script machine['PlacesStringList'] will be Place 1, Place 2

Sounds like a bug to me not to obtain the same result in the interface and by scripting, but anyway as I can’t iterate over text search with a formula column (if I could, I would have been possible to get the last item of the list by searching for the space+comma separators in the list), I can’t see a proper solution for your problem

Bests,
Benjamin

[Ben]

Anyway, the javascript is working as expected.
I also don’t get the issue but I lack time to make troubleshooting.
Thanks a lot !!

[bennhatton]

Ok, here us finally a JS script to update all columns at once. It needs an extra formula column (called placesList here) with the simple formula {Places}

const table = base.context.currentTable;
const rows = base.getRows('Machines','Default View'); // Adapt to your case

let selectedRows = [];
let updatedRows = [];

rows.forEach((row) => {
  if ('placesList' in row) {
    selectedRows.push(row);
    updatedRows.push({'currentPlace': row['placesList'].split(', ')[row['placesList'].split(', ').length-1]})
  }
});
base.modifyRows(table, selectedRows, updatedRows);

Now I stop bothering you
Bests,
Benjamin

[Ben]

And THAT works !!
You rock