Workflow

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Workflow
Monday, August 18, 2014PrintSubscribe
Reports as Email Attachments, Data Views without Extenders, Azure SDK 2.4

Code On Time release 8.0.8.0 introduces the following features and fixes.

  • Implemented support for attachments in Email Business Rules. Multiple xml tags <attachment/> can be specified in the body of the email template.
     
  • Added SMTP settings to Features page of Project Wizard
     
  • Report handler now supports static method Report.Execute to enable generation of report data on the server. The method accepts filter, sort expression, controller, view, template name, and output format as parameters.
     
  • SQL business rules can generate emails. This allows creating multiple complex emails with optional attachments using the full power of SQL dialect of the database engine.
    1) First, a business rule must set EnableEmailMessages property of the business rules class.
    2) Next, another business rule executing in the same sequence will return a list of data rows with columns titled as "To", "Subject", "Body", etc. to produce a list of outgoing emails.
     
  • Desktop and Touch UI now support parsing of "data-" attributes to enable initialization of data views from user controls without using server-side tags. This feature will be the core capability in http://cloudontime.com virtual pages. We are also considering replacing DataViewExtender references with the new “data-” attributes in the generated pages. For example, a master detail relationship between Categories and Products can be declared with this snippet of XML:
    <div id="categories" data-controller="Categories" data-show-in-summary="true"></div>
    <div id="products" data-controller="Products" 
        data-filter-fields="CategoryID" data-filter-source="categories"></div>
    
       
  • Generate from SQL will include byte fields if "base Table Name" is specified. Base table name will be used to create the controller to ensure the labels make sense.
     
  • Updated "Generate From SQL..." function to add up to 1000 data fields to grid1.
     
  • Added ability to "not create controllers automatically" when creating a new project. This will create a database object filter of "__none" and will not create any controllers.
     
  • If multiple views are available, then page header is not displayed in grid/list below the tabs representing view names in Touch UI apps.
     
  • Touch UI recognizes data-content-framework attribute on custom pages to enable upcoming support for built-in CMS (content management system). The future supported values are "bootstrap" and "" (empty string).
     
  • Custom pages do not fire appscroll events to improve performance of long “bootstrap” pages.
     
  • Wide and Tall pages will disabled sidebar and page header in Touch UI applications.
     
  • First virtual page of the data app will not display  a page header
     
  • Custom controls can use data-page-header attribute to set up the text of the page header in Touch UI apps. The page header is inherited from activator label (if any) and will not be displayed if it matches the first page.
     
  • Azure SDK 2.4 is now supported in Azure Factory project type.
     
  • Implemented enhanced processing of Azure SDK references to prevent duplicates created after Azure SDK upgrade. Azure Factory project do not include a reference to Azure Storage for Azure SDK 2.4. It must be added manually when needed.
     
  • Negative search with quick find now requires a space in front of the sample. So the search sample "to-do" will not be treated as "positive 'to' and negative 'do'".
     
  • Moved resetting of Result.Canceled to beginning of SelectedValue loop to allow PreventDefault on each individual selected row in business rules executed for data view with multiple row selection.
     
  • Report handler uses a more robust method of figuring the base URL of application to ensure correct image URLs in reports.
     
  • Application framework adds "order by row_number__" to "SELECT" requests to ensure consistent results on multi-processor machines running SQL Server. Thank you Patrick Saunders and fgary6861!
     
  • The contents of "virtual" pages marked as data-app-role="page" is wrapped in a div with class app-page-content. This significantly improves the rendering of Bootstrap content pages in CMS.
     
  • Touch UI: Settings menu displays "Settings" label.
     
  • Touch UI: Action Group menus of actions display header text of action group as a header for the panel.
     
  • Touch UI: Method $app.mobile.navigate navigates to a virtual page. The method must be used when programming custom virtual pages to complete navigation.
     
  • Touch UI: The framework will not auto-enhance virtual pages marked as data-enhance="false".
     
  • Touch UI: Custom user controls can implement "delayed" transitions to pages if remote data is required to display the page. The tutorials are coming up this week.
     
  • Touch UI: Action groups with child items are displayed on the sidebar.
     
  • Property "field" can be used to reference field names when configuring filters for Web.DataView.search and $app.execute methods.
     
  • Method $app.execute will execute "Select" command if a command is not specified. It will also use "grid1" as the default view Id.
     
  • Data controllers based on Result Sets produced by business rules will work correctly with field names that contain non-alphanumeric characters or starting with numbers.
     
  • Touch UI: User controls without activators do not have an empty link on the "main" user interface page.
     
  • Added null field check for negative quick find searches.
     
  • Generate button in Project Designer will always be shown if "Browse" is hidden.
     
  • Fixed DotNetNuke project file to create Web.*.config files in /WebApp, not /Sandbox folder.
     
  • Project Designer's "Generate" button will be hidden if User Interface = Touch UI.
     
  • Touch UI: View selector options in context menu will synchronize with the view selection tabs of grid/list.
     
  • Modified "Data Controller" construction selectors on "Database Connection" page.
     
  • Fix the bug causing Quick Find to fail if "," without a following query sample are entered.
     
  • Select "includes" / "does not include" filter in advanced search bar if another search criteria is present will not throw an exception in Desktop UI.
     
  • Fixed the spelling with incorrect processing of map-latitude and map-longitude tags in Touch UI apps.
     
  • Included new translated resources in hr locale. Thank you uremovic!
     
  • Updated lookups to use sort expression of view.
     
  • Moved execution of ControllerAction methods into "InternalExecuteBusinessRules" method to ensure parity between those methods and business rules.
     
  • Minimized simultaneous appearance of multiple progress indicators in Touch UI apps.
     
  • Enhanced visual appearance of Dedicated Login in Touch UI.
     
  • Touch UI:  Summary views are refreshed only if they are visible on the page and are in the direct sight of a user.
     
  • Negative search uses NULL value check on fields to ensure correct output. Previously presence of NULL values in a data row was causing exclusion of correct “negative” data rows from the output.
