Help

Re: Sorting a lookup field with automation script

Solved
Jump to Solution
1686 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Pierre-Alexand2
6 - Interface Innovator
6 - Interface Innovator

Hi, 

I have a lookup field that is showing the address of different radio controlled products i make.

I would like to sort these using a script automation.

Here is the script I came up with:

 

const address = input.config()

console.log(address)

const sortedAddress = { address: [] };         //something wrong here
sortedAddress.address.sort((a, b) => a - b);

console.log(sortedAddress)
 
Here is a screenshot
 
Screen Shot 2023-09-19 at 11.54.55 PM.png
 
Any idea on how to fix this?
 
I don't understand how to sort the object provided by the input.config().
2 Solutions

Accepted Solutions
Alexey_Gusev
13 - Mars
13 - Mars

Hi,
you need array to sort.

input.config()  is always an object containing properties that you set on left side.
You can write:

const x = input.config() // x={address: Array[5]}
const y = x.address  // y=[17,254,38,8,22]

if you set several values on the left side, you need to assign each, like
const a=x.second_value
const b=x.third_value...

to avoid this, ES6 allows to assign object properties to variables with same names.
like 

const {address, second_value,third_value} = input.config ()

but you have only one, so 

const {address} = input.config ()
// other part
console.log(address)
let sorted=address.sort((a,b) => a-b )
console.log(sorted)

 

problem is that you can't do nothing with it, lookup field is read-only

If you want to write result to the table, you need to reorder links (according to their lookup values).
So, you need to query linked table, get all IDs of records you want to reorder, together with their values, sort according to values, and write sorted array of IDs to the linked field (in format :  [ {'id':'rec123qweDSA456'},{'id':'rec456....'}, ....three ID objects more.. ]
the task might be easier if you use selectRecordsAsync at full power (with sorts and recordIDs)
of course, you can write it to table by script, but you can do 
output.set('sorted', variable_with_result)
and then use it in next, 'no code' step, to update record

See Solution in Thread

Pierre-Alexand2
6 - Interface Innovator
6 - Interface Innovator

Thank you very much this helped a lot.

The second stage of this script was to update another field in the record with the sorted list.

Here is the full completed code in case this might be usefull to someone else:

 

// Define the input data
const obj = input.config();
const { address, id } = obj;

// Sort the address array
const sortedAddress = [...address].sort((a, b) => a - b);
const addressString = sortedAddress.join(', ');

// Select table
let table = base.getTable("Entreprises");

// Find the record
let record = await table.selectRecordsAsync();
let targetRecord = record.records.find(r => r.id === id);

// Update the record
if(targetRecord) {
await table.updateRecordAsync(targetRecord, {
"Adresses en opération triés": addressString
});
console.log(`Successfully updated record with ID: ${id}`);
} else {
console.log(`Record with ID: ${id} not found.`);
}
 
Screen Shot 2023-09-20 at 4.49.24 PM.png

 

And the result here:

 

Screen Shot 2023-09-20 at 4.49.54 PM.png

See Solution in Thread

4 Replies 4
Alexey_Gusev
13 - Mars
13 - Mars

Hi,
you need array to sort.

input.config()  is always an object containing properties that you set on left side.
You can write:

const x = input.config() // x={address: Array[5]}
const y = x.address  // y=[17,254,38,8,22]

if you set several values on the left side, you need to assign each, like
const a=x.second_value
const b=x.third_value...

to avoid this, ES6 allows to assign object properties to variables with same names.
like 

const {address, second_value,third_value} = input.config ()

but you have only one, so 

const {address} = input.config ()
// other part
console.log(address)
let sorted=address.sort((a,b) => a-b )
console.log(sorted)

 

problem is that you can't do nothing with it, lookup field is read-only

If you want to write result to the table, you need to reorder links (according to their lookup values).
So, you need to query linked table, get all IDs of records you want to reorder, together with their values, sort according to values, and write sorted array of IDs to the linked field (in format :  [ {'id':'rec123qweDSA456'},{'id':'rec456....'}, ....three ID objects more.. ]
the task might be easier if you use selectRecordsAsync at full power (with sorts and recordIDs)
of course, you can write it to table by script, but you can do 
output.set('sorted', variable_with_result)
and then use it in next, 'no code' step, to update record

Sho
11 - Venus
11 - Venus

For example, if the output is like this, it can be used for repeat processing.

const {address} = input.config()
address.sort((a, b) => (a < b) ? -1 : 1);
const sortedAddress = address.map(value => ({
    "address" : value,
}))
output.set("data", sortedAddress);

Sorry, i just have to explain what I mean
I just tried to imagine next step.
Okay, I've sorted the numbers in 'Lookup' and what?

Alexey_Gusev_0-1695227071381.png

..

Pierre-Alexand2
6 - Interface Innovator
6 - Interface Innovator

Thank you very much this helped a lot.

The second stage of this script was to update another field in the record with the sorted list.

Here is the full completed code in case this might be usefull to someone else:

 

// Define the input data
const obj = input.config();
const { address, id } = obj;

// Sort the address array
const sortedAddress = [...address].sort((a, b) => a - b);
const addressString = sortedAddress.join(', ');

// Select table
let table = base.getTable("Entreprises");

// Find the record
let record = await table.selectRecordsAsync();
let targetRecord = record.records.find(r => r.id === id);

// Update the record
if(targetRecord) {
await table.updateRecordAsync(targetRecord, {
"Adresses en opération triés": addressString
});
console.log(`Successfully updated record with ID: ${id}`);
} else {
console.log(`Record with ID: ${id} not found.`);
}
 
Screen Shot 2023-09-20 at 4.49.24 PM.png

 

And the result here:

 

Screen Shot 2023-09-20 at 4.49.54 PM.png