Trying to update records if, "Client_No" matches with javascript

[shenson]

Ive tried doing this and when I do it loops through and keep updating the one and only record, doesn't add the record if the Client_no doesnt match

// @ts-nocheck

var sourceTableName = base.getTable("1_Import");
var destinationTable = base.getTable("2_Enroll");
let createdDateTime;

// Calculate the date range for last month
var currentDate = new Date();
var lastMonthStartDate = new Date(currentDate);
lastMonthStartDate.setMonth(currentDate.getMonth() - 1);
lastMonthStartDate.setDate(1); // Starting on the 1st day of the previous month
var lastMonthEndDate = new Date(currentDate);
lastMonthEndDate.setDate(0); // Ending on the last day of the previous month

console.log("Last Month Start Date:", lastMonthStartDate.toISOString());
console.log("Last Month End Date:", lastMonthEndDate.toISOString());

var qry = await sourceTableName.selectRecordsAsync({
    fields: [
		"Wholesale_Partner_Name", "AFF_Agents", "Client_Name", "Client_No", "Affiliate_or_CNI",
		"Status", "Termination_Date", "Enrollment_Date", "Start_Date", "Welcome_Call_Date",
		"Originally_Enrolled_Debt", "original_draft_amount", "current_draft_amount",
		"Originally_Scheduled_Draft_Type", "Monthly_Draft_Type", "Next_Draft_Date",
		"Successful_Monthly_Drafts", "Last_Transaction_Date", "Last_Amount_Transacted",
		"Total_Transacted_In_Period", "First_Posted_Draft_Type", "First_Draft_Date",
		"First_Draft_Post_Date", "First_Draft_Amount", "Second_Posted_Draft_Type", "Second_Draft_Date",
		"Second_Draft_Post_Date", "Second_Draft_Amount", "Third_Posted_Draft_Type", "Third_Draft_Date",
		"Third_Draft_Post_Date", "Third_Draft_Amount", "Fourth_Posted_Draft_Type", "Fourth_Draft_Date",
		"Fourth_Draft_Post_Date", "Fourth_Draft_Amount", "Fifth_Posted_Draft_Type", "Fifth_Draft_Date",
		"Fifth_Draft_Post_Date", "Fifth_Draft_Amount", "Sixth_Posted_Draft_Type", "Sixth_Draft_Date",
		"Sixth_Draft_Post_Date", "Sixth_Draft_Amount", "Seventh_Posted_Draft_Type", "Seventh_Draft_Date",
		"Seventh_Draft_Post_Date", "Seventh_Draft_Amount", "Eighth_Posted_Draft_Type", "Eighth_Draft_Date",
		"Eighth_Draft_Post_Date", "Eighth_Draft_Amount", "Ninth_Posted_Draft_Type", "Ninth_Draft_Date",
		"Ninth_Draft_Post_Date", "Ninth_Draft_Amount", "Tenth_Posted_Draft_Type", "Tenth_Draft_Date",
		"Tenth_Draft_Post_Date", "Tenth_Draft_Amount", "Eleventh_Posted_Draft_Type", "Eleventh_Draft_Date",
		"Eleventh_Draft_Post_Date", "Eleventh_Draft_Amount", "Twelfth_Posted_Draft_Type", "Twelfth_Draft_Date",
		"Twelfth_Draft_Post_Date", "Twelfth_Draft_Amount", "Last_Draft_Status", "Last_Draft_Date",
		"Commissionable_Month", "CSS_Referral_Payment_Date", "CreatedDateTime"
    ]
});

console.log(qry.records);

