3 Replies Latest reply on Jul 14, 2015 12:09 PM by themactech.

    Creating Pie Chart using related data in first portal row

    themactech.

      Title

      Creating Pie Chart using related data in first portal row

      Post

      Hi all

      I may be doing this wrong but I want to create a pie chart but one of the data points I want to use is in a related table and I want to pull the data from the most recent related record to use in this chart. For example. I have a list of table called Cars with each record being specific car. I also have a related table called Travel. Everytime a car goes on a trip, a new travel record is created showing it's current location. On my car layout, I have my travel portal sorted by most recent entry/location. There are only 5 different locations that these cars can go.

      So i'd like to create a pie chart (or bar chart) showing the different locations with the number of cars at each location.

      Currently I'm trying to make this chart on the cars table... maybe I need to create it in the travel table.

      Any advice would be appreciated.

      Thanks

      Clay

       

       

        • 1. Re: Creating Pie Chart using related data in first portal row
          philmodjunk

          I don't see how that chart can work from just the data in a single portal row. Seems like you need this data for all locations.

          I would do the following:

          Add a "count of" summary field to the portal's table.

          I would use a layout based on the portal's table, not the parent table.

          Set up the Summary field as your Wedge data series and the location field as your wedge label series.

          Perform a find to find only the records needed for this chart.

          Sort the records by location to group them so that you get just one wedge for each location.

          • 2. Re: Creating Pie Chart using related data in first portal row
            themactech.

            Thanks!! I will give that a try today.

             

            • 3. Re: Creating Pie Chart using related data in first portal row
              themactech.

              It worked just as you described. THANK YOU. To find the records I needed I had to do a little fancy footwork. 


              I wanted to find the latest location entry for each "car". (in the script the actual object is called a flatrack). I may have gone about this weird but I sorted the list of locations by Flatrack ID and Date Entered (decscending). Then the script goes to each record... if the following record has the same Flatrack ID as the previous one.. it's omitted.. till all i'm left with is a single entry for each flatrack with the most current location / date.

              I've attached a picture of my script for anyone to tear apart and critique. :)