2 Replies Latest reply on Feb 22, 2012 1:57 AM by carlsson_1

    Creating a chart based on related data

    carlsson_1

      Title

      Creating a chart based on related data

      Post

      I am currently viewing a record for a client. I'd like to see a chart showing how much they have been invoiced over the years. 

      Ie. the Y Axis containing the sum, and the X Axis showing the years.

      And I want the chart in the same layout as the client record.

      No matter how I do this I can't get it to work... What am I missing? :-???

        • 1. Re: Creating a chart based on related data
          philmodjunk

          If you want one data point for each year, you need a way to group your data by year. If you based your chart on a layout based on the invoices table, this could be done quite simply so you may want to consider that option.

          To do it from your client based layout, you'd need to add an intermediary related table to link to your invoices table by both year and client ID. Each such record could then compute a yearly total to serve as a data point in the chart.

          Clients--<YearlyTotals----<Invoices

          Clients::ClientID X YearlyTotals::anyfield

          YearlyTotals::gClientID = Invoices::ClientID AND
          YearlyTotals::Year = INvoice::cYear

          First, the fields. gClientID is a field with global storage specified. YOu can set up a script trigger to update this field with the current client record's clientID using the OnRecordLoad trigger.

          cYear would be a calculation field that extracts the year from a date field in invoices: Year ( InvoiceDate )

          Now the relationship details:

          Since you almost certainly have a relationship between clients and Invoices that should not be changed, you'll need to use the duplicate button (two green plus signs) to make a new table occurrence of invoices to set up these relationships. I've specified the cartesian join operator for the relationship from Clients to YearlyTotals so that the current record in Clients will match to all YearlyTotals records. You can also use a different relationship to match to only a specified range of years if you use two match fields for the start and end years and use inequality operators.

          A calculation field can now be placed in YearlyTotals to compute the total sales for the selected client for the specified year.

          You might want to set up a portal to Yearly totals that displays the calculation field first, to make sure you are getting the correct data, then set up your chart to chart the related data from yearlyTotals.

          • 2. Re: Creating a chart based on related data
            carlsson_1

            Thanks Phil!

            I was hoping to leave more relationships out of the way, but now I know that I need them.

            Thanks.