Regreting repeating fields

[Ickynono]

I use a fairly simple database the contains donor and donation information. The donation fields are set up as repeating fields: Date, Amount, Check#, Amount and Event.

The problem is that I have been requested to set up a report showing all of the people who donated for a particular event. The search in the event field brings up all of the people who donated but I cant get just the specific event info to show up in a report, just the first item in the repeating fields shows up. I forsee requests for more reports, such as everyone who donated in April 2004, etc. Was using repeating fields a huge mistake? Is there a solution? Help! I've been using FMP for years but I am not too up to speed on the developing side. ANY advice/suggestions will be greatly appreciated!

[RalphL]

Yes, you make a mistake. Now lets fix it.

Make a copy of your database. We will work with the copy. Do you have a Donor ID field? If you don't add a text field named Donor ID. Make it auto-enter serial number starting with 00000001. Put the cursor in this field and then select replace using the serial number option. You should now have an unique serial number in each record.

Next step is to export/import some of the data to a new file called Line Items. Choose Export to a Filemaker Pro file. Selecte the fields Donor ID, Date, Amount, Check#, Amount and Event. After you export to Line Items open it and delete all records. You now have created your related file.

Select Import then select you donor file (copy) as the source. Match field names and choose add new records. After clicking on the Import button you will get a dialog box giving you some options. Choose Split into seperate records.

You now should have a record for every repeation. In your donor database (copy) make a relationship to Line Items Doner ID = ::Donner ID. Add a portal to you layout using this relationship. And display the fields Date, Amount, Check#, Amount and Event.

After every thing checks out delete the repeating fields from your layout.

You will make the report in the Line Item file. You will a relationship in it to the donor database. Again Donor ID = ::Donor ID. Use this relationship to pull in things like donor name, address etc.

[QuinTech]

Hi Ick, welcome to FM Forums. You've got the right idea--what you need is a relational database. You could start by creating an index, or key, field: Make a new text field called "Index". Set it to auto-enter serial numbers. Now on some layout, find all records and replace the contents of that key field (Ctrl-=) with Serial numbers. (Make sure "Update serial number in entry options" is checked off.) You now have a unique ID for each person, which is a good start.

Now save your database as a clone, and import data into the clone from the old database, matching field to field. At the end of the import process, select "Import values in repeating fields by splitting them into separate records" and also have "Perform auto-enter options" UNchecked. You now have multiple records for each person, one record for each donation.

Now save this file to another name and call it Donations. Delete all the fields that are not specifically relevant to donations--Donations.fp5 should now contain Donation Name, Date, Amount, and the Index number we created earlier, and probably very little else. Now go back to your first database, and delete the fields here that you did NOT delete in Donations, with the exception of the Index. Delete all duplicate person records, and create a relationship from the Index field in Person to the Index field in Donations.

You now have a relational database system. I can't get into all the implications of this, but i think you may pick up a lot as you go along, especially from these forums.

HTH

Jerry

[QuinTech]

... and you'll find that posters frequently have different solutions that both work ...

[Ickynono]

Thank you RalphL and QuinTech! I will most certainly try one of your suggestions!

I plan on perusing this forum frequently!!

Thank you again!!