Foundry – Rapid Application Development

The Sharperlight Foundry has been designed to allow a non-developer to rapidly design and create simple Applications that require a database to store and report from in a secure manner.  For example you may be capturing data in Excel workbooks but don’t have the skill sets to develop a Database Application where multiple users can report and update the data via a Web Browser or Excel.  This would be a case where the Foundry could be used to rapidly build the Application and transfer the existing data.  The users could then more easily share and update the data in a secure manner via the Web Channel or Excel. In Foundry you define the types of data required, the relationships between the data fields along with some branding.  When the design is ready you can then execute the Project and have Foundry automatically create the DataModel, Database and Reports ready for use.   If after testing the Foundry produced Application you find that additional changes and modifications are required, you can simply re-execute the Project and have it rebuild everything.  At some point you can leave the Foundry behind and continue editing the Project using the full Sharperlight Studio or Publisher to access more advanced functionality. When defining Tables you have the option to base the Tables on any data that is accessible via the Sharperlight Query Builder. In this way when the Foundry Project is executed the data from the Queries will automatically be transferred into the new Foundry Tables along with any validation checks that may be required due to data relationships.  For example you may want to create an Application to handle Budgets and Forecasts for the New Year based on your current transactions in the live Production Ledger.  In this case you would base the Table on a Query in Query Builder that pulls the Accounts and Actuals for the current year and use Output Expressions to create a Budget field where it’s the Actual Amount plus 5% to get a basic starting point.  Once Foundry has created the Database you could then allow users to go into the Web Channel and update the Budget values for each Account where required.   Because Foundry creates a separate Database, you need not worry about the issues that come from modifying your Financial Application Database directly.  Another example would be defining your data starting point in Excel worksheets, where each Sheet has the different sets of data you would like to seed your Foundry database with.  By using Excel Worksheets you can query data, transform it or create new datasets that may not exist anywhere else.  This is possible because Query Builder can query data held in Excel worksheets as well as many other types of data such as CSV files or Web Pages. Foundry can not only populate your Database with data, it will also create a Web Channel Report Pack that will allow you to query the data and also create new rows and update existing data.  When Foundry creates the DataModel it will create all the logic to allow Writeback via the Web Channel and Excel.  In this way a non-developer can very quickly create an end to end Application, test it, modify it and finally release it for users in a fraction of the time that it would normally take.  

What the Foundry can do

• Allows you to Define Tables based on Queries or design a schema without data
• Supports Join Relationships between Tables Fields (One to One or One to Many)
• Supports basic Field validation such mandatory, value ranges, value lists and Join checks
• Supports cascading filters concepts on Fields in Writeback and Lookups
• Supports compound unique Fields via the auto generation of identity Fields
• Support for branding names, web sites and logos
• Automatically creates the DataModel
• Automatically creates the Database, Tables and Indexes
• Automatically validates and loads the Database Tables with data where required
• Validates the data loaded into Tables to ensure referential integrity
• Automatically create the Publisher Report Pack ready for use in the Web Channel
• Automatically creates a Database Schema SQL Script file
• The Foundry Project can be saved to a file and opened from a file
• The Foundry Project can changed and re-execute any number of times until the design complete  (note that re-executing the Project will delete all previous data as deletes the tables if they already exist first)
• On Project Execution a log is shown so that any issues with logic or data can be easily identified and fixed.

 

Examples of Foundry Projects

• Require a simple Budgeting or Forecasting Application based on the current Financial Data that is separate from the Production System that users can update via the Web Channel
• Need to turn data that is currently being captured in Excel into a Database Application so that more than one user can securely access and enter data via a Web Browser or Excel
• Require a simple Application to store and track company data that is not currently being handled such as Timesheets, Client Support Tickets , Client Details or Production Data
• Prototyping ideas for Applications and Storing data
• Pre-sales where the Client has asked to see how their requirements might be implemented and captured
• Archiving of historical data.  The Database and DataModel created by Foundry supports Writeback so after the initial snapshot, scheduled jobs can use writebacks to pull new data into the Foundry Project database.
• Use a tool to better understand how Studio, DataModels and Publisher work.  Once Foundry has created all these layers you can then view the DataModel in Studio or the reports in Publisher to their settings

 

Basic Steps to Creating a Foundry Project

• Before starting Foundry ensure that your Client Setup is using Local Connections and not Remote Connections.  This is because Foundry can only create a Database over a Local Connection.
• Start Foundry and start on the Product Tab
• Enter a Product Code, Description and provide any branding if required such a URL links and Logos
• Select the Tables Tab and start defining your Tables by clicking the new Icon
• You can base a Tables on a Query Builder’s Outputs or by defining the Fields yourself
• Adjust the Table Category, Table Code and Description to suit
• Use the Category Dimension for reference tables such as Code lists, Accounts etc. and Fact for Transaction based Table
• Define or adjust the Fields for each Table by double clicking the Table
• Check that each Field has its own unique Code and Description
• Check the Fields Data Type is correct along with other attributes like is it Unique or is part of the Unique Index.  This is important for Writeback to correctly identity and update or insert rows in your database.
• Define the Field Join relationships if any in the blue Join area.  Sometimes your Table will have a Field Value that must exist in another Table like an Account Code.
• Define any Value Lists you may have such as Male, Female or Debit, Credit etc. on the Value Lists Tab.  Table Fields can then reference these in the Validation frame to turn cryptic codes into something more readable
• View the Relationship Tab to confirm if any joins may be missing
• Organise the Tables into Folders by setting the Folder Attribute for each Table.  These Folders will be the basis of the Web Channel Menu later on in the Report Pack
• Click the Validate Button and view the Logging Tab for any errors.  Double click on errors to be taken to the general area where the error came from so that you can fix it.
• Save your Foundry Project to a File.
• Click the Execute Button and if there are no errors you should be able to view the Report pack in the Web Channel ready for testing.
• If there are any changes to be made make them in Foundry and click the Execute button again.  Warning this does create the Database and Tables so any data you may have entered is cleared down and reloaded so it’s back at the start position.
• If the design is complete and testing finished you may simply exit Foundry and then deploy the Database, DataModel and Published Reports to your production system.
• If additional more advanced logic needs to be added to the DataModel you may continue editing it in the Sharperlight Studio.  ( Please note, at this point you should not go back to Foundry )
• The Publisher Report pack can be edited in Publisher to better suit the requirements

 

Limitations

• Foundry will required a Client Setup Local Connections to create a Database as it cannot do this over a Remote Connections
• With this release of Foundry it will only create Microsoft SQL Server Databases.
• Foundry when first creating the SQL Server Database will use the Client Setup System Connections details so please ensure these are sufficient.  Once the DataModel is created you may set the Connection details to something else in Client Setup if required.
• Joins,  have a maximum of 4 fields that can be matched in each Table
• Fields can only have one Join to another table
• Field indexes are based on the Is Unique and Index Attributes.  If you wish to create your own compound indexes by manually creating them after the Database Tables have been created
• Only one level of Folders when organizing Tables or Fields
• DataModel events and processes cannot be defined in Foundry but can be in Studio
• Foundry always rebuilds Databases, DataModels and Published Reports.  It does not resync and preserve any modifications or data you may have done outside of Foundry
• Requires Sharperlight version 3.1.40 or higher

  ​