Skip to main content

HI there - I’m about to pull my hair out and have been trying to figure this automation out for several hours now.

 

I have a table called “Field Enrollment” where I am tracking “Producers” with their unique farm #, tract #, and field #. I have a concatenate formula to give me “Farm Key” which is Producer & Farm.

 

I have a second table called “Producer Enrollment” that I want to be auto populated by the Farm Key. However, there are cases like in my example where there are duplicate Farm Keys and I only want the one. (A bit more context: In Field Enrollment we are required to track data related to the unique Farm/Tract/Field. In Producer Enrollment, we are required to track other types of data related to the TOTAL farm, so essentially a roll -up from Field Enrollment.) How do I create the unique record?!  

 

Example Field Enrollment table:

 

Example Producer Enrollment table: 

 

Here’s the automation I thought would work and is saying is working (but it’s not) 

 

Argh, and here’s the latest script Chat GPT wrote. Still no new record…

 

// Get the input config

let inputConfig = input.config();

let farmNumber = inputConfig.farmNumber;

let producerName = inputConfig.producerName;

let contractNumber = inputConfig.contractNumber;

 

// Tables

let producerEnrollment = base.getTable('Producer Enrollment');

 

// Check if a record with the same Farm # and Producer Name already exists

let existingRecords = await producerEnrollment.selectRecordsAsync({

    fields: l'Farm #', 'Producer Name']

});

 

let recordExists = existingRecords.records.some(record =>

    record.getCellValue('Farm #') === farmNumber &&

    record.getCellValue('Producer Name') === producerName

);

 

if (!recordExists && farmNumber && producerName) {  // Also check for empty values

    await producerEnrollment.createRecordAsync({

        'Farm #': farmNumber,

        'Producer Name': producerName,

        'Contract #': contractNumber

    });

}

 

Hey ​@AndreaR,

Would you mind explaining the following in further detail?

“I have a second table called “Producer Enrollment” that I want to be auto populated by the Farm Key. However, there are cases like in my example where there are duplicate Farm Keys and I only want the one.”

What do you mean when you say that you want the Producer Enrollment be auto-populated by  the Farm Key?

If there are records with same Farm Key, which one is “the one”?

Sorry I cannot provide a straight forward answer. However, with answers to the above, and some additional details on your use case and context, I’m pretty sure I’ll be able to help (and probably no script is needed).

Mike, Consultant @ Automatic Nation

Hm, does this look right?

If so, I’ve set it up here for you to check out

And this is how the automation looks:

The idea is it’ll check many records it found, and if it didn’t find anything it’ll create one (records length = 0), and if it did it’ll link them together (records length = 1)

 

I also added a thing at the end where, if it found > 1 record (i.e. there’s more than one ‘Producer Enrollment’ record with the same farm key), then it’ll send an email alert

 

 

Hi Adam, wow! That’s looking pretty good! The only thing is that we have hundreds of these so I don’t want to click a “run automation” button. Is there some other trigger that could be used, perhaps “if Farm isn’t empty”? Will test now and report back. THANK YOU!!!

Oh lawd, it worked. THANK YOU. That only took me about 6 hours 😜 Have a good one!

Reply


Terms & Conditions