Skip to main content

Hello, First time post so apologies in advance...

I have a Deliveries table, The idea is when I select a "ship package" [eg.Floor: Sheathing] it pulls up the Assemble numbers with a lookup field - that correlate to a material list on another table. I then have a script which converts that lookup field into a multiple select field. This all functions correctly.

Now my issue is that i want to select a series of boxes ["Ship" "Ready" Delivered"]. The idea being that i have a script [i have zero programing knowledge and have ai write my scripts], That when "ship" is checked the script hunts through the "Materials Table" using data from  the "# Reformat" field and with each record that is found in "Assembly #" to match one of the many possible numbers it checks the "Picklist" box. My script below says it works but it does not check the any boxes. 

 
 

 

let deliveryTable = base.getTable("Delivery");
let materialsTable = base.getTable("Materials");

// Fetch all records from the "Delivery" table
let deliveryQuery = await deliveryTable.selectRecordsAsync();

// Fetch all records from the "Materials" table
let materialsQuery = await materialsTable.selectRecordsAsync();

for (let deliveryRecord of deliveryQuery.records) {
// Get the names from the "# Reformat" field
let reformatNames = deliveryRecord.getCellValue("# Reformat");
if (reformatNames && reformatNames.length > 0) {
for (let materialRecord of materialsQuery.records) {
let assemblyNumber = materialRecord.getCellValue("Assembly #");
if (assemblyNumber && reformatNames.some(name => name.name === assemblyNumber)) {
// Check if the "Add Picklist" field exists and is a checkbox
if (materialsTable.fields.some(field => field.name === "Add Picklist" && field.type === "checkbox")) {
// Update the "Add Picklist" field to checked
await materialsTable.updateRecordAsync(materialRecord.id, {
"Add Picklist": true
});
}
}
}
}
}

 

Can anyone tell me whats wrong here...? I have tried repeatedly to make this work but is does not.

The next step is when i check "Ready" it will remove the pick list check and click the "Ready" box on the materials table. And after that i Check Delivered and it marks the delivery as Checked. Creating date stamps along the way.  

Beyond that i hope to automate emails at each stage to notify parties of the shipments progress etc. but first the picklist needs to be created. 

Any help is appreciated! - Keith

The issue seems to be that the script is written assuming that Assembly # is a text field, while it is a select, even if it's a single select.

For a select, you can get the value with column.name instead of just column.

So changing
name.name === assemblyNumber
to
name.name === assemblyNumber.name
resolves it.

Full code:

 

let deliveryTable = base.getTable("Delivery");
let materialsTable = base.getTable("Materials");

// Fetch all records from the "Delivery" table
let deliveryQuery = await deliveryTable.selectRecordsAsync();

// Fetch all records from the "Materials" table
let materialsQuery = await materialsTable.selectRecordsAsync();

for (let deliveryRecord of deliveryQuery.records) {
// Get the names from the "# Reformat" field
let reformatNames = deliveryRecord.getCellValue("# Reformat");
if (reformatNames && reformatNames.length > 0) {
for (let materialRecord of materialsQuery.records) {
let assemblyNumber = materialRecord.getCellValue("Assembly #");
output.text(`${assemblyNumber?.name}`)
if (assemblyNumber && reformatNames.some(name => name.name === assemblyNumber?.name)) {
// Check if the "Add Picklist" field exists and is a checkbox
if (materialsTable.fields.some(field => field.name === "Add Picklist" && field.type === "checkbox")) {
// Update the "Add Picklist" field to checked
await materialsTable.updateRecordAsync(materialRecord.id, {
"Add Picklist": true
});
}
}
}
}
}

 

 

Note the output.text I added, that will print out the value of assemblyNumber on each iteration, a bit of "Debugging 101" for you 🙂

Best, Milan - Automable.AI Free Airtable consultation

Hmm, I'm not sure if I fully understand your base structure, but couldn't you use lookup fields for this instead by putting a 'Add to picklist' checkbox field in your main table?

And I've set that up here for you to check out

If you did need that 'Add picklist' checkbox to be in the Materials table instead, it seems like you could use a Repeating Group too.  What issues did you face with that that made you use a script instead?

---

For the date stamp stuff, you could do that by creating automations that trigger whenever the appropriate fields are ticked, and then you'd just update a Date type field with the time the automation ran, and that automation would probably send out the emails too

  

Reply


Terms & Conditions