airtable-validate-inputs.js

/*
Create Tables and select the one you need to input data.
Create Validations Table for each Table as TableName-Val.
Create Three Fields as Text in Validations Table as Column, Regex, Remark
Enter Column Names to Validate into Column Field in Validation Table.
Give Regex to validate and Remark to show when invalid data entered.
*/

/* Ask to input data in table whose validation table exists. */
let allTables = base.tables.filter(table => table.name.endsWith("-Val")).map(table => table.name.slice(0,-4))
let tableName = await input.buttonsAsync("Select Table to Input Data", allTables)
let valTableName = `${tableName}-Val`

/* Store validation details for selected table. */
let validationData = {}
let validationsTable = base.getTable(valTableName)
let theData = await validationsTable.selectRecordsAsync()

for(let record of theData.records) {
    validationData[record.name] = {}
    validationData[record.name].label = `Enter ${record.name}`
    validationData[record.name].errorLabel = `Enter VALID ${record.name} (${record.getCellValueAsString("Remark")})`
    validationData[record.name].regex = record.getCellValueAsString("Regex")
}

let askAgain = true

let theTable = base.getTable(tableName)
let theFields = theTable.fields

let recordsCount = 0
while(askAgain) {
    output.clear()
    let rowValues = {}
    recordsCount += 1
    let fieldType
    let fieldCount = 0
    for(let field of theFields) {
        switch(field.type) {
            case "singleLineText" :
            case "email" :
            case "url" :
            case "phoneNumber":
            fieldType = "string"
            break

            case "number" :
            case "percent" :
            case "currency" :
            case "rating" :
            case "duration" :
            fieldType = "number"
            break

            case "date" :
            case "dateTime" :
            fieldType = "date"
            break

            case "checkbox" :
            fieldType = "boolean"
            break

            default :
            fieldType = "none"
            break
        }
        /* If column data can be asked using text box. */
        if (fieldType !== "none") {
            let inputValue
            fieldCount += 1
            /* If validation exists for column, validate input value. */
            if (validationData[field.name]) {
                let inputLabel = validationData[field.name].label
                let inputErrorLabel = validationData[field.name].errorLabel
                let inputFlag = false
                let inputErrorFlag = false
                let inputRegex = new RegExp(validationData[field.name].regex, 'g')
                while (!inputFlag) {
                    if (inputErrorFlag && fieldCount > 1) {
                        output.table(rowValues)
                    }
                    inputValue = await input.textAsync(inputErrorFlag ? inputErrorLabel : inputLabel)
                    if (inputRegex.test(inputValue)) {
                        inputFlag = true
                        inputErrorFlag = false
                    }
                    else {
                        output.clear()
                        inputErrorFlag = true
                    }
                }
            }
            /* Else store input value as it is. */
            else {
                inputValue = await input.textAsync(`Enter ${field.name}`)
            }

            if (fieldType === "string") {
                rowValues[field.name] = inputValue
            }
            else if (fieldType === "number") {
                let numberValue
                try {
                    numberValue = inputValue.includes(".") ? parseFloat(inputValue) : parseInt(inputValue)
                }
                catch (error) {
                    numberValue = 0
                }
                rowValues[field.name] = numberValue
            }
            else if (fieldType === "date") {
                let dateValue
                try {
                    dateValue = new Date(inputValue)
                }
                catch (error) {
                    dateValue = new Date()
                }
                rowValues[field.name] = dateValue
            }
            else if (fieldType === "boolean") {
                rowValues[field.name] = (parseInt(inputValue) === 1)
            }
        }
        /* Else skip the column. */
        else {
            rowValues[field.name] = null
        }
    }
    output.clear()
    output.table(rowValues)
    //@ts-ignore
    let rowSaved = await theTable.createRecordAsync(rowValues)
    output.text("Row created Successfully!")
    let isAgain = await input.buttonsAsync("Enter Next Row", ["Yes", "No"])
    askAgain = isAgain === "Yes" ? true : false
}

output.clear()
output.text(`Total ${recordsCount} rows are added to ${tableName} table.`)