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.");