Script needed to search for highest number +1 in a field, but in a certain range

Hi all,
I have base a certain table with fields “Production method” and “number” as per attachment.

(The numbers are imported testdata)

The goal/

  • each production type should get an (auto) number.
  • each production should get the highest number available +1in the field from a certain range.

eg. "production-type 1 should get the highest number + 1 available from field "Number. The highest number available must lie in range 30000 to 40000

eg. "production-type 2 should get the highest number + 1 available from field "Number. The highest number available must lie in range 20000 to 30000

When a production method changes value, a new number must be calculated in another
specific range. Business wise this should not occur, but might be needed to catch user mistakes

I was planning on doing this via Automations. “When record is updated” Or “When a record matches conditions” → “Run script”

But I have not much coding experience. Very, very basic.
Can this be done via script? Can I get some pointer or links to learn what I need.

I gues I need
-to get the recordID where the update takes place which value from “Production-method”.

-Based on the outcome from the “Production-method” I need to have code that seek the highest number in a certain range in the “Number” field a do a calculation.

-Insert this calculated number in the “Number” field in the corresonding record.

Please note that I’m a novice user, and have little coding knowledge. We are on the Pro-plan.

I think I have a manual workaround, but by script/automation this will be far more efficient en user-error prone.

Any pointers, examples,remarques,etc would be greatly appreciated.

Kind regards,

Welcome to the community, @Hendrik_Van_Dorssela! :smiley: This is definitely doable with an automation that runs a script. If you have a budget for the script development, message me directly and we can discuss the available options.

Welcome to the forums @Hendrik_Van_Dorssela!

I originally wrote and published this base on Airtable Universe in response to a Stack Overflow question along your lines.

My example isn’t identical and the whole point was to illustrate why trying to mimic Excel is an unsustainable idea. But if you’re willing to get stuck in and/or can’t follow the path of least resistance, which is this:

-then this should be all you need to figure out how to write a script that will do exactly what you’ve described, just this one, totally sustainable time… eventually. :slight_smile:

I think I figured it out.

  • Query on the table
  • new array via filter function on the query setting a range. (using filter function)
  • search for the highest number in that range + 1 = is “new number per range” (using reduce function)

The code looks like crap I guess. Must clean it out. So I cannot share yet, should that be desirable. But it kinda works. The performance isn’t all that, but maybe I should query only on the fields I need, instead of the whole table.

Probably I should provide some information for the end user what is going on.

1 Like

Congrats on working it out! Don’t worry at first about how “clean” it is. You’ll continue to learn more and produce cleaner code with more practice.

That’s always a good approach. :slight_smile: