Faxing with FaxLogic | Boomerang Notification Services for SQL Server

Integrating with FaxLogic

Introduction

Boomerang Notification Frameworks supports three different fax services, MS-FAX, RightFax and the cloud-based service from FaxLogic. This page explains and gives examples of how fax messaging using FaxLogic’s fax service can be integrated and automated with Boomerang Notification Framework.

FaxLogic is a cloud-based internet fax service. FaxLogic is the technology leader in hosted fax solutions that help people and businesses optimize their productivity and document workflow. By combining the best of analog fax, Internet Fax and fax servers, FaxLogic lets you keep your fax machine and give it Internet-powered features.

Sending a Fax Message

To send a basic fax message through FaxLogic using Boomerang Notification Framework is an easy task and can be accomplished with three simple SQL statements.

  1. Create Event (EVENT_MASTER)
  2. Create Job (OUT_FAX)
  3. Release Event (EVENT_MASTER)

The below SQL statement will send a fax cover sheet including receiver name, cover subject and notes.

---- Faxing with FaxLogic --------------------
declare @gKey uniqueidentifier, @jKey uniqueidentifier
 
set @gKey = NEWID()
set @jKey = NEWID()
 
insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Send FAX with FaxLogic')
insert OUT_FAX (gKey, jKey, Receiver_Fax_Number, Receiver_Name, Cover_Note, Cover_Subject, Cover_Template) 
values (@gKey, @jKey, '(615) 823-5005', 'Bob Slydell', 'Cover_Note', 'Cover_Subject', 'default')
update EVENT_MASTER set Status = 0 where gKey = @gKey
---- Faxing with FaxLogic --------------------

Result:Faxing with FaxLogic | Boomerang Notification Framework

Fax Cover Sheet Details

Some cover sheet details like; from company name and date are default values that may be changed in the FaxLogic Administrator Dashboard however most information can be assigned dynamically. To add receiving company name and phone number include the following columns in the SQL statement  Receiver_Company and Receiver_Phone.

The default cover sheet is specified in Boomerang Administration Console but can be omitted (Null) or changed to another layout. In this example cover sheet template “version2″ is used.

For a complete list of available fields see the OUT_FAX page.

---- Faxing with FaxLogic --------------------
declare @gKey uniqueidentifier, @jKey uniqueidentifier
 
set @gKey = NEWID()
set @jKey = NEWID()
 
insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Send FAX with FaxLogic')
insert OUT_FAX (gKey, jKey, Receiver_Fax_Number, Receiver_Name, Cover_Note, Cover_Subject, Receiver_Company, Receiver_Phone, Cover_Template) 
values (@gKey, @jKey, '(615) 823-5005', 'Bob Slydell', 'Cover_Note', 'Cover_Subject', 'TPS Inc', '1-800-222-2222', 'version2')
update EVENT_MASTER set Status = 0 where gKey = @gKey
---- Faxing with FaxLogic --------------------

Result:
Faxing with FaxLogic | Boomerang Notification Framework

SQL Server Reporting Services – SSRS Reports

Boomerang Notification Framework supports all rendering options and functions in SQL Server Reporting Services and may be used to add content to a fax message.

All SSRS reports will be rendered as TIF images when sent as a fax message. If the SSRS report requires parameters the name and value of these should be inserted into CONTENT_PARAMETERS.

To add an SSRS report named “Sales_YTD_Demo” in reporting directory “Boomerang_Demo” add the following SQL statement.

---- SSRS report --------------------
insert EVENT_CONTENT (gKey, jKey, Src_Type, Path) values (@gKey, @jKey, 2, 'Boomerang_Demo/Sales_YTD_Demo')
---- SSRS report --------------------

Result (excluding cover sheet):
Faxing with FaxLogic | Boomerang Notification Framework

File from Network Share

To add a Microsoft Word, Adobe PDF or any other type of file that is supported by FaxLogic and that resides on a network share add the following SQL statement.

-- Word file -------------------
insert EVENT_CONTENT (gKey, jKey, Src_Type, Path) values (@gKey, @jKey, 1, '\\localhost\Temp\temp\Letter.docx')
-- Word file -------------------

The statement will retrieve the file “Letter.docx” from network path “\\localhost\Temp\temp”.

Result (excluding cover sheet):Faxing with FaxLogic | Boomerang Notification Framework

Image or Document from SQL Database

To add a Microsoft Word, Adobe PDF or any other type of file that is supported by FaxLogic and that resides in a SQL database add the following SQL statement. The statement below will use an image from the AdventureWorks database and ProductPhoto table.

-- Image Stream -------------------
insert EVENT_CONTENT (gKey, jKey, Src_Type, Stream) select @gKey, @jKey, 0, LargePhoto from AdventureWorks.Production.ProductPhoto where ProductPhotoID = 73
-- Image Stream -------------------

