Pie Chart woes

My first instinct is that your underlying data model is incorrect. If the outcome you want to display a pie which has Widget (2), Whatsit (2) and Dohicky (1) then your database table should have two Widget rows, 2 Whatsit rows and 1 Dohicky row.

If you have no control over the data model then you'll need to manipulate/coerce the data that is returned so that it meets your needs.

Hi Stewart,
The above was an example. the se case is that in another part of the site, a user can select multiple items of many different types. 
Effectively each row in the DB is an order for multiple items. The list of items per order are stored in an array and written out to the db in a single column.

I am trying to show in reporting how many of each type is ordered. It would not be very efficient to have a table with a column for each item. (which may change as we add / decrease the items)

Storing multiple values in a single field is a difficult design decision. Is that done by intention? How does your entity relationship model look like?

Interesting...a normalized model would have one table for the Order and another for the Order Items...

On the assumption  you don't have any influence over how the DB is modeled then you'll need to manipulate your returned data to achieve a structure you can use in your pie chart.

On the assumption that what you've provided so far is a precise representation of the data returned (e.g. the / symbol is the separator in your returned data) here's how you might go about normalizing the data for use in your pie chart:

a!localVariables(
  local!data: {
    a!map(id: 1, value: "Widget"),
    a!map(id: 2, value: "Widget / Whatsit"),
    a!map(id: 3, value: "Whatsit / Dohicky")
  },
  local!values: a!flatten(
    a!forEach(
      items: local!data.value,
      expression: fn!trim(fn!split(fv!item, "/"))
    )
  ),
  local!dedupedValues: fn!union(local!values, local!values),
  local!chartSeries: a!forEach(
    items: local!dedupedValues,
    expression: a!map(
      item: fv!item,
      count: fn!count(fn!wherecontains(fv!item, local!values))
    )
  ),
  a!pieChartField(
    series: a!forEach(
      items: local!chartSeries,
      expression: a!chartSeries(label: fv!item.item, data: fv!item.count)
    )
  )
)

While that works as a tech demo, it will get you into performance trouble as soon as your data volume increases. Relational databases are really good in aggregating this kind of data, but to make use of it, you need to normalize your data model.

While that works as a tech demo, it will get you into performance trouble as soon as your data volume increases. Relational databases are really good in aggregating this kind of data, but to make use of it, you need to normalize your data model.

...which was my very first comment - that the underlying model didn't appear to be normalized and consequently you cannot leverage the power of the DB to aggregate it

Too many comments n one...
Agree the Db isn't the best - this was an extension to another tech demo. And just fiddling with the 'new'  records way of doing things as opposed to entity queries. Thanks for the response and the key was flatten. Just something i just couldn't put my finger on.

thanks all