Relatinal Table instead of Repeating Field

[Rfox]

Hi,

I work a little with Filemaker Pro but not enough to really know what I'm doing.

I am trying to add to my Kid's Church DB the ability to record Memory Verses for VBS and the Reward Points associated with it.

The Main DB Records are of children, and if we put this in another Table it might have: Child ID Number (related field), Name, Grade -- then VS Code & Points.

For the verses, each verse has a Code Number(related field to Verse DB) and a points designation. I would like to do an input of the code number and look up the Points. These are in another DB.

I would like to be able to enter 20 Verse codes & Points for each of 5 days - Monday through Friday.

What comes to mind is a repeating table with 20 Reps - one for Code and one for Points for each of the 5 days. That's 10 fields.

But repeating fields are VERY hard to script for lookups, etc. in the reps - and the common wisdom here is "GET RID OF REPEATING FIELDS"

But I don't quite know how to do this without making hundreds of fields to replace them. How do I create the repetitions in a relational DB without a new field for each entry?

[Rfox]

OK. I got it. All you have to do is post and then you think of the answer. I've been wrestling with this 2 days before posting.

It is just thinking about it in reverse.

The Memory Verse Data base just inputs & associates to the Memory Verse Code the ID of all the kids who did that verse.

Then a Portal will show all the verses that kid has been posted to.

If you have any further refinements, I'm listening.

I think I will forget about the day they learned the verse. That may not be important.

But is there any way to break up the portal list. What if it contain 100 listings? Maybe I should record the day learned - sort by that - and have portals based on the search result. I'll have to study how to do that.

[The Shadow]

Rfox,

Since you're using FM7, you can sort directly in the portal if you like. Also, the portal now has more control over what row to start at, and how many entries to display.

[Rfox]

The challenge now is to show the verses for each day - yet still be able to show TOTAL points for ALL days.

I set up a second relationship for Day Entered in both the Child and Parent DB.

So on the Layout I can choose a child, Type "Monday" in the Day Field and the portal will display all the verses for the child for that day. And that will calculate the points earned that day.

So far so good. But what if I want to be able to have a field with the total points for all days for that child. I haven't figured that out yet.

Or - what if I want to be able somewhere to display ALL the verses learned by that child. I seem to have precluded that by having the double relationship which specifies both Child ID and Day Learned.

Can you help with that. And thanks so much for your efforts.

[The Shadow]

You can add a second version of the Child table to your relationship graph, and have a different relationship to it; in this case, it sounds like you need a relationship based only on Child ID. [use the "add table" button on the lower left of the relationship graph]

If you have a summary field in Child that is showing the Sum of all points, when referenced through your relationship it will show for that day only, but the same field referenced through the new relationship will show the entire sum for that child.

[The Shadow]

If you want an example, you can download one here:

http://www.fmforums.com/threads/showflat.php?Cat=0&Board=solutions&Number=106137

Sorry you don't get a custom example - but I just did one just like this... ;^)

[Lee Smith]

Hi Shadow,

Tip #1

If you use the URL Function from the "Instant UBB Code" box, the [color:"blue"] URL wont make the page go off of the window like yours just did.

Tip #2

You only need to paste the first part of the URL in the Forum anyway in order for it to work. i.e. this much.

http://www.fmforums.com/threads/showflat.php?Cat=0&Board=solutions&Number=106137

Tip #3, you can go back and edit your posts if they don't come out the way you want them too.

Lee

[Rfox]

Thanks, The Shadow. Using 2 Versions of the Child DB with different relationships is great. Now I will look at your example. If I had known there was an example already, I wouldn't have asked the question -- but my searches did not find the answer.

[The Shadow]

Thanks for the posting tips, Lee. I've corrected the URL.