This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Help

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

Topic Labels: Data Extensions
274 0
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
shenson
4 - Data Explorer
4 - Data Explorer
See Solution in Thread

‎Aug 18, 2023 12:14 PM

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 Kudos
Reply
0 Replies 0
Copyright © 2024 Khoros, LLC