V7.3 – Dynamic pick lists & web services

One of the most common features our customers use is dynamic pick lists (or radio/checkbox controls). Examples abound such as a List of products for a Sales Order / PO, Number of available vacation days for a Leave Approval, List of projects for a Time Sheet and many others. With frevvo, you could always setup dynamic options using a business rule written in JavaScript.

/*member productCode, productName, resultSet*/
var x;
if (form.load) {
eval(‘x=’ + http.get(‘http://localhost:18080/database/BIRT/allProducts’));
var opts = [];
for (var i=0; i < x.resultSet.length; i++) {
if (x.resultSet[i]) {
opts[i] = x.resultSet[i].productCode + ‘=’ + x.resultSet[i].productName;
}
}
Products.options = opts;
}

V7.3 – no JavaScript

Products-WS

Selection Control connected to a web service

Starting with V7.3 (late August 2017), you can select an Options Source in the Properties pane for the selection control (drop down, radio or checkbox). By default, the Source is Design Time which simply means the choices are static and the designer specifies the list of options by typing them in at design time. But, you can now select Web Service. If you do that, three additional properties appear below:

 

  • Options URL: This is the end point for the web service. If you’re using the database connector,  it’ll be the same as the URL used in the JavaScript rule above. The assumption is that this call returns either XML or JSON.
  • Value binding: the path in the returned XML or JSON that determines the value of the select control (what’s in the submission).
  • Label binding: the path in the returned XML or JSON that determines the label of the select control (what the user sees).

That’s it. The URL can also be dynamic.

Edmunds

Selection control connected to the Edmunds API

For example, the screenshot at left shows a checkbox control that’s connected to the Edmunds API. The Options URL depends on the Year since that web service API returns Car Makes for a particular year. The URL is set to:

https://api.edmunds.com/api/vehicle/v2/makes?state=new&year={Year}&view=basic&fmt=json&api_key=<key hidden>

When the user types something in Year, the web service is automatically called, the results are refreshed and the choices in the Car Makes checkbox will change based on whatever the web service returned.

V7.3 will be released at the end of August. Visit our website and sign up for a free 30-day trial if you’d like to learn more or contact us for more information.

How To: Dynamic email addresses

A question we get asked frequently is how to send an email from a form or flow submission where the email address of the recipient is dynamically determined at runtime. For example, I have a drop down control and if the user selects HR, I want the email to go to the HR Department (hr@example.org) and if the user selects Finance, I want to send it to Finance (finance@example.org).

It’s very easy in frevvo.

1. Create a hidden Email control in your form.

AdminEmail

Drag & drop an Email control in your form. In the Properties panel, uncheck Visible so the control is hidden from the end user. Name the control Admin Email. We assume that you already have a drop down control (in the screenshot, it’s called Department and the choices are HR and Finance).

When the user selects HR from the Department drop down, we’ll set the Admin Email hidden control’s value using a business rule.

2. Set the Admin Email address using a business rule

Create a simple business rule.

if (Department.value == ‘HR’) {
AdminEmail.value = “hr@example.org”;
} else if (Department.value == ‘Finance’) {
AdminEmail.value = “finance@example.org”;
} else {
AdminEmail.value = null;
}

In frevvo’s Winter release (expected in late 2017), you’ll be able to create these type of rules visually with the Visual Rule Builder.

3. Use the Admin Email control in the To: email

AdminEmailWizard

Email Wizard that uses a control template

In any Email wizard, you can use the Admin Email hidden control for the To:, Reply to:, Cc: and other fields. In the above example, we’ve used it for the To: field.

That’s it. When the User selects HR, the rule will set the Admin Email control to hr@example.org. When the form is submitted, the Admin Email template in the To: field will be evaluated and will resolve to the value of the corresponding control (hr@example.org). The email will be sent to the dynamically resolved email address.

frevvo + Google Apps: Part 4. Updating a Google Sheet

Does your organization uses Google Apps? You can combine frevvo + Google Apps to create some really cool and useful solutions. In Part 1 of this series, we saw how you can save (write) submissions to a Google Sheet. In Part 2, we saw how you can use the Google Distance Matrix API to automatically calculate mileage and reimbursement amounts. Part 3 showed you how to create dynamic pick lists from a Google Sheet (read).

google-ss-loc-ext

Google Sheet: Employee Location and Extension

Today, we’ll take a look at how you can update existing values in a sheet. It’s another common scenario that can be used for a variety of tasks (e.g. limiting the number of submissions for a particular form or creating a sequentially increasing counter). We’ll use this sample Google Sheet to discuss. It has a row for each employee: Employee Id, Location and Extension.

First, it’s important to note that Google Sheets is not a transactional system like a database and results can be unpredictable if multiple users update the same Sheet at the same time.

As before, you have to first get an access token. This is a one-time step. Login to your Google account and then visit: https://app.frevvo.com/google/consent. Follow the prompts and copy the access token and save it. Detailed documentation is here.

We’ve created a simple example form. Select the employee, a location and a new extension number. Click the Update Google Sheet button, wait a few seconds and see that the sheet was successfully updated. We did this using this rule:

if (UpdateGoogleSheet.clicked) {
var eid = EId.value; // Unique key in the Google Sheet row
var headers = ‘{“user”:”<google id>”,”password”:”<access token>”}’;
var updatequery = ‘/google/spreadsheets/update/key/<spreadsheetkey>?wsname=Locations&query=employeeid=”‘ + eid + ‘”‘;
var updateparams = ‘&updates=location=’ + Location.value + ‘,extension=’ + Extension.value;

eval(‘x=’ + http.put(updatequery + updateparams, null, headers, false));
}

  1. It’s triggered by clicking on the Update Google Sheet button.
  2. We setup headers and an update query using your access token and spreadsheet key (the long ID in the URL of the Google Sheet).
  3. Add updateparams: we’re updating location and extension with new values.
  4. Run the update – perform an http.put() and eval the results.

Try it yourself using the sample form.

Interested in learning more? Visit our website to view examples, watch videos, sign up for a free 30-day trial or contact us for more information.

Like what you see? Check out Part 5 which shows you how you can save documents generated by your frevvo form to Google Drive.

 

frevvo + Google Apps: Part 3. Dynamic Pick lists.

Does your organization uses Google Apps? You can combine frevvo + Google Apps to create some really cool and useful solutions. In Part 1 of this series, we saw how you can save submissions to a Google Sheet and in Part 2, we saw how you can use the Google Distance Matrix API to automatically calculate mileage and reimbursement amounts.

employees-sheetToday, we’ll take a look at dynamic pick lists (drop downs). It’s a very common scenario and, with frevvo, you can use business rules to dynamically initialize the options (choices) in a pick list from a Google Sheet. We’ll use this sample Google Sheet to discuss. It has a row for each employee: First Name, Last Name, Employee Id.

First, you have to get an access token. This is a one-time step. Login to your Google account and then visit: https://app.frevvo.com/google/consent. Follow the prompts and copy the access token and save it. Detailed documentation is here.

We use a rule to read information from the Sheet and populate the employee pick list. Here’s the relevant business rule:

var x;

if (Connect.clicked) {
var headers = ‘{“user”:”sales@frevvo.com”,”password”:”<access token>”}’;
var readquery = ‘/google/spreadsheets/query/key/<spreadsheetkey>/w/Employees’;
eval(‘x=’ +http.get(readquery,headers));

var opts = [”];
for (var i = 0; i < x.results.length; i++) {
opts[i + 1] = x.results[i].employeeid + ‘=’ + x.results[i].firstname + ‘ ‘ + x.results[i].lastname;
}
EId.options = opts;
}

  1. It’s triggered by clicking on the Connect button.
  2. We setup headers and a query using your access token and spreadsheet key (the long ID in the URL of the Google Sheet).
  3. Run the query – perform an http.get() and eval the results.
  4. Parse the results into an array. The array elements are “hagen=Walter Hagen” etc.
  5. Set the options to the array.
googleapps-picklist

Dynamic pick list from a Google Sheet. Click the image to try it.

The pick list will display the actual names (Walter Hagen, Alexa Stirling etc.) whereas the values returned upon selection will be the ids (hagen, stirling etc.) so it’s easy to perform further lookups.

You can try it yourself by clicking this link or on the image.

Interested in learning more? Visit our website to view examples, watch videos, sign up for a free 30-day trial or contact us for more information.

Like what you see? Check out Part 4 which shows you how you can update a Google Sheet with values from a form.

[Infographic] Why Hybrid Cloud?

 Tell me, Why Hybrid Cloud?

Why hybrid cloud? Why not hybrid cloud is the question you’ll be asking yourself after this article!

Hybrid Cloud: “A hybrid cloud is an integrated cloud service utilizing both private and public clouds to perform distinct functions within the same organization.” (http://www.interoute.com/cloud-article/what-hybrid-cloud)

The Road to Hybrid Cloud. The cloud computing model continues to create a larger and larger footprint in the IT landscape as enterprises pursue the model’s benefits including but not limited to: 1) shared resources 2) facilitated access to information and resources from anywhere on any device 3) on demand provisioning and reallocation of computational resources. The greatest benefits are typically achieved using public cloud services however most organizations are unwilling to put ALL their data (particularly sensitive information) into public cloud solutions. In order to still reap the benefits of the cloud computing model, organizations will implement some solutions on internal private cloud platforms.

