Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Data Extensions
494 0
cancel
Showing results for 
Search instead for 
Did you mean: 
shenson
4 - Data Explorer
4 - Data Explorer

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

 

0 Replies 0