Not possible directly using formula’s. You can do it using noco-scripts.
I took a sample table “Source” which had a “Date” field, and this was to be looked up from another table for season reference to fill in “Season” field
“Season” table had “Start” and “End” to identify range.
I have kept below script verbose to make it easy to understand. You can clean it up & hard-wire as required.
(This script is GPT generated)
// Fill a label field in one table by looking up a date range in another table.
//
// Use case: row in "Source" has a Date; "Seasons" table has Start/End/Title.
// For each Source row, find the Seasons row where Start <= Date <= End and
// copy its label into the chosen field on Source.
const sourceTable = await input.tableAsync('Pick the source table (the one with the date)');
const dateField = await input.fieldAsync('Pick the date field in Source', sourceTable);
const targetField = await input.fieldAsync('Pick the field to fill (e.g. "Season")', sourceTable);
const seasonsTable = await input.tableAsync('Pick the lookup table (e.g. "Seasons")');
const startField = await input.fieldAsync('Pick the "start" date field in the lookup table', seasonsTable);
const endField = await input.fieldAsync('Pick the "end" date field in the lookup table', seasonsTable);
const labelField = await input.fieldAsync('Pick the label field to copy from (e.g. Title)', seasonsTable);
// Normalize any date-ish value to YYYY-MM-DD so we can compare with plain string compare.
function toYmd(v) {
if (v == null) return null;
const s = String(v);
return s.length >= 10 ? s.slice(0, 10) : null;
}
// ---- Load all seasons into memory ----
const seasonsQuery = await seasonsTable.selectRecordsAsync({
fields: [startField, endField, labelField],
pageSize: 100,
});
const seasons = [];
function collectSeasons(records) {
for (const r of records) {
const start = toYmd(r.getCellValue(startField));
const end = toYmd(r.getCellValue(endField));
const label = r.getCellValue(labelField);
if (start && end && label != null) {
seasons.push({ start, end, label: String(label) });
}
}
}
collectSeasons(seasonsQuery.records);
while (seasonsQuery.hasMoreRecords) {
await seasonsQuery.loadMoreRecords();
collectSeasons(seasonsQuery.records.slice(-100));
}
if (seasons.length === 0) {
output.markdown('### No seasons found — aborting.');
return;
}
function findLabel(dateYmd) {
for (const s of seasons) {
if (s.start <= dateYmd && dateYmd <= s.end) return s.label;
}
return null;
}
// ---- Walk source rows and update ----
const sourceQuery = await sourceTable.selectRecordsAsync({
fields: [dateField, targetField],
pageSize: 100,
});
let processed = 0;
let updated = 0;
let unchanged = 0;
let noDate = 0;
let noMatch = 0;
async function processBatch(records) {
for (const r of records) {
processed++;
const ymd = toYmd(r.getCellValue(dateField));
if (!ymd) {
noDate++;
} else {
const newLabel = findLabel(ymd);
if (newLabel == null) {
noMatch++;
} else {
const current = r.getCellValueAsString(targetField);
if (current === newLabel) {
unchanged++;
} else {
await sourceTable.updateRecordAsync(r.id, { [targetField.id]: newLabel });
updated++;
}
}
}
output.clear();
output.text(`Processed ${processed} records...`);
}
}
await processBatch(sourceQuery.records);
while (sourceQuery.hasMoreRecords) {
await sourceQuery.loadMoreRecords();
await processBatch(sourceQuery.records.slice(-100));
}
output.clear();
output.markdown('### Done');
output.table({
Processed: processed,
Updated: updated,
'Already correct': unchanged,
'No date in row': noDate,
'No matching season': noMatch,
});
Output (Season field is auto-populated)
You can also set up NocoDB automation in which case, every time Date field is modified, automation runs by default to update “Season” field in real-tiime.