Result (excluding cover sheet):
Faxing with FaxLogic | Boomerang Notification Framework

Multiple Documents

To add multiple documents and images to a fax message simply insert multiple records into EVENT_CONTENT. The following statement will include a SSRS report, a MS Word file and GIF images from a SQL database.

-- Multiple Document Sources  -------------------
insert EVENT_CONTENT (gKey, jKey, Src_Type, Path) values (@gKey, @jKey, 2, 'Boomerang_Demo/Sales_YTD_Demo')
insert EVENT_CONTENT (gKey, jKey, Src_Type, Path) values (@gKey, @jKey, 1, '\\localhost\Temp\temp\Letter.docx')
insert EVENT_CONTENT (gKey, jKey, Src_Type, Stream) select @gKey, @jKey, 0, LargePhoto from AdventureWorks.Production.ProductPhoto where ProductPhotoID = 73
-- Multiple Document Sources  -------------------

Sending Multiple Faxes

By adding more fax recipients to the table OUT_FAX multiple faxes will be sent within the same event. The following SQL statement will send out “Letter.docx” to all fax recipients.

--- Sending Multiple Faxes -------------------
declare @gKey uniqueidentifier, @jKey uniqueidentifier
 
set @gKey = NEWID()
 
--- select query -------------------------------
select top 10
 @gKey as gKey 
,NEWID() as jKey
,pp.PhoneNumber as Receiver_Fax_Number
,p.FirstName + ' ' + p.LastName as Reciver_Name
,'c:\temp\Letter.docx' as Letter
into #_temp_fax_out
from AdventureWorks.Person.Person p
inner join AdventureWorks.Person.PersonPhone pp on p.BusinessEntityID = pp.BusinessEntityID
--- Sending Multiple Faxes -------------------

Result of select query:

gKey                                 jKey                                 Receiver_Fax_Number       Reciver_Name                                                                                          Letter
------------------------------------ ------------------------------------ ------------------------- ----------------------------------------------------------------------------------------------------- -------------------
0DD67E07-BEBD-4D0C-89F3-5E926D12B3A3 03B4E061-44EE-408F-8CF0-21C6CFE6DBF0 697-555-0142              Ken Sánchez                                                                                           c:\temp\Letter.docx
0DD67E07-BEBD-4D0C-89F3-5E926D12B3A3 AF05F2C4-B4A2-4799-ADD6-91AA15AE0B81 819-555-0175              Terri Duffy                                                                                           c:\temp\Letter.docx
0DD67E07-BEBD-4D0C-89F3-5E926D12B3A3 9FAE10B7-211A-47E5-A27A-0D0DDF8599B7 212-555-0187              Roberto Tamburello                                                                                    c:\temp\Letter.docx
0DD67E07-BEBD-4D0C-89F3-5E926D12B3A3 516A5AC3-7240-44F2-829B-3230728337A5 612-555-0100              Rob Walters                                                                                           c:\temp\Letter.docx
0DD67E07-BEBD-4D0C-89F3-5E926D12B3A3 DFD195A4-A81D-420A-B42A-33AF8426DFD6 849-555-0139              Gail Erickson                                                                                         c:\temp\Letter.docx
0DD67E07-BEBD-4D0C-89F3-5E926D12B3A3 BF5A1926-0635-45AC-B7A0-4FBD89550B82 122-555-0189              Jossef Goldberg                                                                                       c:\temp\Letter.docx
0DD67E07-BEBD-4D0C-89F3-5E926D12B3A3 2CFA32CE-9DD1-4F02-9F39-075E50EB1FA1 181-555-0156              Dylan Miller                                                                                          c:\temp\Letter.docx
0DD67E07-BEBD-4D0C-89F3-5E926D12B3A3 659B3645-80A5-4DB8-BF56-2BC18DAD71F7 815-555-0138              Diane Margheim                                                                                        c:\temp\Letter.docx
0DD67E07-BEBD-4D0C-89F3-5E926D12B3A3 60408D27-1B08-4B75-8103-7561E92A3B4E 185-555-0186              Gigi Matthew                                                                                          c:\temp\Letter.docx
0DD67E07-BEBD-4D0C-89F3-5E926D12B3A3 E925A2A6-7E46-472D-B0BE-A469BD1356ED 330-555-2568              Michael Raheem                                                                                        c:\temp\Letter.docx
 
(10 row(s) affected)

To send faxes out:

--- Sending Multiple Faxes -------------------------
--- Send fax out -----------------------------------
insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Send FAX with FaxLogic')
 
insert OUT_FAX (gKey, jKey, Receiver_Fax_Number, Receiver_Name)
select gKey, jKey, Receiver_Fax_Number, Reciver_Name from #_temp_fax_out
 
insert EVENT_CONTENT (gKey, jKey, Src_Type, Path)
select gKey, jKey, 1, Letter from #_temp_fax_out
 
