Loading ...

Posted in: QQube    General Usage

Customer Information & DB Connection/Queries

Subscribe to RSS
  • Hi guys,

    My company and I are new to QQube, and we'd like to straighten a few things out. For one, Is there any place to retrieve customer information from the QQube Excel Add-on such as their address and phone numbers?  I noticed the fields were there in the Access DB, but I would like to know how to add more fields to the Excel Add-on such as customer phone numbers.

    Also, I hear the QQube database itself is actually just an SQL database. Doesn't that mean I can look at the tables in QQube as well as run SQL queries on it? If so, how do I connect to the database via DSN, through things like an SQL browser, or even Excel (via external data option, not the add-on)? I would use Access to query the database, but I'm unsure how to connect to the entire database. I am also unsure which driver I'd need too, whether it's SQL server or MySQL. 

    Any help would be greatly appreciated!

  • Answered

    You have two DSN's that are created.  See the bottom of this guide: https://clearify.com/wiki/view/92/installed-components

    The whole purpose of providing 'pre-fabricated' subjects is so that you don't have to deal with tables, relationships, or manual queries - like we have been doing for the last few decades.  Each subject is self contained, and there would only be rare instances where you would need to tie two of them together (to do something that QB doesn't do underneath the hood), or do a manual query.

    Whether it is Excel, PowerPivot, Access, Crystal Reports, Power BI, or Tableau you should just be able to drag your fields.

    The Microsoft Access examples have all of the tables are already linked together with the proper relationships (a data warehouse only has one way to tie the data together) - and thus all of the fields  You can either modify the existing sample query, or create your own.

    Here is the diagram of the relationships for the pre-configured tables used in the Sales Subject example in the Configuration Tool

    Microsoft Access - Example in the QQube Configuration Tool

    If you wanted to, you could right click on the sample query to open the design view, add tables that you don't see (they will auto link, because the relationships are already defined), and then add fields to the query, then rename it.  Beats the heck out of creating manual queries.

    Microsoft Access Query Example

  • Hi, thanks for the reply! We have some tables from other databases outside of Quickbooks we would like to link. Is it possible to create a new table inside the QQube database, so we may create links to it with the Quickbooks tables? This would help us consolidate our data into one place. The option would be to either do that or figure out how to create joins between two different databases. Any suggestions?

    We appreciate the outstanding customer service!

  • Using Access you could load other tables and link them to the tables in QQube.  Your only issue will be deciding which field to link to.  The ID fields used in QQube are internal ID fields, and wouldn't be useful to you.  Fields like Customer Name, Item Full Name, etc.

  • That's true. Well, what we would like to do is combine reports, and so can you do that via the Excel Add-on? That is, we'd like to grab fields from different subjects, such as Sales, Open PO, and Inventory combined. I'm slowly combining them, such as Sales and Inventory, but I'm stuck with adding Open PO (The Open PO Line Purcahse Order Open Quantity is what we want from that.) I'm currently doing it via queries, but if we can do this via Excel Add-on, it would be easier. 

    You've been a tremendous help, thank you again. I'm sure as soon as we get a proper hang of QQube, we'll be blazing through this all.

  • There are only VERY RARE instances where you would ever tie two subjects together - and that would be because you are trying to do something that QuickBooks does not do under the hood. Again this is extremely rare.  You will only make it far more difficult than it needs to be.

    In QuickBooks reporting there are reports in certain areas.  Please do not confuse this with subject areas in QQube.

    Subjects are fully contained - and in some cases contain information from other stand-alone subjects.  For instance the Job Costing contains the info in the Open Purchase Order Subject, the Time Tracking Subject, Vehicle Mileage Subjects.  People might use the Open Purchase Order Subject on its own to evaluate goods by deliivery date, and that is all they would need - they don't need job costs, revenue, estimates, etc.

    Here are two helpful guides:

    1. What QuickBooks can and cannot do: https://clearify.com/wiki/view/152/quickbooks-desktop-data-availability
    2. Schemas for all available analytics: https://clearify.com/wiki/view/163/qqube-schema-for-quickbooks-desktop
Page 1 of 1 (6 items)