Charts and ExecuteSQL
I've been trying to use the Charting functions in FMPRO 12 but am running into a few hiccups. It seems a little backwards to me how the charting functions work when compared to regular tabular reporting. I've attached a screenshot below of the TO's that I am working with.
Ultimately, I want to create a bar chart with the X-axis being the names of the different categories (from the table Search_Category) and the counts of each category within Search_OFATR.
Now I can pull in the Category names from the TO "Search_Category" but then I don't have a way to calculate the sums that correspond to each category (it will give me the counts for all values within the table). Now I have a feeling that I will need to use a count-unique self-join relationship.
So I thought that I could use the ExecuteSQL function to do this but since I'm querying the Search_OFATR, I will recieve the values from that table and (from my understanding) I cannot pull the related category names from the TO "Search_Category". If I do this then my X-axis labels become cryptic foreign keys.
Now in all the FM tech briefs and examples, the charts come from rather simple tables so my next idea was to create another table that would summarize the chart data that I want to use but I have no idea how to do this.
How would I create another table that would automatically update from another table? If this isn't the best way to do this what would be?