In the workplace I want to create a web-based front end in which outside folks will fill in forms and click a ‘submit’ button and it creates a new data row in a Microsoft SQL Server table. Left to my own devices, I’d whip up something in FileMaker and have it running by the end of the day, but the employer doesn’t own a FileMaker license, so I’m looking at the tools they do have.
Left to my second-tier choices, I’d whip up something in a php file that mixes php code and HTML code, and have it running by the end of the month, but it’s a large employer with a lot of persnickety rules and policies, and it has been alleged to me that if I did this, the php+HTML code that I write would be regarded as “an application” and would need to get vetted and go through an approval process that would take geological eons. Whereas if we make use of an already-licensed off-the-shelf software package and create a “survey” or a “form” for the folks to use, that will be considered “making use of already-approved software” and will go through the approval process much easier. Seems silly as hell to me, since I would no more have created “an application” in php than I would have created “an application” when I write an email or compose a Microsoft Word document to send to the end users. Whatevs…
I see two likely candidates: Crystal Reports and Tableau, both of which they already have and are accustomed to putting on folks’ desktops to create stuff. Both products are typically used to create “Reports”, i.e, to summarize data that’s already IN a SQL database. I’d be wanting to use it to generate new records instead… but surely they both support that, yes?
Anyone with relevant experience, please advise. Including whatever you can tell me about learning curve.
FWIW, I signed up for trial experience with Tableau and have managed to upload Excel workbooks that contain the headers and some sample data and even define relationships between the tables. But how to write the front end that intersperses human-readable instructions and blanks for data entry with the (I assume) SQL commands to grab what they input and shove it into the target table created from the Excel spreadsheet I uploaded, that’s a shitload less clear to me.
Of Crystal Reports, I know little except that it was used in a previous workplace for querying SQL, haven’t worked in it at all.
I’m not aware of any way for Tableau to do this. I use it pretty much every day to create reports and I’ve never come across any way for Tableau to take user input into an SQL database or any other without a custom extension. Our company typically doesn’t allow those, because they usually rely on sending the data out of the company to someone else’s server before doing whatever the extension needs to do. I think there’s one called write-back made for what you’re looking for, but I’ve never used it because it’s not available in my company.
I haven’t used Crystal Reports in years so my memory of it is hazy, but I recall hating it almost as much as Access back when we did.
I think I know a way, though it is a pretty “hacky” way to accomplish this. IMO, neither of the tools you mention are designed to accept user INPUT. They’re reporting and “Business Intelligence” tools used to summarize data across diverse platforms (SQL Server included).
However, I am pretty sure either of them can be set to take a series of parameters and execute a stored procedure. You could write a stored procedure that inserts data based on the parameters supplied. The downside is that gracefully handling errors is not the string suit of these tools, so if something unexpected happens, the users are gonna get an ugly error message.
Also, your DBA is gonna be REALLY pissed off that you suddenly want to switch from your reporting tool having read-only access to having write access “for this one little thing” which is always how things start. And that’s if either of these tools are actually pointed to the writable source-of-truth instead of a copy of the data specifically made for reporting.
I don’t have another solution for you, short of using something like MS Access as a front end, or having users upload excel files to a share that is polled and imported on a regular basis.
Honestly, though, none of this sounds great. What’s the original source for the data? An application of some sort? Couldn’t that be modified to allow the data input you’re looking for?
Historically (god help me), the external organizations would fill out part of a paper form, have their clients fill out the second half, then send us batches of those forms, scanned to PDF or TIFF or JPEG (or, god help me again, pasted into freaking Word); then, on our end… it was my full-time job for several years to drag those PDF (etc) files into an Optical Character Reader program (Teleform) and then come along behind that and run Teleform’s quality-control correction program, viewing the imaged document on one half of the screen and editing what the OCR thought it said on the other half; then exporting the results to SQL Server. We are not going to be doing that any more, everyone agrees on that much. But all the data came from outside organizations. We just collect it.
What they bloody well ought to do is shell out for FileMaker Server and some desktop licenses for FileMaker. It took me about an hour and a half to flesh out a FileMaker db to show as example of the intended workflow, with web-capable screens for prompting user input from the orgs and a different set for user input from the orgs’ clients and flattening the two tables to create a row to write back to SQL. Which, incidentally, FileMaker can use as a back end and which we’re already running and which is already the destination.
Second tier obvious solution is to write a web page in a combo of php (or whatever folks use these days that’s replaced it) and HTML, such as the contents of an already-existing table of organizations is referenced to determine which questions to ask of the org’s clients, then their responses in the web fields are written to a new SQL record using a standard SQL insert query. My own chops date back to the heydays of php but I still know how to do it and it’s all non-commercial and well-documented and all that.
In all cases, the SQL table need not be the final-destination SQL table; I can see the DBA / IT dept concern for publishing the “real” SQL table to the outside world. It can be a “holding tank” table that a later routine lets us examine for garbage data and then writes onwards to the actual final destination table.
I’m tired of feeling like I’m being asked to produce an elegant soufflé with a goddam campfire and they won’t approve any aluminum foil.
I believe Microsoft Forms has the ability to store directly in SQL Server tables via Power Automate but I have not done so directly. Have used Forms to collect data in Excel and have Power Automate do things with it.
MS Forms is a web-only data collection tool that you can present to the public at large, or restrict to your MS 365 organization, or a particular subset of those users. I believe it comes standard with A1/E1 licenses but I’m not very knowledgeable in modern MS licensing. If you have a MS 365 license, logon to https ://www.microsoft365.com/apps?auth=2&home=1 and that should list all the apps included in your license.
I broke that URL so you could see what it was since it showed up in a “phishy” looking format.
The Desktop that I remote into doesn’t have it installed but that doesn’t mean the org doesn’t have it. In fact, I have a list of software that they have a license for, amazingly enough!
Hmm… no listing for Forms but they also don’t actually list Excel separately and they do list MS Office 365, approval status “Authorized”.