A newbie really needs help

[vulcannomad]

I have a very simple Filemaker solution called ChurchMusicPlanner. Each record can have up to 20 hymns drawn from a hymnal index table (600+ hymns) listed in twenty different fields - Hymn1, Hymn2, Hymn3 etc. where each of the twenty fields accesses the hymnal table from drop-down menus.

What I need is a way to list all the hymns used from a found set of records by the number of times they were used. For instance, "Like a River Glorious" used 4 times this past month. So you could have any number of records with twenty different hymn fields in each record with each field having the possibility of having that hymn listed. I don't need a "search" function where I look for a hymn. I need it to simply list whatever hymns were used and how many times they were used over that found set of records each with twenty fields. Is this possible?

Edited December 2, 2008 by Guest

[bcooney]

What does each record represent? Performance Date?

This is a really bad design, btw. The hymns assigned should be in another table, a join table btw the parent record and the hymn index table.

I don't think you can get the report that you need with this structure.

[vulcannomad]

Thank you for your response. The record is a service order for a church service where different events are scheduled. I have the entire hymnal index in another table. The twenty fields are just look-up fields from that table. There are just twenty different fields because I wind up saving this as a run-time app and there was no way I found for the user to customize his own service order without actually having the FileMaker program. Those twenty look-up fields all access the hymn index table and can take on one of up to 600 values - hymn titles. I am a musician first and a joke of a software developer, I know - but I have a lot of fun doing it.

[bcooney]

Well, if the model is Service Order and the items on the SO are hymns, then you're missing a Service Order Line Item table. You should not have created 20 separate fields!

Read up on relationships. On a Service Order form layout, you'd have a portal to ServiceOrderLineItems. Each line item would store a HymnID.