// Insert retrieved records into destinationTable
for (const record of qry.records) {
    const clientNo = record.getCellValue("Client_No");
	createdDateTime = record.getCellValue("CreatedDateTime"); // Set createdDateTime for the current record

    console.log("Processing record. Wholesale_Partner_Name:", record.getCellValue("Wholesale_Partner_Name"));
    console.log("CreatedDateTime:", createdDateTime);
	console.log("Client_No:", clientNo); // Add this line to log the clientNo for each record

    // Check if CreatedDateTime is not null
    if (createdDateTime !== null) {
        console.log("Record has CreatedDateTime. Inserting into destinationTable.");

        try {
            const fieldsToUpdate = {
             "DataCopied": true, // Update the checkbox field in the source table
                "Wholesale_Partner_Name": record.getCellValue("Wholesale_Partner_Name"),
                "AFF_Agents": record.getCellValue("AFF_Agents"),
                "Client_Name": record.getCellValue("Client_Name"),
                "Client_No": record.getCellValue("Client_No"),
                "Affiliate_or_CNI": record.getCellValue("Affiliate_or_CNI"),
                "Status": record.getCellValue("Status"),
                "Termination_Date": record.getCellValue("Termination_Date"),
                "Enrollment_Date": record.getCellValue("Enrollment_Date"),
                "Start_Date": record.getCellValue("Start_Date"),
                "Welcome_Call_Date": record.getCellValue("Welcome_Call_Date"),
                "Originally_Enrolled_Debt": record.getCellValue("Originally_Enrolled_Debt"),
                "original_draft_amount": record.getCellValue("original_draft_amount"),
                "current_draft_amount": record.getCellValue("current_draft_amount"),
                "Originally_Scheduled_Draft_Type": record.getCellValue("Originally_Scheduled_Draft_Type"),
                "Monthly_Draft_Type": record.getCellValue("Monthly_Draft_Type"),
                "Next_Draft_Date": record.getCellValue("Next_Draft_Date"),
                "Successful_Monthly_Drafts": record.getCellValue("Successful_Monthly_Drafts"),
                "Last_Transaction_Date": record.getCellValue("Last_Transaction_Date"),
                "Last_Amount_Transacted": record.getCellValue("Last_Amount_Transacted"),
				"Total_Transacted_In_Period": record.getCellValue("Total_Transacted_In_Period"),
				"First_Posted_Draft_Type": record.getCellValue("First_Posted_Draft_Type"),
				"First_Draft_Date": record.getCellValue("First_Draft_Date"),
				"First_Draft_Post_Date": record.getCellValue("First_Draft_Post_Date"),
				"First_Draft_Amount": record.getCellValue("First_Draft_Amount"),
				"Second_Posted_Draft_Type": record.getCellValue("Second_Posted_Draft_Type"),
				"Second_Draft_Date": record.getCellValue("Second_Draft_Date"),
				"Second_Draft_Post_Date": record.getCellValue("Second_Draft_Post_Date"),
				"Second_Draft_Amount": record.getCellValue("Second_Draft_Amount"),
				"Third_Posted_Draft_Type": record.getCellValue("Third_Posted_Draft_Type"),
				"Third_Draft_Date": record.getCellValue("Third_Draft_Date"),
				"Last_Transaction_Date": record.getCellValue("Last_Transaction_Date"),
				"Third_Draft_Post_Date": record.getCellValue("Third_Draft_Post_Date"),
				"Third_Draft_Amount": record.getCellValue("Third_Draft_Amount"),
				"Fourth_Posted_Draft_Type": record.getCellValue("Fourth_Posted_Draft_Type"),
				"Fourth_Draft_Date": record.getCellValue("Fourth_Draft_Date"),
				"Fourth_Draft_Post_Date": record.getCellValue("Fourth_Draft_Post_Date"),
				"Fourth_Draft_Amount": record.getCellValue("Fourth_Draft_Amount"),
				"Fifth_Posted_Draft_Type": record.getCellValue("Fifth_Posted_Draft_Type"),
				"Fifth_Draft_Date": record.getCellValue("Fifth_Draft_Date"),
				"Fifth_Draft_Post_Date": record.getCellValue("Fifth_Draft_Post_Date"),
				"Fifth_Draft_Amount": record.getCellValue("Fifth_Draft_Amount"),
				"Sixth_Posted_Draft_Type": record.getCellValue("Sixth_Posted_Draft_Type"),
				"Sixth_Draft_Date": record.getCellValue("Sixth_Draft_Date"),
				"Sixth_Draft_Post_Date": record.getCellValue("Sixth_Draft_Post_Date"),
				"Sixth_Draft_Amount": record.getCellValue("Sixth_Draft_Amount"),
				"Seventh_Posted_Draft_Type": record.getCellValue("Seventh_Posted_Draft_Type"),
				"Seventh_Draft_Date": record.getCellValue("Seventh_Draft_Date"),
				"Seventh_Draft_Post_Date": record.getCellValue("Seventh_Draft_Post_Date"),
				"Seventh_Draft_Amount": record.getCellValue("Seventh_Draft_Amount"),
				"Eighth_Posted_Draft_Type": record.getCellValue("Eighth_Posted_Draft_Type"),
				"Eighth_Draft_Date": record.getCellValue("Eighth_Draft_Date"),
				"Eighth_Draft_Post_Date": record.getCellValue("Eighth_Draft_Post_Date"),
				"Eighth_Draft_Amount": record.getCellValue("Eighth_Draft_Amount"),
				"Ninth_Posted_Draft_Type": record.getCellValue("Ninth_Posted_Draft_Type"),
				"Ninth_Draft_Date": record.getCellValue("Ninth_Draft_Date"),
				"Ninth_Draft_Post_Date": record.getCellValue("Ninth_Draft_Post_Date"),
				"Ninth_Draft_Amount": record.getCellValue("Ninth_Draft_Amount"),
				"Tenth_Posted_Draft_Type": record.getCellValue("Tenth_Posted_Draft_Type"),
				"Tenth_Draft_Date": record.getCellValue("Tenth_Draft_Date"),
				"Tenth_Draft_Post_Date": record.getCellValue("Tenth_Draft_Post_Date"),
				"Tenth_Draft_Amount": record.getCellValue("Tenth_Draft_Amount"),
				"Eleventh_Posted_Draft_Type": record.getCellValue("Eleventh_Posted_Draft_Type"),
				"Eleventh_Draft_Date": record.getCellValue("Eleventh_Draft_Date"),
				"Eleventh_Draft_Post_Date": record.getCellValue("Eleventh_Draft_Post_Date"),
				"Eleventh_Draft_Amount": record.getCellValue("Eleventh_Draft_Amount"),
				"Twelfth_Posted_Draft_Type": record.getCellValue("Twelfth_Posted_Draft_Type"),
				"Twelfth_Draft_Date": record.getCellValue("Twelfth_Draft_Date"),
				"Twelfth_Draft_Post_Date": record.getCellValue("Twelfth_Draft_Post_Date"),
				"Twelfth_Draft_Amount": record.getCellValue("Twelfth_Draft_Amount"),
				"Last_Draft_Status": record.getCellValue("Last_Draft_Status"),
				"Last_Draft_Date": record.getCellValue("Last_Draft_Date"),
				"Commissionable_Month": record.getCellValue("Commissionable_Month"),
                "CSS_Referral_Payment_Date": record.getCellValue("CSS_Referral_Payment_Date"),
                "CreatedDateTime": record.getCellValue("CreatedDateTime") // Use the original value
            };

                        // Find existing record by Client_No in the destination table
			 console.log("Searching for existing record with Client_No:", clientNo); // Add this line
             const existingRecord = await destinationTable.selectRecordsAsync({
                filterByFormula: `{Client_No} = '${clientNo}'`
            });

            if (existingRecord && existingRecord.records.length > 0) {
                // Update existing record with the new field values
                await destinationTable.updateRecordAsync(existingRecord.records[0], fieldsToUpdate);
                console.log("Record updated successfully.");
            } else {
                // Insert the new record into the destinationTable
                const newRecord = await destinationTable.createRecordAsync(fieldsToUpdate);
                console.log("Record inserted successfully.");

                // Update the checkbox field to true for the source record
                await sourceTableName.updateRecordAsync(record, { "DataCopied": true });


                // Update the "ModDateTime" field with the current date and time
                await destinationTable.updateRecordAsync(newRecord, {
                    "ModDateTime": new Date().toISOString()
                });
                console.log("ModDateTime updated successfully.");
            }
        } catch (error) {
            console.error("Error processing record:", error);
            if (error instanceof Error) {
                console.error("Detailed error message:", error.message);
            }
        }
    } else {
        console.log("Record has no CreatedDateTime. Skipping.");
    }
}

console.log("Processing completed.");