Duplicating records by scripting and assigning them to a different single select feature

[Jakub_Szerszen]

Hi guys :slightly_smiling_face:

Happy Friday!

I was trying to adapt one of the available scripts. However, I failed badly.

Opposite to the script I shared above, I’m using only two tables and in the main, I don’t want to create a template but I want to take a record (only a product name field) from the first table and incorporate it into the second table and assignee this record to a specific client (for that column, I’m using a single select feature). Those two tables are linked to each other by the product name column.

In other words, the first table is containing all our products, while the second table contains multiply of these duplicated records but each of these records is assigned to a different client. Each client’s view is tailored and depending on their profile we share only products that are relevant to their profile.

As our list of partners is growing, this task is becoming time-consuming as each time we need to perform it manually but with the sort of semi-automation, we could actually save plenty of time.

Would appreciate any help in this regard :slightly_smiling_face:

[JonathanBowen]

Hi @Jakub_Szerszen - What you are describing sounds doable. A few thoughts:

• Generally, I wouldn’t use a single select field for clients. Clients feels like it should have its own table, which might make the base structure closer to the example script (but irrrespective of the script, clients in its own table is a good move).
• How are you deciding which clients get which product? Do all clients get all products?
• What’s the workflow you want here? You add a new product in the Products table, run the script and it creates a record in the second table for each client/product combination?

[Jakub_Szerszen]

Hi @JonathanBowen

Thanks for your message.

JonathanBowen:

Generally, I wouldn’t use a single select field for clients. Clients feels like it should have its own table, which might make the base structure closer to the example script (but irrrespective of the script, clients in its own table is a good move).

It would be rather a bad solution for us due to the nature of our business. I don’t like when a base is stuck with dozen of tables. I like to keep each base with a maximum of 5-6 tables and as we cooperate with approximately 150 different partners you could imagine how messy a base could become. :slightly_smiling_face:

JonathanBowen:

How are you deciding which clients get which product? Do all clients get all products?

It’s purely a client’s decision. During the onboarding process, we’re interviewing a client and based on their answer we know how we want to tailor our portfolio for them.

JonathanBowen:

What’s the workflow you want here? You add a new product in the Products table, run the script and it creates a record in the second table for each client/product combination?

We already have the solution in place (automation is running a script) when a new product is added to the main table. When a product matches certain conditions, the automation is adding a new product to our partners’ catalogues. However, the process is fully manual when we onboard a new client and need to build their view from scratch. Due to multiply records and a large number of our products, this is becoming a really time-consuming task. While with sort of semi-automation this task could be reduced to about 5 minutes.

I designed the example base
I’m working with. Our original base is more sophisticated but that should give you a good idea of the workflow.

[Jakub_Szerszen]

I think I found the root of the issue where my script is failing.

let productCatalogue = base.getTable('Product Catalogue');
let customers = base.getTable('Clients Roadmap&Portfolio');

let record = await input.recordAsync('Select a game', productCatalogue);
if (record) { 
    output.text(`You selected ${record.getCellValueAsString('name')}`);
    output.text(`Adding ${record.getCellValueAsString('name')} to the client\s view ... please wait`);

     let gameTitle = await productCatalogue.selectRecordsAsync();

    let selectedGame = [];

    for (let name of gameTitle.records) {
        selectedGame.push(
            {
                fields: {
                    'For View': {name: 'Omega'},
                    'Game Name': {id: name.id}
                }
            }
        )
    await customers.createRecordsAsync(selectedGame);

    output.text('Game added successfully to the client\s view')
}        
    
} else {
    output.text('Game cannot be added to the client\s view as it\s already available in the client\s view!!');
}

The script is failing on the ‘Game Name’ field and accordingly to the message displayed on airtable

Linked records field value must be an array of objects with property ‘id’ corresponding to linked record id.

Does anyone know what I should put in the line for Game Name? :pray:

[JonathanBowen]

If Game Name is a linked field, the value you send needs to be an array of objects, not a single object, so this line:

'Game Name': {id: name.id}

should be:

'Game Name': [{id: name.id}]

(notice square brackets around the object. It doesn’t matter if you expect to link only a single record (1 object) - it must still be inside an array.

[Jakub_Szerszen]

thank you @JonathanBowen! The script is working now :blush:

[Jakub_Szerszen]

Hey @JonathanBowen

If could I ask you one more question. How could I implement into this script a conditional statement?

    for (let name of gameTitle.records) {
        selectedGame.push(
            {
                fields: {
                    'For View': {name: 'Omega'},
                    'Game Name': {id: name.id}
                }
            }
        )
    await customers.createRecordsAsync(selectedGame);

I’d like to add another line into this script for what we call a delivery. I’d like airtable to fetch information from the product catalogue and fill the delivery column depending on the product type.

I tried to use

'Delivery': if(type === “xxx”) { delivery = “ready to be released}

but then everything was underlined.

[JonathanBowen]

@Jakub_Szerszen - you should be able to do this with a “ternary” expression:

developer.mozilla.org

Conditional (ternary) operator - JavaScript | MDN

The conditional (ternary) operator is the only JavaScript operator that takes three operands: a condition followed by a question mark (?), then an expression to execute if the condition is truthy followed by a colon (:), and finally the ...

Something like:

'Delivery': type === 'xxx' ? 'ready to be released' : 'some other value'