I have created a database to track my bonds. Different bonds come due at
different dates (month & year). I created a layout that lists the bonds
in chronological order of due date. What I want to create is a layout
that's a grid with years down the side and months across the top.

Into each cell I want to calculate how many bonds are due that
month/year. When I click on the grid box I want to see the total value
of all the bonds that come due in that month/year combo.

I am not sure how to begin this project. Using a Mac and Fm 8.0

Thanks for any help.

Rich

Hi Rich,

What you're describing is similar to a calendar form in which there are, in
this case, 12 (months) times x number of years, say 15. Filemaker's never
been great for building these matrices-- Excel often does a better job. The
obvious choices (and there are other valid approaches) are 15 12 cell
repeaters, or (my choice) 180 relationships.180 relationships aren't fun to
build, and they would have slowed an FMP 6 file to a crawl, but it might not
be too slow post 6, pariticluarly if you move to 8.5 on one of the Intel
Mac's, which run Filemaker at a pretty stunning speed. This performance (and
amount of labor) increases as you go further out than 15 years, but you
could also use variables (global fields) for your left-side keys and, while
losing some of the 'grid' emulation, dramatically both improve performance
and reduce the amount of grunt work.

The basic idea for a given relationship, say 'June_2010', is a calc
('6_06')returning that month and year as the left-side key, and a
calc('DueDate_Calc') in the table (might be the same table) holding the bond
due dates that returns the month and year of the due date. So, where
'DueDate' is a date field, 'DueDate_Calc' would use:

Month(DueDate) & " " & Year(DueDate)


....and return text, i.e., '6 2010'. The space between the month and date
makes it easier to read, and easier to de-construct on the other side if the
need arises.

The left hand key, '6_06', would be a simple hard coded calc (it need not be
indexed) also returning text:

'6 2010'

Build one of those calcs for every month of every year you want to track,
and build a relationship for every one of them to 'DueDate_Calc'.

Draw portals on your layout in the desired grid, and put in the fields in a
couple of portal rows you want to track, maybe the bond name and its payoff
value. Rather than display a total payoff value for each relationship, which
will really slow down the display, I think your idea is a good one-- display
it by clicking on the portal. This is a good place to invoke a separate
window, so you can see the total without having to leave the page.

A vastly simpler variation, but one that reduces or entirely eliminates the
grid, is to make the left hand key a calc based on two global number fields
('global_Month' and 'global_Year'). The calc would return text and read:

global_Month & " " & global_Year

Display the two globals in pulldowns, populated by (for global_Month)
integers from 1 to 12, and (for global_Year), a hard coded list of the years
you want to track. You now only need one relationship, and one portal.

Or, you could use some variation in between, such as 12 calcs and 12
relationships, one for each month, and a Years pulldown. In that case,
selecting '2010; from the Years pulldown would make 12 portals show data for
each month for that year. Or, 15 portals for the years, and a pulldown for
the months.

You might also start with the globals and see how it goes. If you're having
fun and performance is OK, you can move on to the hard coded calcs and
relationships and get your grid. Just remember the universal problem of hard
coding stuff like this is that you will need to add 12 more calcs and
relationships every year, or any time you want to extend out the grid.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634

Certified For Filemaker 8
Certified For Filemaker 7


"Rich Sagall" <rich.sagall (AT) pobox (DOT) com> wrote