Help

Re: Update a record from one table to another

3385 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Sharon_Watson
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi All,

Apologies for the lack of scripting ability. I’m hoping to get started if someone can point me in the right direction.

I have 2 tables TableA has records in with following ID, Rate

TableB has the same ID, Rate

I want to build a script that, when run, prompts the user for the ID from TableA and then updates all records (multiple records for ID in TableB) in TableB with the value set on TableID for that ID

Hope this makes sense.

Regards

10 Replies 10

Yep - that can be done in script blocks. Hopefully, someone has done something like this already and will share shortly. If not, check this out and I’ll task this to one of our experienced block script devs.

Thanks Bill. I can’t find anything that does the same as I’m requesting, although I guess it’s a common thing to do. I will try and work it out but will take advantage of your request form in the meantime.

Could you explain a little more about your use case?

  • Do you want a one-time snapshot value from TableA to be placed in TableB for all records and then never updated?

  • Do you want the rate in TableB to be continuously synchronized with the rate in TableA?

  • Do you want periodic snapshot values from only selected records copied over at different times?

  • Do you want a snapshot rate copied from TableA to TableB only on the creation of new records?

Depending on your use case, the code might look a little different. The first two use cases could also be accomplished without a script.

Hi,

Thanks for the reply.

TableA holds a cost (Rate) that is the cost for a day.
TableB holds the bookings based on that Rate.

If the rate changes for a person (ID) then I want to update the rate manually in TableA and then run a script that updates all the records in TableB that match the ID of the change in TableA

The rate will then be fixed and doesn’t need to synchronize

Hope that helps.

Thanks again

It sounds like your use case could possible be solved by using a daily “Rate Schedule” table set up too. See here:

The Scripting block could then help in a simpler way, by simply connecting all unlinked records to “Today’s Rate” record.

Thanks. I don’t think it needs to be that complicated. I use the batch block at the moment but thought it would be easier in scripting. I’m not familiar with java script but it would simply be an update statement in SQL where the ID = the ID specified by the user.

Unfortunately, it is not possible to use SQL statements in scripting block. The logic of SQL statements have to manually translated into the context of the JavaScript scripting API objects and methods.

Thanks I realise that but wondered if there is a equivalent to update a field based on user input, that would get me started.

Hey Sharon:

I’d highly recommend using linked records and lookups (like in @Jeremy_Oglesby’s example) for this use case, as it will save you having to manually run a script every single time you change rates. This will ultimately reduce the chance of errors; for example, if a collaborator updates the rate but forgets to run the script, the data will be wrong.

If you do want to use a script, here’s a quick example that should get you started (you’ll have to change the names of the tables/fields to match your own base).

let tableA = base.getTable('People');
let tableB = base.getTable('Bookings');
let person = await input.recordAsync("Whose rate changed?", tableA);
let query = await tableB.selectRecordsAsync();

// Find all the affected bookings and update them
let updates = [];
for (let booking of bookings) {
    // This assumes you aren't using a linked record field.
    // If you are using a linked record field, you want to do
    // booking.getCellValue('People')[0].id instead, since the cell value
    // is returned as an array of records.
    if (booking.getCellValue('Person ID') === person.id) {
        updates.push({
            id: booking.id,
            fields: {
                // Update the cell values — change this
                'Field to update': 'New value'
            }
        });
    }
}

// Only up to 50 updates are allowed at one time, so do it in batches
while (updates.length > 0) {
    await tableB.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}