Help

Updating a table record w/ user input; option.fields argument for selectRecordsAsync()

Topic Labels: Scripting extentions
2623 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Shaun_Walker
4 - Data Explorer
4 - Data Explorer

Hi!

I’m outlining a bit here since as of this writing, only see a few topics related to the selectRecordsAsync method. Hoping this is helpful to someone else!

GOAL
My goal here is to update a value in a record selected by the user (e.g. {"Status": "Not Started"} -> {"Status": "In Progress"}). User first selects a record with await input.recordAsync and then the record is updated with updateRecordAsync(fieldId, {"key": "value"})).

BACKGROUND & ATTEMPT 1
First - a sanity check and quick background on my approach here.

As part of this, I don’t need to/want to present a list of all records. For example, I have about 50 records in total, but only about 10 are relevant, and thus only the 10 should be presented to the user for selection.

Since you can render the input.recordAsync from a view, I started by creating a view which is configured how I want it w/ filters, sorting, etc, so that only the 10 records I want are listed. And voila - the input.recordAsync presented just the 10 options. Easy peasy.

But, when it was time to implement the update methodology, I was getting an id error. Basically, I learned that the id value from a view is different from the id value from the underlying record and table, which, I guess makes sense! For posterity: the view id vals are prepended w/ sel while those from a table are prepended w/ rec.

All of this is to say, doesn’t seem like you can prompt a user to select a record from a view and update the underlying table, at least as far as I can tell, without some hackiness…right?? Would be curious if I am way off here.

ATTEMPT 2
So - I pivoted to using the selectRecordsAsync() method from the underlying table with all 50 of the records. Figured I could just use .filter() and pass the resulting 10 records I want to the input.recordAsync call.

But, as a default, selectRecordsAsync() returns only the id and name fields for each record. I referred to the documentation (pasted below), and figured I could pass an array of field names as an options.fields argument, and the method would return the record objects with these fields included. Like so:

    let query = await tasksTable.selectRecordsAsync({
        fields: ['Task', 'Status', 'Client', 'Project']
    });
    output.inspect(query);

But this is not returning anything different and is not throwing any errors.

I checked this post, and understand I can use getCellValue() to retrieve other values, but it seems clunky if I need to filter on 5 or 6 parameters, for example.

Here is the documentation I referenced:

image

QUESTION
So, what is the recommended approach for filtering table records by a number of values in that table? Am I using the selectRecordsAsync() call correctly?

2 Replies 2

Hi @Shaun_Walker - on your first point, I don’t think this is the case. If I run this:

let table = base.getTable("People");
let record = await input.recordAsync('Pick a record', table);
if (record) {
    output.text(`You picked ${record.id}`);
}

Or this:

let table = base.getTable("People");
let view = table.getView("People filtered");
let record = await input.recordAsync('Pick a record', view);
if (record) {
output.text(`You picked ${record.id}`);
}

I get a rec ID in both cases. Items prefixed with sel are the Ids of a selected single select value for that record. So if I run this (where I have a field “Group” which is a single select):

let table = base.getTable("People");
let view = table.getView("People filtered");
let record = await input.recordAsync('Pick a record', view);
if (record) {
  console.log(record.getCellValue('Group'));
}

I get this as the output:

Screenshot 2020-05-13 at 14.33.41

So, you should be able to select a record from a view, get the rec Id and update the underlying table.

JB

Shaun_Walker
4 - Data Explorer
4 - Data Explorer

Thank you! Helpful and you are 100% right on the id aspect. I changed up my naming convention to denote that getCellValue() returns a new object. taskObj is the original returned from input.recordAsync whereas as taskStatusObj is the new one after I call taskObj.getCellValue('Status')

Here is a summary of how I stepped through this on my end in case it’s helpful to anyone else.

// User selected record
taskObj = await input.recordAsync('Task to Track', taskView, {shouldAllowCreatingRecord: true})
console.log('First taskObj:', taskObj) // '{id: "rectF7naKtCaj81Vz", name: "Conjoint and Product Line HW"}'
        
// Get record status from taskObj
taskStatusObj = taskObj.getCellValue("Status") || null;
console.log('First taskStatusObj:', taskStatusObj) // New: '{id: "sellEISliXdy82vbr", name: "Not Started", color: "purpleBright"}'
console.log('Second taskObj:', taskObj) // Same as above: '{id: "rectF7naKtCaj81Vz", name: "Conjoint and Product Line HW"}' 

// Print IDs - they are different 
console.log('First taskObj.id:', taskObj.id) // 'rectF7naKtCaj81Vz'
console.log('First taskStatus.id:', taskStatusObj.id) // 'sellEISliXdy82vbr'

But I then got hung up with updateRecordAsync(), for a different reason. Ended up figuring it out but outlining it here in case it’s helpful to someone else.

First, I was correctly referring to the id from the original object, so taskObj.id from a few lines above, which is correctly prepended with rec. My goal, if the Status is not In Progress I want to automatically set it to In Progress. Seemed pretty straightforward.

if (taskStatusObj && taskStatusObj != null && taskStatusObj.name && taskStatusObj.name != 'In Progress') {
    await tasksTable.updateRecordAsync(taskObj.id, {"Status": "In Progress"})
}

But, here is a screenshot of the editor - was getting a linter error.
image
And here was the error message when I tried to run it.
image
Took me a minute, but learned that because the Status field is a single select, must pass in an object. And voila! This did the trick.

if (taskStatusObj && taskStatusObj != null && taskStatusObj.name && taskStatusObj.name != 'In Progress') {
    await tasksTable.updateRecordAsync(taskObj.id, {"Status": {"name": "In Progress"}})
}