What is Hybrid Cloud and How Does It Work. A hybrid cloud refers to an environment that runs both public and private cloud services AND provides a way to to share data between them. Typically the data shared with public cloud services is limited and secured/encrypted to reduce exposure. Exposing a server within your firewall to a public cloud service is not what we mean by hybrid. A hybrid cloud connects 2 or more internal and external cloud platforms in such a way that they can exchange information.

What Are the Advantages. A hybrid cloud allows organizations to leverage the efficiency gains of the cloud computing model without having to expose all their data to public cloud services by providing mechanisms to securely share data. Once mechanisms have been defined for exchanging data between internal and external cloud platforms, CIOs can incrementally control what data is exposed to public cloud solutions. The ability to integrate internal data with external/public cloud services is hugely beneficial (if not required) to leverage public cloud services in a compelling and useful fashion.

The Benefits of frevvo. When you sign up for an account on app.frevvo.com, you’re using frevvo in the public cloud. If you deploy frevvo within your organization, you are setting up frevvo as a multi-tenant, private cloud solution. Either way, there are many examples of frevvo participating in hybrid cloud architectures. A frevvo account running in the public cloud could be configured to securely access LDAP information running within a private cloud. Enterprises running frevvo within their private cloud infrastructure can write data to google spreadsheets (public cloud). Another example is utilizing frevvo’s database connector to allow frevvo from either a public or private cloud to integrate with any back-end relational database.

