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
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
      ------------------------------
      • AdamKeever1's avatar
        AdamKeever1
        Qrew Commander
        6 years ago
        I am sure there are other ways to achieve this, but here is one method that I know of that works. You will need five tables and six relationships.
        Here is the result using one user and one quarter for demonstration purposes:

        Each goal and invoice must select the team member, sales type, and quarter from the relationships and the one to many relationships allow goals and sales to be summarized by sales type and quarter on the team member table and formula numeric fields allow you to calculate the percentage of completion:









        ------------------------------
        Adam Keever
        ------------------------------
  • 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
    ------------------------------