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
jasonjohnson1's avatar
jasonjohnson1
Qrew Cadet
6 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's avatar
IvanWeiss
Qrew Captain
6 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's avatar
    jasonjohnson1
    Qrew Cadet
    6 years ago
    Where is project solutions coming from?

    ------------------------------
    jason johnson
    ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      It is a lookup field on invoices being passed from several levels up.

      The root table is a Project.  It contained Project Solutions (which is a relation to team members).

      Projects<Opportunities
      Opportunities<Order
      Order<Invoices

      ------------------------------
      Ivan Weiss
      ------------------------------
      • IvanWeiss's avatar
        IvanWeiss
        Qrew Captain
        6 years ago
        Just wanted to give this a nudge and see if anyone had any more input on it?  What I am stuck on is I have the association of Sales Goal< Invoices.  So I can summarize the invoices to the sales goals.  

        Except I cannot physically connect them.  How do I populate the related sales goal field on invoices?

        ------------------------------
        Ivan Weiss
        ------------------------------