In Summary. This cloud computing phenomenon migrates both, public cloud and private cloud services, to solve your business needs. From small business to large enterprises, being able to keep private information secure as well as hosting public information is very beneficial. As noted on the infographic (pictured to the left) using frevvo in the hybrid cloud is a business strategy to lead you to successful business agility. Our cloud based product is mobile ready, with a drag & drop easy design, that allows you to create custom forms and workflows to increase business agility.

Click here to view the infographic in full. You can learn more about frevvo on our website as well as join today and receive a free 30 day trial!!.

Dynamic database forms – easy!

It’s a very common business need. Enter a client id and you want client details to automatically fill in from the DB. Select an order and you want to view/edit order line item details. The problem is, it’s not easy to do even for programmers. There are all the details of connecting to the database, managing shared DB connections, error handling, transforming data back and forth between SQL and HTML forms, security/authentication and so on.

Not so with frevvo. It’s amazingly simple with our built-in Database Connector. You can find many details and try it out yourself by following our Database Connector Tutorial.
1. Write your SQL query in the DB Connector

SELECT p.productName as product, o.quantityOrdered as quantity, o.priceEach as price, p.productDescription as description, p.MSRP FROM OrderDetails o, Products p WHERE o.productCode=p.productCode and o.orderNumber={onum} ORDER by o.orderLineNumber

The {onum} is a variable representing the order number. It will get a list of line items for a particular order.

Database Query

2. Add it as a data source to your form or workflow
Generate an XSD for your query (using a sample order) simply by visiting the URL for your query in the DB connector. Save the XML and upload it to frevvo as a data source.

XML Schema

3. Generate the form with the click of a button
Drag/drop to customize as desired.Click the + icon for the data source and frevvo will generate controls. Since this query can return multiple line items, frevvo will generate a repeating section. We’ve changed it to a Table (grid) again just by modifying a property.

Generate FormCustomize

4. Link the form to the SQL query in the DB Connector
Open the Doc URI Wizard and set the URL for the data source to point to your query. In this case, we’re getting the order number variable from a separate drop down in the form.

Link to the SQL Query

That’s it. Try the form. When you select an order, the underlying Table automatically updates with the list of line items for that order. frevvo will automatically add and remove rows as necessary. No programming needed to create a form as sophisticated as this.

Order Line Items

Interested? To learn more Sign up and check out the Database Connector Tutorial. Or contact us for more information.

Using a Google Spreadsheet like a database

There are many cool and useful things you can do with Google Apps for Work like collaborative editing of documents, inexpensive storage and so on. But add frevvo to the mix, and you can take it to the next level and solve real business problems.

One example is dynamic web forms and approval workflows that treat the underlying spreadsheet as a database. The applications are endless – Sales Orders, Employee Records, Student Information and so on.

Check out the video below. It’s not even 2m long and shows you an example from Education. Teachers, administrators or parents can securely access student information stored in a Google Spreadsheet without actually having access to it. They’ll only see information relevant to their particular need, edits are controlled (e.g. data is validated, rules can perform computations etc.) and the user experience is so much nicer.

There’s much more you can do with frevvo + Google Apps such as signatures & workflow. Contact us to learn more or Try frevvo free for 30 days.