Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
6 years ago

Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

I have a project management application that has a CRM component built into it.  My sales team enters their annual sales goal on a table called Sales Goals The sales team enters their data on the t...
Relationships
QuickBaseJunkie's avatar
QuickBaseJunkie
Icon for Qrew Legend rankQrew Legend
6 years ago
Hi Ivan!

Without having access to the data myself, this is my initial thinking...

Relate Invoices to these Sales goals (or this could be done with a sync table if you don't want to relate it directly). I assume the salesperson is already on (or could be added to the invoice). The Sales goal would be the parent with Invoices as the child. I assume there is only one salesperson per invoice.

You can then create your summary fields by quarter, type, and annual... I assume the information you'd need to filter these summaries is already included on the invoice (ie quarter & type). Separating it by salesperson will happen automatically as part of the relationship.

Then it's just a matter of adding formula fields to the Sales goal table to give you the %.

In my head, I believe this will do the trick for you. Let me know if it helps 😀👍

------------------------------
Sharon Faust (QuickBaseJunkie.com)
Founder, Quick Base Junkie
https://quickbasejunkie.com
------------------------------
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    The good news is after some tinkering I came to a very similar thought as to what you had and implemented it.  The only thing I am sure of is for some reason I created a snapshot table as a table import....  To run daily and move the invoices over to it.  Honestly, I am not sure why I would need that after thinking about it some more.  So now I am just giving some thought on if I should eliminate that and pull the data directly from invoices.  I would think I want to as for some reason if something changes with an invoice, it affects the report to goals so I dont think the snapshot table is necessary.  Which means rebuilding that :( but oh well!

    ------------------------------
    Ivan Weiss
    ------------------------------
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    Sharon, so quick question....  On the association I understand I make the relationship in Quick Base.  However, do my users have to associate an invoice with a sales goal record?  Otherwise are they not linked?  I am not quite sure how we get that association to match.  I basically need to auto match the sales goal with the sales person and year.  Otherwise they are not going to link....

    I dont think manually associating makes sense as the users wouldnt really understand the relationship and why they need to match them up.  Unless I just need to explain it and they need to make it habit.

    ------------------------------
    Ivan Weiss
    ------------------------------
    • AdamKeever1's avatar
      AdamKeever1
      Qrew Commander
      6 years ago
      You could use Case or If logic in a formula field to auto populate the sales goal.

      Something along the lines of If([Person]="Jane" and [Year]="2020", "Sales Goal 1", null)

      ------------------------------
      Adam Keever
      ------------------------------
      • IvanWeiss's avatar
        IvanWeiss
        Qrew Captain
        6 years ago
        What I meant was dont I need to populate the "Related Sales Goal" field on the invoices?  If so, how do I auto associate them?  It is going to be a record number that doesnt really align with anything.

        ------------------------------
        Ivan Weiss
        ------------------------------
    • QuickBaseJunkie's avatar
      QuickBaseJunkie
      Icon for Qrew Legend rankQrew Legend
      6 years ago
      Ivan, what I saw in the screenshot you provided was 6 goal records (one for each salesperson) where the individual goals appeared to be fields on those records (not separate tables). 

      If you relate Invoices > Goals all you're users would need to do is select the salesperson associated with that invoice (assuming you set it as the proxy field).

      If you want it to automatically 'link up' I would suggest making the salesperson name the key field and making a 'user' field or other dropdown the related reference field on the Invoice. In this scenario, you would have to have the salesperson on the Invoice (or some other key identifier).

      ------------------------------
      Sharon Faust (QuickBaseJunkie.com)
      Founder, Quick Base Junkie
      https://quickbasejunkie.com
      ------------------------------