Scheduled Database Direct Query

[Joshua T M]

Hello All,

 

I'm looking to implement the below query to run on a schedule to automatically move specific mail in our mailboxes to sent and deleted items; this is related to how we organize the mailboxes.

 

-- Move Sent and Deleted emails

UPDATE h_msg_messages, h_sys_organizations
SET h_msg_messages.h_folder_id = h_sys_organizations.h_custom_11
WHERE LOCATE(CONCAT('- ', h_sys_organizations.h_organization_name, ' -'), h_msg_messages.h_msg_subject) > 0  -- Look for '- Name -'' only in subject
    AND (h_msg_messages.h_folder_id = 113 OR h_msg_messages.h_folder_id = 114)                              -- Support -> Sent & Deleted Folders
    AND h_sys_organizations.h_custom_11 IS NOT NULL                                                         -- Must have a valid folder
    AND h_sys_organizations.h_custom_11 <> 172                                                               -- Ignore organisation

 

Currently the script is ran manually however having a feature to run this automatically on a schedule would be ideal.

 

Thanks!

 

Josh M

[Victor]

@Joshua T M running queries against the DB is a very bad idea. I do not doubt the expertise but mistakes can happen so easily ...

Instead of this why not running a custom made script that invokes Hornbill APIs? For example, you can use https://api.hornbill.com/mail/?op=getMessage to retrieve the message and then you could make use of https://api.hornbill.com/mail/?op=moveMessage. To move the message where it needs to be moved you can use https://api.hornbill.com/data/?op=entityGetRecord for the "Organisation" entity, which will retrieve organisation information including the values in custom fields as well and allow you to process only organisations an/or emails that you want to process...

Using APIs will ensure that you don't accidentally break the data referential integrity.

[Joshua T M]

On 3/21/2019 at 5:27 PM, Victor said:

@Joshua T M running queries against the DB is a very bad idea. I do not doubt the expertise but mistakes can happen so easily ...

Instead of this why not running a custom made script that invokes Hornbill APIs? For example, you can use https://api.hornbill.com/mail/?op=getMessage to retrieve the message and then you could make use of https://api.hornbill.com/mail/?op=moveMessage. To move the message where it needs to be moved you can use https://api.hornbill.com/data/?op=entityGetRecord for the "Organisation" entity, which will retrieve organisation information including the values in custom fields as well and allow you to process only organisations an/or emails that you want to process...

Using APIs will ensure that you don't accidentally break the data referential integrity.

Hi Victor,

I wholly agree with you and appreciate the options you have provided!

Looking at my other posts the API has been a nightmare to configure for other uses, I'll give the above suggestion a go now.

Thank you!

Josh M

[Victor]

@Joshua T M - APIs are APIs they're not really intended for everyday user Looks like you do have some knowledge in the area so hopefully, they won't challenge you too much! ... In any case, any queries or issues come back here and we will do our best to assist