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 GoalsThe sales team enters their data on the table and I can view it as follows. Please note that we have two types of sales we track (design revenue and build revenue). We are a design and construction firm.Now I have another table called Invoices. I want to build a meter chart on the home page of each user showing their percentage towards goal. I also want to be able to view the bottom report with a comparison to show what has currently been invoiced and a percentage towards goal. Again per quarter and annual separating the two types.I am struggling with how to relate this data.... My first instinct was I need to summarize the data out of the Invoices table. But I need that sum sorted by type and sales person. I would need to pull each out quarterly and could sum it up for the annual via formula.My other thought was if I need a snapshot table of some sort, but honestly I am not sure if I would need that as the data is always live. It is not like it is a cumulative number like a pipeline tracker or something like that.Any advice on how to structure something like this? I have to imagine someone has built this type of functionality into a CRM tool.Thanks! ------------------------------Ivan Weiss------------------------------

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

One - Sale Goals to Many- - Invoices is the relationship and make the Sale Person the proxy lookup field in invoices. Change the default record picker in Sales Goals to Team Member as the only field showing so that way people are only selecting names. I would suggest having a formula in the Invoices table to assign the quarter based on invoice date if you don't want people to pick the quarter themselves. For example:If(Month([Invoice Date])>0 and Month([Invoice Date])<4,1,If(Month([Invoice Date])>3 and Month([Invoice Date])<7,2,If(Month([Invoice Date])>6 and Month([Invoice Date])<10,3,4)))Now with the relationship setup you can create the summary fields. Yes you could use the "is during" Criteria when making the summary instead. Make sure when you design this that you are thinking of what it will look like at the end of the year going into the next year with new sales goals. Don't forget the field to select in Invoices if the project is design or build.------------------------------jason johnson------------------------------

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 Junkiehttps://quickbasejunkie.com------------------------------

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

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

Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

15 Replies

jasonjohnson1
Qrew Cadet
5 years ago
One - Sale Goals to Many- - Invoices is the relationship and make the Sale Person the proxy lookup field in invoices. Change the default record picker in Sales Goals to Team Member as the only field showing so that way people are only selecting names. I would suggest having a formula in the Invoices table to assign the quarter based on invoice date if you don't want people to pick the quarter themselves. For example:
If(Month([Invoice Date])>0 and Month([Invoice Date])<4,1,
If(Month([Invoice Date])>3 and Month([Invoice Date])<7,2,
If(Month([Invoice Date])>6 and Month([Invoice Date])<10,3,
4)))
Now with the relationship setup you can create the summary fields. Yes you could use the "is during" Criteria when making the summary instead. Make sure when you design this that you are thinking of what it will look like at the end of the year going into the next year with new sales goals. Don't forget the field to select in Invoices if the project is design or build.

------------------------------
jason johnson
------------------------------
- IvanWeiss
  Qrew Captain
  5 years ago
  So I think I followed all of this and here is a screenshot of my relationship. I also got rid of the snapshot and now have sales goals going directly to Invoices. But on my report I have nothing being summarized up from Invoices. For some reason I dont think those records or automatically linking. I guess I am not sure how I automatically get the related sales goal to link to the appropriate invoices
  
  I added a report to the invoices table to show my quarterly invoices so we can see the data. Obviously I see the issue is the related sales goal column is empty. When I am not understanding is how to auto populate that to create the link. I dont want end users to have to link to sales goals manually for every single invoice.
  
  ------------------------------
  Ivan Weiss
  ------------------------------
  - jasonjohnson1
    Qrew Cadet
    5 years ago
    Where is project solutions coming from?
    
    ------------------------------
    jason johnson
    ------------------------------
Quick_BaseJunki
Qrew Captain
5 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
  Qrew Captain
  5 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
  Qrew Captain
  5 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
    Qrew Commander
    5 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
    ------------------------------