Skip to main content
Solved

Sorting a lookup field with automation script

P
Forum|alt.badge.img+11

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
 
 
Any idea on how to fix this?
 
I don't understand how to sort the object provided by the input.config().

Best answer by Alexey_Gusev

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

View original
Did this topic help you find an answer to your question?

4 replies

Alexey_Gusev
Forum|alt.badge.img+23
  • Alexey_Gusev
  • Brainy
  • 1126 replies
  • Answer
  • September 20, 2023

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

S
Forum|alt.badge.img+19
  • Sho
  • Inspiring
  • 560 replies
  • September 20, 2023

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);
Alexey_Gusev
Forum|alt.badge.img+23
  • Alexey_Gusev
  • Brainy
  • 1126 replies
  • September 20, 2023
Sho wrote:

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?

..

P
Forum|alt.badge.img+11
  • Pierre-Alexand2
  • Author
  • Participating Frequently
  • 7 replies
  • September 20, 2023

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.`);
}
 

 

And the result here:

 

Reply


Most helpful members this week

Terms & Conditions