Saturday, August 16, 2014PrintSubscribe
Sending Emails with SQL Business Rules

Email business rules offer a simple and effective mechanism of creating email notifications for various workflow tasks. For example, a simple notification can be sent out when a new customer account is created. Another example is a notification with attached PDF reports triggered by an update of a product category record.

An email business rule is a static text-based template with placeholders matched to a data controller action command name and execution phase. The template may also include XML-based attachment instructions. A single email notification is generated by application framework when an email business rule is matched to a command activated by application user. Template placeholders are replaced with the field values of the affected data row. If the multiple selection mode is enabled, then a separate notification is generated for each selected data row. Here is an email business rule that produces a notification with two attachments for Categories controller.

An email business rule selected in Project Designer of Code OnTime app generator.

The text of the email business rule script template is shown next. The data value placeholders are highlighted.

Host: smtp.gmail.com
Port: 587
UserName: YOUR_EMAIL_ADDRESS@gmail.com
Password: PASSWORD
EnableSSL: true

From: "Sales Admin" <your_email_address@gmail.com>
To: RECIPIENT@northwind.com
Subject: Category "{CategoryName}" has changed!

Dear Admin,

This product category has been changed.
See attachment for category product details.

System Monitor

<attachment type="report">
    <name>{CategoryName}</name>
    <controller>Categories</controller>
    <view>editForm1</view>
    <filter>
        <item>
            <field>CategoryID</field>
            <operator>=</operator>
            <value>{CategoryID}</value>
        </item>
    </filter>
</attachment>
<attachment type="report">
    <name>{CategoryName} Products</name>
    <controller>Products</controller>
    <sortExpression>UnitPrice desc</sortExpression>
    <filter>
        <item>
            <field>CategoryID</field>
            <operator>=</operator>
            <value type="Int32">{CategoryID}</value>
        </item>
    </filter>
</attachment>

If a specific notification cannot be expressed with a static text template, then consider using SQL business rules to compose an email by writing notification text in the programming language of the database engine.  Database programming languages, such as T-SQL of Microsoft SQL Server, offer enough flexibility to compose a notification of any complexity.

SQL business rules are text-based scripts executed by the database engine. The scripts may include references to data controller fields, properties of BusinessRules class associated with the data controller, URL arguments, and session variables. Developers reference any of these resources as parameters that do not have to be explicitly declared in the scripts.

Application framework binds these “pre-defined” parameters to the corresponding resources, executes the script, collects the output parameter values, and ignores any datasets that may have been produced when the script was executed.

A special mechanism exists in the application framework to force it to pay attention to the output of the SQL business rule script. The developer must supply two business rules that are matched to the same command. The first business rule triggers the “awareness” mode of the application framework. The second business rule produces a dataset that will be captured by the framework. Application framework copies the output data to an instance of System.Data.DataTable class and assigns it to BusinessRules.EmailMessages property. The property implementation iterates over the data table rows and treats each row as a notification that must be sent out. The column names of the table, such as “Port”, “To”, “From”, “Subject”, and “Body” allow the framework to compose correct email messages. The message “awareness” mode is automatically turned off when the last email has been sent.

Here is an example of data controller with two SQL business rules that will generate an email with report attachments when a product category has been updated.

A data controller with two SQL business rules that will generate email notifcations in Touch UI application created with Code On Time.

The first SQL business rule is very simple

Property Value
Type SQL
Command Name Update
Phase After
Script
set @BusinessRules_EnableEmailMessages = 1

The script of the this rule assigns value True to the BusinessRules.EnableEmailMessages property of Boolean type and does nothing else. The implementation of the script may do any other type of processing if needed.

The mode of email message awareness is activated now!

The second SQL business rule will produce a singe data row. The script has a “debug” section that declares parameter values used for testing. Application framework will remove this section at runtime  before asking the database server to execute the script. The highlighted fragments are the output column names.

Property Value
Type SQL
Command Name Update
Phase After
Script
-- debug
declare @CategoryID int
declare @CategoryName nvarchar(5)
select @CategoryName = 'Confections', @CategoryID = 1
-- end debug

