Dispatch: From An SQL Source

Dispatch Process

Sync & Save PLUS Dispatch follows these steps each time a sql type (Sql Server, MySql, Oracle, Access, Sybase ASE, ODBC) dispatch job runs:

  1. Sync & Save queries the source database for records to dispatch using the query entered in the Query SQL box.


    • The Query SQL should only return records that have not already been dispatched.
    • Click the preview button to see the resulting records.

  2. Sync & Save transforms the data received using the format configured on the format tab.


    • Click the refresh button on the left side of the screen to populate the first 25 records produced by the Query SQL.
    • Click Populate Fields to refresh the list of fields that can be populated.
    • Map the data you wish to upload by dragging fields from the left side of the screen.
    • The @mobileNumber field is not required. However, if it is included the record status will be set to "Sent" and the record will be delivered to the correct mobile unit, otherwise the status will be set to "Pending".
    • Note that Date Time type fields should be populated with UTC/GMT values.

  3. Sync & Save uploads each dispatch record to the doForms web service which pre-populates the form fields and sends it to the correct mobile device (if specified).

  4. Sync & Save executes the Post Dispatch SQL statement for each dispatch record after receiving a response back from the doForms web service.


    • This step should be used to mark the records as having been successfully dispatched so that they are no longer included in the results of the Query SQL.
    • All the data fields returned by the Query SQL statement are available as parameters for this sql statement. Add an @ symbol before the field name to include the field as a parameter (e.g. @DispatchID above). Note: Use : instead of @ for Oracle.
    • The special parameter fields @DispatchKey and @DispatchStatus (see below) are also available.
    • Records that fail to be dispatched successfully will have the @DispatchStatus = -1 and @DispatchKey = "".
    • Click the preview button to view a sample sql statement prepared with the parameter values replaced.

  5. Sync & Save queries the doForms web service for the status of all current dispatch records and executes the Status Sync SQL statement for each record received.


    • This step is generally used to keep the source database up to date as records advance through the dispatch workflow.
    • The only parameter fields available to this step are @DispatchStatus, @DispatchKey, @DispatchStatusName and @DispatchStatusDate.
    • Leave this field blank if you don't wish to download updated status values from doForms.
    • Click the preview button to view a sample sql statement prepared with the parameter values replaced.

@DispatchKey

The @DispatchKey parameter is populated with the value of the key field for the dispatch record from the doForms web service. The value is an approximately 90 character long text string.

@DispatchStatus

The @DispatchStatus parameter is populated with the following numeric values from the doForms web service:


1.  Pending -- Received by the doForms web service, but no action has been taken.
2.  Scheduled -- Scheduled to be sent to a mobile device.
3.  Sent -- Sent to a mobile device.
4.  Received -- Received by a mobile device.
5.  Viewed -- Viewed by the user on the mobile device.
6.  Rejected -- Rejected by the user.
7.  Completed -- Completed by the user.
10. Recurring -- A recurring dispatch.
11. Unlocked -- Automatically unlocked after timeout has passed.

@DispatchStatusName


The @DispatchStatusName parameter is populated with the name of the corresponding dispatch status.

@DispatchStatusDate


The @DispatchStatusDate parameter is populated with the DateTime value (in UTC) of when the current dispatch status was set.

@DispatchActionStatus


The @DispatchActionStatus parameter is populated with any Status text values sent by an Action control on the form. This parameter is available beginning with version 1.12.11.


