Start a new topic

SQL related questions


Going thru a slew of problems and as I am solving them, I notice that our SQL database is getting a bit larger. We got plenty of time, but I just wanted to feel this out a bit. This may be a not bright question, but I am just barely starting to understand SQL and see a lot of potential for expansion on what we are doing with it.

If the time comes, we are in "too big" territory for SQL express, is there any reason we couldn't transfer to something else? SQL server standard is the obvious next step, but I am wondering if there's any reason why we couldn't use another brand or PostgreSQL for example.

I don't know if there are queries that will only work on SQL Express, you know?

Also - is there any reason why we couldnt use a cloud database such as Azure? 

I am also wondering - would Quotewerks break if we added columns to tables or new tables to the database that Quotewerks wouldn't use? I'm thinking about making a lightweight tool for people in production to be able to initial off on various stages of production by writing to the database. 

I've only ever known QW use a SQL Express, 'Full' SQL and Azure SQL database.

In fact, a growing number of my clients have gone down the Azure SQL route for more 'cloud-like' functionality. Performance takes a bit of a hit with Azure SQL, but only really during intensive functions such as running complex reports.

Any queries I've tried with SQLExpress would also work with SQL 'Full' and Azure SQL.

I'm sure the QW team 'officially' wouldn't necessarily want you to be writing data to the QuoteWerks database.

That said, I've done it and not had any issues - but I understand that's my risk.

There are several fields available in the database, not available via the interface, that are handy to use for the sort of purpose you're talking about.

Thanks for the tips Matt.

Just to add to Matt's notes regarding usage of fields not available to the QuoteWerks UI... as Matt correctly suggested - very risky as they could be removed at any time with an upgrade. Writing to fields that ARE available is less risky (if you are careful!) because you'd expect any data to be migrated if the field changed name or the database structure changed.

Creating fields in the QuoteWerks native tables though: a very bad idea!!! (For any program or as a development practice.) Even creating new tables within the QuoteWerks database is not good! You are much better off creating a database that sits alongside QuoteWerks and references the relevant field in the QuoteWerks database for the added information you want to store. We always have a "QuoteWerks_AddOns" (or "QuoteWerks_Reporting" if it's a custom reporting project) that sits alongside the QuoteWerks database with Views through to the QuoteWerks data, and a table structure which compliments whatever it is we're building to reference the relevant fields in QuoteWerks. If the QuoteWerks database structure changes: we just change our Views. If the QuoteWerks database gets moved: we just change our Views. It means the processes and logic of our add-on can work independently of QuoteWerks.

That's fair. I was thinking about sort of using the existing database to replicate into a second one that has the expanded functionality but I haven't really sat down to figure out the best way to go about things at this stage.

My ideal hope is that I can keep it minimal and perhaps use a query to attach files as linked documents to exisitng QW projects so that we keep everything in one place. We already scan every project and attach them anyhow, but we do a lot of paperwork and I am wanting to digitize it without making it complicated for people in the shop to deal with.

What is it that QuoteWerks' native 'Link File' features aren't doing for you? Or is it just about simplifying the interface for some people that might want to access the information in those linked documents?

Interface simplification. 

the production employees for the most part are not computer people, so it needs to be as basic and simple as possible. right now this is in the form of passing around paperwork folders with various signatures needing which ends up back in the office upon completion of order and gets scanned.

i have a vision of kiosk style stations that they can see what orders are open, can click on them to enter information about their part of the process, and not have to deal with everything else that comes in quotewerks at all. but the entries provided are for record keeping, which we store all records against a project against the quotewerks order linked documents section. 

even just looking at an order in quotewerks would confuse the heck out of most people because we have dozens of line items in there that they don't normally see for inventory related purposes. the people in the office now still dont' really comprehend what is going on with all that, so if something needs adjusted for a special circumstance i have to personally do it.

Steve, I'm thinking CIT-DocumentOpen, what do you think?

@Matt: DocOpen with a new "View Linked Documents" feature maybe? Possibly with a.n.other screen pop-up or two for the Users to enter information about their part of the process and/or link in a new Document.

I've sent Brian a video of DocOpen + edit mode. Will see if it triggers an interest in something that might be 50/70/100% already done!

I am saving this feature video and discussion for a bit down the road, not sure when I will get to it yet but I definitely will. I have too many plates spinning right now and I was just putting some feelers out for future use.

After going through some exercises with Matt already it started connecting some dots about how to accomplish some of my longer term goals.

I will be viewing the other video sent sometime in the next day or two for sure.

Login or Signup to post a comment