Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
5 years ago

Maintaining Data Integrity Automatically

Just curious about best way to approach something.

During the sales process our sales team enters all of their "Opportunities" in an Opportunities table.  That is basically the backbone of our CRM functionality.

At some point they hit a "win" button which triggers the creation of an "Order" in the Orders Table and associated "Invoices" in the Invoices Table.

So basically:  Opportunities < Orders < Invoices is the table relationships in my app.

At the onset I want my sales team to maintain the Opportunities total revenue value because they are negotiating the deal.  Once the job is sold, as sales people tend to do, they move on to the next opportunity and lose sight of the data.  In our industry the value continues to change via Change Orders, etc. so we do not keep up to date information....

Is there a way that I could trigger Quick Base to use data entry when there is no Order and once an order is created use a field in that orders table to auto update the opportunity?  This way I have valuable data integrity on win ratios etc. and how much revenue was actually generated by an opportunity....

------------------------------
Ivan Weiss
------------------------------
Data import and export
Relationships
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    5 years ago
    Do you need to have the data entry fields updated on the Opportunity, though? This would essentially be negatively impacting the data integrity as well. Rather than that, maybe you should have two sets of Metrics/KPIs - one at the time of the sale and one that trues up these numbers by incorporating the changes. If you do this, you would just need to use Summary fields to bring up any totals from Orders/Change Orders and Invoices. This would also give you an opportunity to compare the Expected Value of an Opportunity vs the Actual Value of the associated Orders and use that as an indicator for how well your Sales team is estimating their numbers.

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      5 years ago
      Blake, I honestly never really thought of separating the original opportunity vs final value as it is typically a moving target for us.  We dont sell widgets but more projects.  So it starts with a rough estimate and hones in on that from there.  Let me give you a scenario....

      A new client calls us to install a coffee bar.  Doing a lot of coffee bar's we put in a budget of 200k.  After a few meetings and starting to design we see the client wants something upscale so we update it to 300k for the coffee bar.  Design is done and we hard price it and we come in at 275k so we put that in as the opportunity value.  Client accepts and we hit our "win" button.  Now as we move through construction the clients adds a few things and we have 15k worth of change orders so the final value is 290k.  

      We never had a firm opportunity value from the onset, it is our best guest scenario.  So I am okay with the final number being 290k.  But, you see how many updates are needed and knowing a sales team you can imagine how much of them are done :)  Right now we have a report that monthly reports on mismatches between Opportunity values and Order values so we can review and align them.  But, it is a manual process.  I was looking to force that match automatically.

      I guess the other option is to do a sum value on the orders table and use that.  But, the problem is all of my reporting is based on this "opportunity value" so I would need to make a new formula field which would check if orders are present...  If they are it could populate that formula field from the orders.  If not it could take opportunities. And I guess I could convert all of my reporting to use that new field.  That just means modifying a lot of reporting already in place vs update a field already in use ya know?

      Hopefully that explained it well?  Let me know your thoughts, open to suggestions either way!

      ------------------------------
      Ivan Weiss
      ------------------------------
      • BlakeHarrison's avatar
        BlakeHarrison
        Qrew Captain
        5 years ago
        Well, if your original number isn't as important and assuming your Opportunity Value and Order Value fields are on the same table, you could setup an Automation that runs a Table to Table Import on a regular basis. 

        You'll want to first setup the TTI, using the same criteria that you have setup in your current mismatch report and set the Source and Destination as the same table (wherever you have these fields). You'll also want it to only import the Order Value into the Opportunity Value.

        Then, you can create an Automation and set it to run the TTI on the schedule you choose.

        ------------------------------
        Blake Harrison
        bharrison@datablender.io
        DataBlender - Quick Base Solution Provider
        Atlanta GA
        404.800.1702 / http://datablender.io/
        ------------------------------