Jump to content

Scheduled Database Direct Query


Joshua T M
 Share

Recommended Posts

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

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...