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
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
      ------------------------------