Debtor Contacts and SM Calendar Export to Outlook XP/03/07

Discussions relating to 3rd party solutions (customisations, reports, modules, applications) which are a value-add to the Jiwa product.

Debtor Contacts and SM Calendar Export to Outlook XP/03/07

Postby Hyperus » Mon Feb 18, 2008 3:46 pm

**Edit 07 April 2009 - Special checks for Labour Lines that have Start Time Greater than End Time on any labour lines.
I would strongly recommend running the following TSQL statement to ensure your Labour Line Tables do not have this condition otherwise Outlook may complain when exporting appointments with a start time that is after the end time :-
Select * from SM_JobLabour Where StartTime > EndTime
- use the Job/Task output info from this to fix any lines that are incorrect

Then to avoid further occurrences of this, use the following breakout :-
'------------------------
'Task Labour Changed:-
'------------------------
Code: Select all
If Trim(FieldName)="StartTime" Or Trim(FieldName)="EndTime" Then
  If IsDate(JobObject.Tasks(TaskKey).Labours(LabourKey).StartTime) And IsDate(JobObject.Tasks(TaskKey).Labours(LabourKey).EndTime) Then
    If JobObject.Tasks(TaskKey).Labours(LabourKey).EndTime < JobObject.Tasks(TaskKey).Labours(LabourKey).StartTime Then
      JobObject.Tasks(TaskKey).Labours(LabourKey).EndTime = JobObject.Tasks(TaskKey).Labours(LabourKey).StartTime
    End If
  End If
End If
End Sub
'------------------------

**End of 07 April 2009 Edit


(note - updated ZIP attachment file due to missing tsql permissions for JiwaUser 20/2/2008)
(note - updated ZIP 07 April 2009 to cater for NON-exchange users and a bug with MAPI Public Folders being opened regardless)

Heres a tid-bit that might assist some who have requirements to export either Debtor Contacts or Service Manager Calendar Appointments from Jiwa to Microsoft Outlook XP/2003/2007 - either running in stand-alone pop3/smtp mode OR connected to Microsoft Exchange Server. This code has been tested on Jiwa Versions 6.5.8 and 6.5.9. Note that if you plan on exporting to a public calendar folder or public contact folder, the public folders named in the jiwa breakouts must exist in Exchange Server public folders and must also have correct permissions for the user exporting from Jwa or Jiwa will appear to be unresponsive for the 60 second vbscript default timeout delay.

Attached to this post is a zip with all required files. It contains required breakout code for Jiwa and also 2 x TSQL Stored Procedures for installing into your Jiwa Database.

To install the 2 tsql files below - Just start SQL Query Analyser and exec each of these individually as you would normally run tsql code snippets. Ensure you first set your TEST Jiwa Database as the default database before execution. Never deploy code like this to live until you are sure everything will be ok in test :)

sp_racs_HoursWorked.sql
sp_racs_GrabAllDebtorContacts.sql

Now, the next step is to deploy the Jiwa Breakout text. This code was predominantly tested in Service Manager hence why the 4 menu options described here pertain to the Service Manager Breakout Menus.

Jiwa - Service Manager - Maintenance
Record Menu - Utilities - Manage Breakouts
Left-Click "Form Loaded" breakout and paste the following :-
Code: Select all
FormObject.mRecordCustom(0).visible = True
FormObject.mRecordCustom(0).Caption = "RACS - Export Contacts to PUBLIC 'DebtorList' Folder"
FormObject.mRecordCustom(1).visible = True
FormObject.mRecordCustom(1).Caption = "RACS - Export Contacts to PERSONAL Contacts Folder"
FormObject.mRecordCustom(2).visible = True
FormObject.mRecordCustom(2).Caption = "RACS - Export Appointments to PUBLIC 'JiwaSMDiary' Folder"
FormObject.mRecordCustom(3).visible = True
FormObject.mRecordCustom(3).Caption = "RACS - Export Appointments to PERSONAL Calendar Folder"



Code: Select all
FormObject.mRecordCustom(1).visible = True
FormObject.mRecordCustom(1).Caption = "RACS - Export Contacts to PERSONAL Contacts Folder"
FormObject.mRecordCustom(3).visible = True
FormObject.mRecordCustom(3).Caption = "RACS - Export Appointments to PERSONAL Calendar Folder"



Dont forget to leave the "End Sub" at the end on a line of its own.
Now Left-Click "Form Custom Menu Clicked" Breakout and paste the entire contents of the Breakout_FormCustomMenuClicked.txt file from the JiwaExport.zip attached to this post. Note that this text DOES include the
"End Sub". This also ASSUMES that you do't already have breakout code in here already.... please be cautious here.

Now - goto the top of this breakout code and note these 4 lines :-
Code: Select all
Const OutlookPublicContactsFolderName = "DebtorList"
Const OutlookPublicCalendarFolderName = "JiwaSMDiary"
Const SMCalendarDaysInArrears = 1
Const SMCalendarDaysInAdvance = 1


The first 2 options here need to be set correctly for the Public Folder Exports to function on Microsoft Exchange Server - the names are self-explainatory. You will need to create *Root* Public Folders represented by these names of the CORRECT TYPE in public folders - i.e. DebtorList should be a folder of type "contact items" and JiwaSMDiary should be a folder of type "calendar items". Ensure these have write permissions for users that you require to be able to export from Jiwa.

The days in arrears and days in advanced are fairly easy to change and tune to your liking.

Please test these in a test database and test public folders before considering going live to ensure you understand these export functions and any side-effects they may have.

If you wish to have these menu items in Debtors Maintenance instead of Service Manager for exporting debtor contacts alone, then references in the main breakout will need to change from "JobObject" to "DebtorObject". This can be done using notepad with Search and Replace to replace "JobObject" with "DebtorObject". It will also require the menu breakouts above to be performed in Debtors Breakout Maintenance instead of SM Maintenance using something like this for the menus :-

Code: Select all
FormObject.mRecordCustom(0).visible = True
FormObject.mRecordCustom(0).Caption = "RACS - Export Contacts to PUBLIC 'DebtorList' Folder"
FormObject.mRecordCustom(1).visible = True
FormObject.mRecordCustom(1).Caption = "RACS - Export Contacts to PERSONAL Contacts Folder"


Or

Code: Select all
FormObject.mRecordCustom(1).visible = True
FormObject.mRecordCustom(1).Caption = "RACS - Export Contacts to PERSONAL Contacts Folder"



I welcome any feedback regarding the above... all the usual disclaimers apply with regards to this above... This has been posted with the best intentions to assist others... However, I cannot accept any responsibility if things don't go as planned. I will however do my best to assist should issues arise. PLEASE TEST in a TEST Database before deploying so you can hit any possible snags as early as possible - I cannot stress this strongly enough.

/Roy Adams
Attachments
JiwaExportv102.zip
NEW v1.02 File containing Jiwa Breakout and 2 stored procs required to make Jiwa Debtor Contacts and SM Calendar exports function. Fixed issue with public folders for NON-Exchange Users
(5.32 KiB) Downloaded 241 times
Hyperus
Occasional Contributor
Occasional Contributor
 
Posts: 23
Joined: Mon Feb 18, 2008 1:12 pm
Location: Shailer Park, Queensland

Return to Third Party modules and solutions

Who is online

Users browsing this forum: No registered users and 1 guest

cron