How to query the line-number of a record

[Stefan_Kunz]

Hi,

I’m looking for a way to query the “line number” (based on the current manual sorting of a table) when iterating over the records. I don’t have a table column which reflects the actual sorting so I can’t use a column as sort criteria with selectRecordsAsync(). Please see this simplified example:

let table = base.getTable("MyTableName");
let result = await table.selectRecordsAsync();
for (let record of result.records) {
  // if record matches a certain criteria -> output certain record fields
  // including the line number according to the current (manual) sorting sequence
}

Any hints are appreciated!
Thanks, Stefan

[JonathanBowen]

Hi @Stefan_Kunz - think this will do what you want. Given a table like this:

this script:

let table = base.getTable("Table 1");
let result = await table.selectRecordsAsync();
let lineId = 1;
for (let record of result.records) {
  if (record.getCellValue("Name").includes("o")) {
    console.log(lineId, record.getCellValue("Name"));
  }
  lineId += 1;
}

produces this:

A record from result.records doesn’t have an index attribute or similar, so looks like we need to construct it.

JB

[Stefan_Kunz]

Hi @JonathanBowen,

thank you for your reply! Unfortunately it’s not that easy because your lineId counter only reflects the sequence which is returned by selectRecordsAsync() which does not necessarily match the actual sequence of the table. E.g. if you manually move record “Mouse” to the first line in front of “Dog” it will still show the same output as in your example even though I would then expect it to show:
1 “Mouse”
2 “Dog”.

Thanks,
Stefan

[JonathanBowen]

Ah, yes, you’re right. It doesn’t look like it works off the sort order of the table (or more specifically the view) you’re working with. What you could do is use the sort options in the “select records” call:

let table = base.getTable("Table 1");
let result = await table.selectRecordsAsync({
  sorts: [
        {field: "Name"},
    ]
});
let lineId = 1;
for (let record of result.records) {
  if (record.getCellValue("Name").includes("o")) {
    console.log(lineId, record.getCellValue("Name"));
  }
  lineId += 1;
}

You can sort by multiple fields within the table/view.

I guess the downside of this is that the sort it totally set within the script and so doesn’t necessarily match the sort in table at any given time.

JB

[Kasra]

Hi!

If you selectRecordsAsync from a view, then the records will be returned in the same order that they appear in that view. E.g.

let table = base.getTable('Table 1');
let view = table.getView('Grid view');
let query = await view.selectRecordsAsync();
// query.records will be in the order that they appear in "Grid view"

There isn’t really a well-defined sort order for a table, records are only sorted in the context of a specific view. Hope this helps!

[Stefan_Kunz]

Thanks @JonathanBowen! As mentioned in my introductory question I don’t have any columns which could be used to reflect the actual sorting but never mind as the answer from @Kasra already solves the problem.

Thanks for your time!
Stefan

[Stefan_Kunz]

Hi @Kasra! Thanks a lot for this hint - this does the trick! I did not think of the View to contain the displayed record order. Works perfectly now!

Thanks again & best regards,
Stefan

[Chris_Guthrie]

This just helped me greatly! Thanks @Kasra and @Stefan_Kunz for contributing to this topic!

[robinzim]

Yes, querying the view rather than the table preserved the order of the items that were manually sorted using drag-and-drop in the UI. Thank you, @Kasra!