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

[Hendrik_Van_Dor]

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,

[Justin_Barrett]

Welcome to the community, @Hendrik_Van_Dorssela! :grinning_face_with_big_eyes: 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.

[Dominik_Bosnjak]

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:

Justin_Barrett:

have a budget for the script development

-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. :slightly_smiling_face:

[Hendrik_Van_Dor]

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.

[Justin_Barrett]

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.

Hendrik_Van_Dorssela:

The performance isn’t all that, but maybe I should query only on the fields I need, instead of the whole table.

That’s always a good approach. :slightly_smiling_face:

[Hendrik_Van_Dor · Accepted solution]

I did not practice anymore (so still NooB) . I’m having a small issue concerning the opening post.

I’m able to search trough a range in a field for the highest number and do +1 as per code below.
The issue is that nothing is returned when I search for a range where 300001 does not exist.
So I use the reduce function on an empty range. My goal is : if 300001 does not exist then ‘newnummer’ = 300001. Maybe this is purely a JS question but even with Stackoverflow I could not wrap my head around this. Is it possible to use an “offset value” in the reduce function? I’m having trouble with syntax.

(Or hire a consultant?)

Blockquote

let set2 = query.records.filter(record => record.getCellValueAsString(‘nummer’) > 300000 && record.getCellValueAsString(‘nummer’) < 400000);
let groot2 = set2.reduce((prev, curr) => prev.getCellValue(‘nummer’) >= curr.getCellValue(‘nummer’) ? prev : curr);
let newnummer = groot2.getCellValue(“nummer”) + 1;

Blockquote

[Justin_Barrett]

@Hendrik_Van_Dorssela First off, your attempt to format the code using a blockquote didn’t work. For one thing, blockquote formatting is different from code formatting. Second, quoting something isn’t done by putting two blockquote blocks around the text. That’s why the two blockquotes you made didn’t work. A default blockquote just contains the literal text “Blockquote”, as you can see above. To actually mark something as a quote, select the text, then choose the blockquote toolbar button, which will wrap the quote syntax around your selection. Similarly, to style something as code, select the text, then click the </> button on the toolbar.

Now on to your issue, which I believe is focused in this line:

Hendrik_Van_Dorssela:

let set2 = query.records.filter(record => record.getCellValueAsString(‘nummer’) > 300000 && record.getCellValueAsString(‘nummer’) < 400000);

There are two problems here that I can see…

First, you’re retrieving cell values as strings, but comparing those strings against literal numbers. If the {nummer} field is a number, and you want to compare it against a number, then use the getCellValue method. That said, JavaScript sometimes auto-converts values for comparison, so this comparison probably still worked anyway. However, the comparison that you’re making is still the reason that you’re not getting the desired result.

You say that you want to add 300001 if it doesn’t already exist, but your first filter condition is that the number be greater than 300000; i.e. 300001 or higher. If you only have a record with the number 300000, then nothing will be returned after filtering the records. The easiest fix for this is to use >= as the first comparison operator. That will include the record numbered 300000, and the rest should give you a new number of 300001.

[Hendrik_Van_Dor]

Hi, Justin.
Thank you for the how-to poste something.

I have completely misexplained my problem. My bad.
My issue was: I have a list of numbers let’s say with the largest number 36521.

My goal was to search this list with different ranges. eg. 0-10000. 10001-15000. Numbers in these 2 ranges already exist in the list. When I was looking in range 300000-400000 none of the numbers in that specific range exist yet in the list I was searching in. So my goals was if to: If >=300000 does not exist, create 300000 in the list (So in fact create a new range) and continue from there. Sorry for my bad explanation.

The reduce function gave an error reducing on “set2” when it was empty.

So I just did a IF (set2.length > 0) { \Do normal code}
else { let nummer = 300000 \and do other code}

I was thingking of tinckering with the reduce function but with the simple IF ELSE statement it also works. I use getCellValue now.