Rollup Field - Include Integer blank values

[Karlstens]

Hi All,

I have a Linked Field containing multiple records. One benefit of having a Rollup Field vs a Lookup, is when constructing JSON data the Rollup Field will provide a comma for empty/null values (where a Lookup will only return an array with less elements for any empty lookup returns).

However, it seems my observation is only true for a Rollup Field referring to String data, but not a Numbers field.

In my example above, how can I get the Rollup to return [7, 11, ]. Or for example, for even more linked records, a Rollup to return [1, ,2, , ,3,4,5] ?

I am thinking that, for a workaround I need to create a Formula Field, that contains my Number field data as a string, so that the Rollup Field can then ArrayJoin(values) the data as a string and include the null fields, and then my JSON parser script re-convert that string data back to integer? :sob:

Tell me that there’s some kind of Rollup Trickery that I can employ here so that null/blank() number fields return to a Rollup.

[kuovonne]

Do your rollups have any conditions? What are your rollup formulas? Do you use. ARRAYCOMPACT()?

[Karlstens]

I’ve tried both ArrayJoin and ArrayCompact, both give similar results, disregarding NaN values and returning arrays that are essentially short on elements.

Here is my example Tasks Table, note the empty values, one in the Status, another in the Hours;

Note in Record 2 (Example of Issue) - if a Status is blank, the rollup still includes the blank value so the array can be created with 4 elements [ “Todo”, “In progress”, “”, “Done”], but this isn’t the case for Number values.

For a workaround, I’m having to create a Formula on the Tasks table, concatenating the hours.

And now we can see the empty string value being included;

Another possible solution is to ensure that no numeric cells are blank, and contain a number - I’d be interested in understanding solving this problem in itself and will explore it further - but I feel that what we’re needing here is Airtable to amend the Rollup Field when working with numeric values. Perhaps they can add a numericArray(values) function to RollUp that will return NaN (or rather, just an empty return, similar to how the ArrayJoin works with string data) when a blank value is struck?

On a similar note, it would be overly useful if Lookup Fields had a toggle to also return blank values as a small empty bubble.

[kuovonne]

This looks like a bug to me.

[Karlstens]

It’s annoying, that’s for sure.

Knowing that essentially, my Rollup had the aim of being used within an Automated Script - I decided to write my own Rollup method within a script, which has the benefit of no longer needing Rollup Fields.

Here’s my example script I built within the Scripting app.

let projectTable = base.getTable("Projects");
let taskTable = base.getTable("Tasks");

let projectRecord = await input.recordAsync('Select a record to use', projectTable);
let myTaskArray = projectRecord?.getCellValue("Tasks").map( element => element.id);

let taskQuery = await taskTable.selectRecordsAsync({
    fields: ["Task Name", "Hours"],
    recordIds:myTaskArray
});

let myRollupData = taskQuery.records.map( element => element.getCellValue("Hours"));

console.info(myRollupData);

It returns the expected hours in their correct order, and a null value where expected - perhaps I need to format this to NaN.

EDIT: Here is the Automation that manually creates a Rollup (such as numeric) into Text field and includes falsey values - uses input.config() for the primary RecordID.

let projectTable = base.getTable("Projects");
let taskTable = base.getTable("Tasks");

let inputConfig = input.config();
let projectRecord = await projectTable.selectRecordAsync(inputConfig.recordID, {
    fields: ["Tasks"]
});

let myTaskArray = (projectRecord)?.getCellValue("Tasks").map( element => element.id);

let taskQuery = await taskTable.selectRecordsAsync({
    fields: ["Hours"],
    recordIds:myTaskArray
});

let myRollupData = taskQuery.records.map( element => element.getCellValue("Hours"));

console.info(myRollupData);

await projectTable.updateRecordAsync(inputConfig.recordID, {
    "Hours Array": `${myRollupData}`
})