Walkthrough

  1. Introduction
  2. System Requirements
  3. Main Screen
  4. Submissions: Adding A New Job
  5. Submissions: Configuring the Export Settings
  6. Submissions: Setting Up the Export Format
  7. Submissions: Using The Submissions Tab
  8. Submissions: Dealing With Errors
  9. Submissions: Handling Repeating Data
  10. Submissions: Exporting To Text Files
  11. Submissions: Exporting To Sql Server, MySQL, Oracle, Access, Sybase ASE, or ODBC
  12. Submissions: Exporting To Excel
  13. Submissions: Exporting To PowerPivot For Excel
  14. Submissions: Exporting to Word
  15. Submissions: Exporting To QuickBooks
  16. Submissions: Exporting To QuickBooks Customer
  17. Submissions: Exporting To QuickBooks Estimate, Sales Order, Invoice, Sales Receipt
  18. Submissions: Exporting To QuickBooks Vendor
  19. Submissions: Exporting To QuickBooks Purchase Order
  20. Submissions: Exporting To QuickBooks Item Receipt
  21. Submissions: Exporting To QuickBooks Bill
  22. Submissions: Exporting to QuickBooks Receive Payment
  23. Submissions: Exporting To QuickBooks Time Entry
  24. Submissions: Exporting To QuickBooks Online
  25. Submissions: Exporting To QuickBooks Online Customer
  26. Submissions: Exporting To QuickBooks Online Estimate, Invoice, Sales Receipt
  27. Submissions: Exporting To QuickBooks Online Vendor
  28. Submissions: Exporting To QuickBooks Online Purchase Order
  29. Submissions: Exporting To QuickBooks Online Bill
  30. Submissions: Exporting to QuickBooks Online Time Activity
  31. Submissions: Exporting To Sage 50 (Peachtree)
  32. Submissions: Exporting To Sage 50 Quote, Sales Order, Sales Invoice
  33. Submissions: Exporting To Sage 50 Customer
  34. Submissions: Exporting To Sage 100 (MAS 90 / MAS200)
  35. Submissions: Exporting To Sage 100 Sales Order, SO Invoice, AR Invoice
  36. Submissions: Exporting To SharePoint
  37. Submissions: Exporting To Salesforce.com
  38. Submissions: Exporting To Evernote
  39. Submissions: Exporting Attachments
  40. Submissions: Exporting PDF Report
  41. Submissions: Exporting A Specific Date Range
  42. Submissions: Filtering Submissions
  43. Submissions: Archive
  44. Submissions: Approve Before Exporting
  45. Submissions: Working With Date And Time Values
  46. Submissions: Working With Select One and Select Multiple fields
  47. Submissions: System Fields
  48. Submissions: Handling New Versions Of A Form
  49. Submissions: Networkfleet
  50. Submissions: Fleetmatics
  51. Lookup Table: Adding A New Job
  52. Lookup Table: Settings
  53. Lookup Table: From A Sql Source
  54. Lookup Table: From A File Source
  55. Lookup Table: From QuickBooks
  56. Lookup Table: From QuickBooks Online
  57. Lookup Table: From Sage 50
  58. Lookup Table: From SalesForce.com
  59. Lookup Table: From A SharePoint List
  60. Lookup Table: From NetworkFleet
  61. Lookup Table: From Fleetmatics
  62. Lookup Table: Filtering Records
  63. Dispatch: Adding A New Job
  64. Dispatch: Settings
  65. Dispatch: Format
  66. Dispatch: From An SQL Source
  67. Dispatch: From QuickBooks
  68. Dispatch: Tables And Repeating Sections From An SQL Source
  69. Dispatch: From A File Source
  70. Dispatch: Tables And Repeating Sections From An XML Source
  71. Dispatch: From Salesforce.com
  72. GPS: Adding A New Job
  73. GPS: Configuration
  74. Options: Calculations
  75. Options: Encryption
  76. Options: Proxy Server
  77. Options: Email Notifications
  78. Connecting to Sharepoint.com
  79. Connecting to Sharepoint
  80. Connecting To Cloud File Systems
  81. Connecting To SFTP
  82. Connecting To QuickBooks
  83. Connecting To QuickBooks: User Account
  84. Calculating tax on a form for QuickBooks
  85. Scheduling Jobs
  86. Job Log
  87. Run Jobs From An External Application
  88. Sending Information To Support
  89. Sending Job Configuration to Support
  90. Licensing
  91. Upgrading
  92. Backing Up And Restoring
  93. Webservice Quota
  94. Release Notes
  95. Release Notes 1.15.x
  96. Release Notes 1.14.x
  97. Release Notes 1.13.x
  98. Release Notes 1.12.x
  99. Release Notes 1.11.x
  100. Third Party Software

Feedback and Knowledge Base