Ever wanted to baseline a project or snapshot a sales pipeline?

Does your data change over time and would you like to track how that data changes?  For example:

  • Do you track tasks and do those task end dates and status change?  Would you like to "baseline" the dates associated with these tasks?
  • Do you have a sales pipeline where the opportunity size or status changes?  Would you like to be able to look at each of sales rep’s entire pipelines over time?

If either of these situations apply to you, we have good news…QuickBase can track these changes and you can build all sorts of reports that show how the information has changed over time and how the current values compare to previous values (e.g. has the opportunity size increased or decreased over time).

  1. Create another table that will track the changes.  You won’t need any fields in this table to start as we’ll be adding them in later steps.  This additional table will be where you track the "snapshots" of the task end dates or opportunity size information.  For the purposes of explaining this, I’ll refer to this new table as the "baseline" or "pipeline snapshot" table(s).
  2. Setup a relationship between the pre-existing task / opportunity tables where these pre-existing tables are the "masters" and the new "baseline" or "pipeline snapshot" tables are the "details". When you are asked about what field to use as the reference field in the details table, accept the default that will create a new field for you.  For help in creating relationships, see the Creating a Relationship on-line help topic.
  3. Setup "lookup" fields in the details tables for any fields you want to track changes on.  In this example, you’ll want lookup fields for the task end dates and opportunity size fields.  For help in creating lookup fields, see the Adding a Field to a Relationship on-line help topic.
  4. Create "snapshot" fields that will "snapshot" the lookup fields you want to track changes in.  Because of the way snapshot fields work, this will make it so that you will capture the lookup fields you want to snapshot at the point when the new detail records are created.  For help in what snapshot fields are or how to set them up, checkout our Setting Up a Snapshot Field on-line help topic.
  5. Setup an import that will take all outstanding tasks or sales opportunities and will import them into the details tables.  When mapping the fields, you will just need to pull the "key fields" (generally the record ID# field) of the open tasks or opportunities tables and will need to import them into the reference fields in the details tables.  To see how to create an import, checkout our Importing Data from other QuickBase Applications on-line help topic. Each time you then run this import, your task end dates and your opportunity sizes will be captured in the snapshot fields.

Once you have the above infrastructure in place, you can do many different things to help you manage your team and to get valuable insights out of this additional data.  Here are some additional, optional things you can do to further improve your application:

  • You can setup formulas that will compare the snapshotted value to the current value.  This will help because if anyone changes the values in the master tables these formulas will show you the difference.  For help in creating formulas, see our Using Formulas in QuickBase on-line help topic.
  • You can build crosstab views in the details tables (the "baseline" and "pipeline snapshot" tables) that will show you how the numeric values have changed over time.  To do this, you’ll want to place the "date created" field as the column field.  For help in creating crosstab views, see the About  Summary and Crosstab Views on-line help topic.
  • You can add "embedded views" in the master table’s forms that will
    show you all the records where you have snapshotted the related details
    records.

The one problem with this approach is that you need to remember to manually fire off the import on a regular basis.  For example, we use this approach to track our sales pipeline and we run the imports on a weekly basis.  By doing this, we can see how each prospect’s probability changes from week to week.  If many people start to use the import functionality in this way, we’ll definitely look into automating this kind of import.

I hope this triggers some great ideas for everyone.  Like most other things in QuickBase, you are only limited by your imagination on how to apply different pieces of functionality and this one leverages all sorts of different features…Enjoy…

  • Kristina

    Being able to schedule the import from table process would be an excellent enhancement!

    [Reply]

  • Kristina

    Being able to schedule the import from table process would be an excellent enhancement!

    [Reply]

  • http://www.quickbase.com/ Jana Eggers

    Kristina, Can you tell us more about the scheduling that you would want to do? Are you using Import now?

    Thanks!
    Jana

    [Reply]

  • http://www.quickbase.com Jana Eggers

    Kristina, Can you tell us more about the scheduling that you would want to do? Are you using Import now?

    Thanks!
    Jana

    [Reply]

  • Gloria

    This would be great if you could trigger an import based on certain field changes. For example, let’s say that I would want to only track when date fields a,c, & f changes as well as text fields K & D. That way, instead of regular scheduling or automation of imports which may or may not have changes, it would only kick it off if there is a change logged.

    I know this would be extremely beneficial to track text fields where we want changes logged but not necessarily set it up as a append or prepend field. This will allow the users to make edits to the main body of the text but also be able to log the changes.

    [Reply]

  • Gloria

    This would be great if you could trigger an import based on certain field changes. For example, let’s say that I would want to only track when date fields a,c, & f changes as well as text fields K & D. That way, instead of regular scheduling or automation of imports which may or may not have changes, it would only kick it off if there is a change logged.

    I know this would be extremely beneficial to track text fields where we want changes logged but not necessarily set it up as a append or prepend field. This will allow the users to make edits to the main body of the text but also be able to log the changes.

    [Reply]

  • http://www.quickbase.com/ Jana Eggers

    Wow, Gloria, I had never linked auditing and this feature. Thank you so much for giving examples, as that helps a great deal to know how you are looking at a feature.

    One question… this would mean that the “imported to” table would contain only the information of the last change. Is that what you want? I think this would go best with one way we’ve seen this used, which was for executive reporting. This way, whenever an exec came in the data would be the most updated. (And this could be data from several different QuickBases.) Does that make sense, or am I missing your point?

    If you want something to log changes explicitly, what control do you want? Keep the entire record? Keep the change? Keep the last 5 changes? Keep all changes? etc.

    Again, thanks! This type of feedback from all of our users is critical to us understanding what you need.

    [Reply]

  • http://www.quickbase.com Jana Eggers

    Wow, Gloria, I had never linked auditing and this feature. Thank you so much for giving examples, as that helps a great deal to know how you are looking at a feature.

    One question… this would mean that the “imported to” table would contain only the information of the last change. Is that what you want? I think this would go best with one way we’ve seen this used, which was for executive reporting. This way, whenever an exec came in the data would be the most updated. (And this could be data from several different QuickBases.) Does that make sense, or am I missing your point?

    If you want something to log changes explicitly, what control do you want? Keep the entire record? Keep the change? Keep the last 5 changes? Keep all changes? etc.

    Again, thanks! This type of feedback from all of our users is critical to us understanding what you need.

    [Reply]

  • Gloria

    Yes, logging and auditing is a definate feature that I think is somewhat lacking in QuickBase (& I have been using this tool for over 4 years now with different applications). QB provides logging of changes via email but not an easy way to have a change log or report to track important changes to records. With a web-based application with variety of users and access, I think it is crucial to have a record of changes that can be audited or able to report on.

    I’ve been trying to get around this by either setting up email notifications and using that as a crude log archive or setting up timestamp/append fields, etc. But with a functionality where we can trigger imports or autotmatically snapshot data based on rules (ie. certain fields or series of activity), this would be extremely powerful.

    Thanks for listening~!

    [Reply]

  • Gloria

    Yes, logging and auditing is a definate feature that I think is somewhat lacking in QuickBase (& I have been using this tool for over 4 years now with different applications). QB provides logging of changes via email but not an easy way to have a change log or report to track important changes to records. With a web-based application with variety of users and access, I think it is crucial to have a record of changes that can be audited or able to report on.

    I’ve been trying to get around this by either setting up email notifications and using that as a crude log archive or setting up timestamp/append fields, etc. But with a functionality where we can trigger imports or autotmatically snapshot data based on rules (ie. certain fields or series of activity), this would be extremely powerful.

    Thanks for listening~!

    [Reply]

  • Robert_S

    I agree with both the auditing and the import comments. From my viewpoint….

    Import: We have relationships setup between applications to allow other applications owners to ‘extend’ an application and add their own fields/views etc. To do this, we create cross-app imports that keep the records tied together. However, the owner’s of the second app have to manually ‘fire’ the import anytime a record is added in the master app. It would be great to have the ability to setup an execution schedule for imports. Or, you can auto-fire based on add/change/delete triggers like many databases allow.

    Auditing: One question I get the most is can you tell who changed what and when? It would be nice to have a checkbox setting for each field to indicate ‘Keep Audit Trail’. Quickbase would then date/time stamp the old value, new value and who changed it. This would be accessible via a link next to the field or maybe you could report the entire audit trail for the record.

    [Reply]

  • Robert_S

    I agree with both the auditing and the import comments. From my viewpoint….

    Import: We have relationships setup between applications to allow other applications owners to ‘extend’ an application and add their own fields/views etc. To do this, we create cross-app imports that keep the records tied together. However, the owner’s of the second app have to manually ‘fire’ the import anytime a record is added in the master app. It would be great to have the ability to setup an execution schedule for imports. Or, you can auto-fire based on add/change/delete triggers like many databases allow.

    Auditing: One question I get the most is can you tell who changed what and when? It would be nice to have a checkbox setting for each field to indicate ‘Keep Audit Trail’. Quickbase would then date/time stamp the old value, new value and who changed it. This would be accessible via a link next to the field or maybe you could report the entire audit trail for the record.

    [Reply]

  • Dane Netherton

    I just set up a database that audits every field by making every field an append-only field. To make it user-friendly, I created two fields for every one that needs to store data.  For example, I need to record an event.  I call this field “Enter_Event” and make it a text field that is append-only.  Then I create a field that I call “Event,” which is a formula field that simply displays the most recent event, without the rest of the audit trail (who changed it and when).  I set the forms so that they display the formula version when somebody wants to view the event: it shows “Event.” But when you click “edit,” the form shows you the append-only version (the field “Enter_Event.”)  Similarly, some reports show the whole audit trail, others show just the latest version. 

    I even set up date fields this way, using multiple-choice month, day and year fields that are append-only.  Only one formula field is necessary for the display field: 

    ToDate(List(“-”, NotLeft(Right([Enter Start Month],”]”),1), NotLeft(Right([Enter Start Day],”]”),1), NotLeft(Right([Enter Start Year],”]”),1)))

    This is not useful for cyclical/periodic snapshots, of course, but they
    are incredibly useful for keeping a full audit trail on every variable
    in a database (when it is vital, for legal reasons that such be
    maintained.) 

    I have found a use for these snapshot things in the past, though.  It would be nice to see a way to set them up to run automatically. . . 

    [Reply]

  • Dinesh Rampal

    Hi
    I tired the post above and meets my requirement except one thing which is need to create the audit trail during save of a form. i.e if i am auditing an field and during save of form, i realize the value has changed, i need to create the trail rather than importing it as suggtested above.
    Would really appreciate any suggestions!!

    regards
    dinesh

    [Reply]