Forum Discussion

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

      ------------------------------
      jason johnson
      ------------------------------
      • IvanWeiss's avatar
        IvanWeiss
        Qrew Captain
        5 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
        ------------------------------