declare @CrLf as varchar(2)
select @CrLf = char(13)+ char(10)

select 
    -- from 
    'YOUR_EMAIL_ADDRESS@gmail.com' "From",
    -- to
    'RECIPIENT@nothwind.com' "To",
    -- subject
    'Category "' + @CategoryName + '" has changed' "Subject",
    -- body
    'Dear Admin' + @CrLf + @CrLf +
    'This product category has been changed.' + @CrLf +
    'See attachment for category product details.' + @CrLf + @CrLf +
    'System Monitor' + @CrLf  +
    -- attachment 1
    '<attachment type="report">' + 
    '<name>' + @CategoryName + '</name>' +
    '<controller>Categories</controller>' + 
    '<view>editForm1</view>' +
    '<filter>' + 
        '<item>' +
              '<field>CategoryID</field>' +
              '<operator>=</operator>' + 
              '<value>' + cast(@CategoryID as varchar) + '</value>' +
        '</item>' +
    '</filter>' +
    '</attachment>' +    
    -- attachment 2
    '<attachment type="report">' + 
    '<name>Products in ' + @CategoryName + '</name>' +
    '<controller>Products</controller>' + 
    '<sortExpression>UnitPrice desc</sortExpression>' +
    '<filter>' + 
        '<item>' +
              '<field>CategoryID</field>' +
              '<operator>=</operator>' + 
              '<value type="Int32">' + 
cast(@CategoryID as varchar) +
'</value>' + '</item>' + '</filter>' + '</attachment>' "Body"

Note that STMP parameters, such as Port and Password are omitted from the script. Application framework will use default SMTP settings from the application configuration file. If any of these parameters are needed to be in the message definition, then make sure that the values are included as the corresponding column names (for example, Host, UserName, etc.)

This particular implementation uses @CrLf parameter to inject end-of-line characters in the message. The entire message body is a single concatenated string of text. A real-world implementation may compose the text by iterating through cursors and executing multiple select statements.

If an HTML output is desired then the corresponding tags should be included in the “Body”.

Developers can return any number of “messages” in the output when needed. This may be accomplished by creating a temporary database table and populating it with the messages. The contents of the temporary table are selected when the job is done. Another alternative is to use UNION of several SELECT statements to send a fixed number of messages at once.

This is a sample email notification produced by SQL business rules from the example above.

An email notification with two report attachments produced by Touch UI app created with Code On Time.

Saturday, August 16, 2014PrintSubscribe
Reports at Attachments in Email Business Rules

Application framework allows generating reports on the server. This capability makes it possible to produce reports as attachments of email business rules.

Consider the following Email Business Rules implementing a simple notification executed in response to Update command in Categories data controller.

Property Value
Command Name Update
Type Email
Phase After
Script
Host: smtp.gmail.com
Port: 587
UserName: YOUR_EMAIL_ADDRESS@gmail.com
Password: PASSWORD
EnableSSL: true

From: "Sales Admin" <YOUR_EMAIL_ADDRESS@gmail.com>
To: RECIPIENT@northwind.com
Subject: Category "{CategoryName}" has changed!

Dear Admin,

This product category has been changed.
See attachment for category product details.

System Monitor

<attachment type="report">
    <name>{CategoryName}</name>
    <controller>Categories</controller>
    <view>editForm1</view>
    <filter>
        <item>
            <field>CategoryID</field>
            <operator>=</operator>
            <value>{CategoryID}</value>
        </item>
    </filter>
</attachment>
<attachment type="report">
    <name>{CategoryName} Products</name>
    <controller>Products</controller>
    <sortExpression>UnitPrice desc</sortExpression>
    <filter>
        <item>
            <field>CategoryID</field>
            <operator>=</operator>
            <value type="Int32">{CategoryID}</value>
        </item>
    </filter>
</attachment>

Note the two attachment elements embedded directly in the email message. Each element defines a snippet of XML markup describing the report execution arguments.

The first attachment is produced for Categories data controller. Data is filtered by CategoryID of the modified record. The report is produced for editForm1 view.

The second attachment is produced for Products data controller. Data is filtered by CategoryID and sorted in descending order of Unit Price. The report is produced for grid1 view.

Note the field names of the updated data record referenced in curly braces. Expressions {CategoryName} and {CategoryID} are replaced with the actual values during processing.

Attachment definitions are removed from the email body by application framework. The framework will generate each report using specified arguments and attach the report to the email before it is sent out to the recipient.

Run the application and edit any category. Click OK button to save the changes.

An email business rule will trigger an email notification with two report attachments produced in a Touch UI app created with Code On Time application generator.

The data will be saved after a slight delay.

Check your smart phone device for messages.

An email notification on an Android device generated by an Email Business Rule of an app produced with Code On Time.

This is how the email message may look.

The text of the email notification produced by an Email Business Rule in an app created with Code On Time applicaition generator.

Click on an attachment to see the report data in a PDF reader installed on your device.

An attachment report with Category details displayed in Adobe Reader on an Android device.

An attachment report with a list of Products in changed category displayed in Adobe Reader on an Android device.