It would be valuable to be able to use a union query to combine multiple queries into a single result. For example, suppose your database has two unrelated tables where one stores customer data and another stores data about suppliers – and both tables have a contact information field. You’d like to see the contact information from both tables in one view. We cannot do this today in Capsio like we can in SQL Server or even MS Access. To do this, we would need to create a view for each table to retrieve the contact information, and then combine those results by creating a union SQL query using the UNION syntax. Very useful for combining data. Others on the Capsio forum have expressed interest in having this feature as well.
Bill Pennock commented
Still no Union! Now I really need one to overcome a problem created by a poor programmer. The database has a table for joining Applicants to Job Listings by id's. When a slightly different need was expressed by a group using this application he did not use this table but made a whole new one for this group only. This new table implemented the connection between applicant and job listing but then went on to hold an entire set of data that was, admittedly, attributes of that linkage but he didn't know how to use a table for those attributes with a simple join to the original table for those applicants that were from this business group. There are perhaps 20 groups using this application and now to get a list of applicant information from this group and the others require 2 separate datapages. One that has the name of the business group who's applicant information you want to list as a drop down selection and the other as a separate that only returns the new business groups applicant information. A union would solve for this so easily.
David Hall commented
Critical feature requirement
Bill Pennock commented
Almost Critical. Union joins are a very important part of the SQL Syntax
Or at least be able to create a formula in a view
Best practice is to store metadata with multiple values, in a separate linked table. This is a bit cumbersome in Caspio using child forms. So when the number of multiple values is limited to 2 or 3, one can opt to include these as different fields in the main table (for example 2 to 3 keywords for an object) . To do any reporting on these 2 to 3 keywords, one would need to union the values in the 2 or 3 fields.