Never having used charts before in FMP I think I need some advice on this problem instead of wasting a bunch of time spinning my wheels trying to find a solution.

I want to create a chart showing how many items are due in each of the upcoming days, i.e. a bar graph showing values sort of like this:

# | # # # | _ | # # | _ | # # # | # | _ | _ | # # | _ |

Due in -3 days | Due in -2 days | Due in -1 days | Due today | Due in 1 day | Due in 2 days | Due in 3 days | Due in 4 days | Due in 5 days | Due in 6 days | Due in 7 days |

I want this to appear in my layout where I browse the items themselves. So each entry on the current table has a due date, and a field that calculates from this how many days away (or past) it is due, however for any given due date, there would be any number (zero, one, or more) of records due on that date.

I'm not sure how to configure my chart to pull the right data, and I suspect calculated fields may need to be involved (I'm hoping not more tables... I have enough tables already!).

Can anyone offer advice on if there is a relatively easy way to accomplish this?

Thanks,

- RG>

Good morning. Sorry for the delay in responding. I wanted to put together a file for you.

Here are some basics: you can set up a chart to show data from "Related Records" (Look at the "Data Source" tab of the char tool). I created a zSystem table and related from this to the Items table using a Cartesian Join.

I used two fields in my example: A "daysDue_c" field and "DaysDue_Text_c" field. The first field here finds the number of days between today and the DueDate of the item. I also created a summary field that does a count of the records. When I then go to sort the chart data (Data Source) by the DaysDue, this summary field does the counting for me.

The 2nd chart is a chart based off ExecuteSQL. As I say in my notes, I prefer this kind of chart. Instead of using a data source of "Related Records", I use "Current Record (delimited Data). This allows me to ignore the layout context and put the chart pretty much anywhere.

I hope this is helpful to get you started. Feel free to ask questions...

(I purposefully didn't explain EVERYTHING. I hope you can pick this apart and learn something more)