I have this app that does the following: (It's supose to do but is not working properly) :
Load multiple QVD's and concatenate them.
This table created from those QVD's will have fields "key" and "request_status" , request_status can have "Pending","Answered" values.
I then do a loop and check if the request_status is "Answered" in a given row, after that it will store into another new field named AnsweredFlag and give it a value "Answered" so it basicly becomes flagged after the loop.
The flagged keys will then be stored into a qvd and left joined to the original table where all the qvd's are.
My send_email.qvd then won't have the flagged keys and this loop will always send the emails... any clues?
But the issue is that, it stores the values into the table but when I reload the app it seems to not have appended anything to the flags. Please see script:
My table:
Requests_triggers:
Load * FROM [lib://FORMS_DATA (cars2clickvm_radu.chitu)/Deal Monitor Outputs/Deal_Monitor_*_FINAL.qvd] (qvd);
left join(Requests_triggers) LOAD key, vAnsweredFlag FROM [lib://FORMS_DATA (cars2clickvm_radu.chitu)/sent_emails.qvd] (qvd);
LIB CONNECT TO 'SMTP - BREVO (cars2clickvm_radu.chitu)';
// Create a temporary table to collect answered keys TempAnsweredKeys: LOAD '' as TempKey, '' as TempAnsweredFlag AUTOGENERATE 0;
//THIS IGNORES THE ONES THAT DONT HAVE THE VANSWEREDflag BECAUSE IT NEVER LOOPS THROUGHT IT
// Loop through the Requests_triggers For i = 0 to NoOfRows('Requests_triggers') - 1 LET vRecipientName = Peek('KAM', $(i), 'Requests_triggers'); LET vListname = Peek('listname', $(i), 'Requests_triggers'); LET vAnsweredFlag = Peek('request_status', $(i), 'Requests_triggers'); //request status check LET vFeedback = Peek('reply', $(i), 'Requests_triggers'); LET vkey = Peek('key', $(i), 'Requests_triggers'); LET vkey_check = Peek('vAnsweredFlag', $(i), 'Requests_triggers');
//There is always the last keys that's why it doesnt work, i need to find a way to only check the keys added by the loop. IF vAnsweredFlag = 'Answered' and (isnull(vkey_check) or vkey_check<>'Answered') then // Check if vkey exists in Sent // and vkey_check <> 'Answered' // Construct HTML message with personalized content LET vMessageHTML = ' <html> <body style="font-family: Arial, sans-serif; color: #333333; line-height: 1.5;"> <div style="max-width: 600px; margin: auto; border: 1px solid #dddddd; padding: 20px; border-radius: 8px; background-color: #f9f9f9;"> <h2>Hello ' & vRecipientName & '</h2> <h2>List Name : ' & vListname & '</h2> <h2>Feedback : ' & vFeedback & '</h2> </div> </body> </html>';
// Add the key to the temporary table CONCATENATE (TempAnsweredKeys) LOAD '$(vkey)' as TempKey, 'Answered' as TempAnsweredFlag AUTOGENERATE 1;
// Send email using the specified properties SENDMAIL: LOAD 'Sent' as [SendEmail.status], 'Success' as [SendEmail.result], '$(vRecipientEmail)' as [RecipientEmail] INLINE [ status, result Sent, Success ];
// Load and execute the send email action SELECT status, result FROM SendEmail WITH PROPERTIES ( to='czo@cars2click.com', subject='Deal Monitor - You have Feedback', message='$(vMessageHTML)', html='true', fromName='Demo - CZO', fromEmail='maria@cars2click.com' );
// Finalize the temporary table by concatenating with Sent ENDIF; Next;
store TempAnsweredKeys into [lib://FORMS_DATA (cars2clickvm_radu.chitu)/sent_emails_test.qvd] (qvd);
// Now perform a LEFT JOIN outside the loop to update the Requests_triggers table LEFT JOIN (Requests_triggers) LOAD trim(TempKey) as key, TempAnsweredFlag as vAnsweredFlag RESIDENT TempAnsweredKeys;
// Drop the temporary table as it's no longer needed DROP TABLE TempAnsweredKeys;
// Store the final result STORE Requests_triggers into [lib://FORMS_DATA (cars2clickvm_radu.chitu)/sent_emails.qvd] (qvd);
Hi, if you do the join using the same vAnsweredFlag field, but the value has changed (like from null to something), the join wont work as all the fields used for join should have the same values.
You can avoid this renaming the first vAnsweredFlag to something like vAnsweredFlag_prev, so using a different field it will do the join of the changed values, but also the loop you shlud also fill the value of this field for the unprocesssed rows.
Another option coud be: Apply the 'IF' conditions in a temporary table before the loop, then you can loop all the rows of this table without the need of any checks, and the end concatenate the modified rows with the existing and unprocessed rows in the qvd to recreate the qvd with all the keys. You can use Exist() to do the check and only add the unprocessed rows from the qvd.
I would reccomend to have a copy of the qvds while developing this, so in case anything doesn't work as expected you can returns to the original state of the flags.