update EVENT_MASTER set Status = 0 where gKey = @gKey
--- Sending Multiple Faxes -------------------------

File In – Fax Out

All other services in the Boomerang Notification Framework may be combined with the Fax Out services. For example the File In services can be used together with Fax Out. File In will poll one or more network shares for new files and insert them into the IN_FILE table.

Faxing with FaxLogic | Boomerang Notification Framework

After the file has been inserted the stored procedure sp_On_File_In is triggered. In this example any file that is saved in “c:\upload” will be stored in table IN_FILE and then faxed.

--- File In - Fax Out -------------------------
ALTER procedure [dbo].[sp_On_File_In]
(
  @fKey uniqueidentifier
)
as begin
  declare @gKey uniqueidentifier, @jKey uniqueidentifier
 
	set @gKey = NEWID()
	set @jKey = NEWID()
 
	insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Send FAX with FaxLogic')
 
	insert OUT_FAX (gKey, jKey, Receiver_Fax_Number, Receiver_Name, Cover_Note, Cover_Subject) 
	values (@gKey, @jKey, '(615) 823-5005', 'Bob Slydell', 'Cover_Note', 'Cover_Subject')
 
	insert EVENT_CONTENT (gKey, jKey, Src_Type, Stream, Name) 
	select @gKey, @jKey, 0, Data, Name from IN_FILE where fKey = @fKey
 
	update EVENT_MASTER set Status = 0 where gKey = @gKey  
  return 0
end
--- File In - Fax Out -------------------------

Fax Status and Error Messages

Boomerang will receive status and error messages updates from FaxLogic for sent fax message up to 24 hours after submission. These updates are stored in the EVENT_LOG table and will indicate fax status like pending, completed, busy line or invalid number.

Every time there is an update the stored procedure sp_On_Fax_Status is triggered. The stored procedure contains jKey, status code and status message.

sp_On_Fax_Status may be used to take action based on the message returned. In the following example details of any sent fax message with an error will be emailed out.

--- Fax Status and Error Messages -------------------------
ALTER procedure [dbo].[sp_On_Fax_Status]
(
  @jKey uniqueidentifier,
  @status int,
  @msg varchar(500)
)
as begin
 
	if @status !=0 
		begin
			declare @gKey uniqueidentifier, @jKeyOut uniqueidentifier
 
			set @gKey = NEWID()
			set @jKeyOut = NEWID()	
			insert EVENT_MASTER(gKey, Str1, Uid1) 
			values(@gKey, 'Fax Errors', @jKey)
 
			insert OUT_EMAIL(gKey, jKey, Subject, Body) 
			values(@gKey, @jKeyOut, 'Fax Error', CAST(@status as varchar(2)) + ' | ' + @msg + CHAR(10) + CHAR(13) + 'jKey: ' + CAST(@jKey as varchar(50)))
 
			insert OUT_EMAIL_RECIPIENT(jKey, Email) 
			values (@jKeyOut, 'info@fuel9.com')
 
			Update EVENT_MASTER set Status = 0 where gKey = @gKey
		end
  return 0
end
--- Fax Status and Error Messages -------------------------

For more information on error messages and trouble shooting see this page.

Fax Status Messages and Error Codes

Type Message
0 Pending Fax is queued/waiting to transmit
0 Complete – fax (to one recipient) completed successfully, with no errors or warnings
2 Error – an unspecified error occurred
2 Invalid Number – the recipient fax number did not validate (i.e. it’s not a phone number)
2 No Fax Detected – no carrier tone detected on the remote end
2 No Fax Detected – no fax machine detected on the remote end
2 Protocol Error – a protocol mismatch occurred between the sending and receiving fax machines
2 Call Ended Unexpectedly – transmission stopped because the call ended unexpectedly
2 Restricted Number – the recipient fax number is restricted (e.g. international call blocking)
2 Network Error – an unspecified network error occurred
2 Invalid Number – the recipient fax number validated, but it’s inactive/unreachable
2 Line Busy – the recipient fax line is busy
2 No Answer – the recipient fax machine did not answer
2 Network Busy – the network is busy
2 System Error – an unspecified fatal error occurred

Scenario Information

This application scenario is based on the following technologies: Boomerang (Basic Edition) 1.6.0, SQL 2008 (64 bit), SSRS and FaxLogic.

Configuration Checklist

  1. Install Boomerang
  2. Configure FaxLogic

About Boomerang Notification Framework

Boomerang is a collection of notification services for creating applications that generate and send, as well as receiving notifications. Using the Boomerang notification framework, you can quickly create applications to generate and send notifications to customers, suppliers, partners and employees.

The application interface of Boomerang is entirely based on SQL Server (MS) meaning that each notification service has a corresponding table object that represents the service in question. For example to send and email out you would insert a record into the OUT_EMAIL table. To send a fax you would insert a record into the OUT_FAX table.

Download Sample

Faxing with FaxLogic sample