Repeating Field???

[ginger]

We currently have a file "Contacts". Which contains all sale contacts, with multiple fileds for the normal "sales analysis type stuff". We now need to add an event field with an independant date field and an event type field (quote, catalog etc) all following the date field. We would like to be able to track multiple events for each contact - in one entry screen and then track the activity for each contact on a separate summary sheet showing activity by month. So far we have used a repeating field for the date/event in the entry form, however, when we try to take this information to the summary form (where we first do a find on the entry form for the appropriate date range), the count is always reflective of every event for a given contact. Re: say one contact has three events, dated 09/01/01, 10/15/01 and 11/01/01 the summary sheet shows three events for the month of November only? The biggest issue here is keeping data entry to a minimum, so that the entry person can do a find per contact and do the appropriate review then add event and date.

Geez, rereading this I am getting more confused....hope someone else will be able to help sludge through. When we started this project seemed very easy???

[FUBAR]

I don't think I'm going to be much help, but at least I can give you a heads up warning that basically every reply you get to your problem will involve "Don't use repeating fields"

I don't have much experience with them as I've read in a few books (not to mention tons of posts here) about the limitations of using repeating fields.

"Using repeating fields: Generally speaking, you shouldn't. Whenever possible, build your database by creating relationships between files, and list your multiple entries in a portal. Repeating fields in FileMaker are leftovers from when it was a flat file database, not a relational one. Because repeating fields can contain multiple entries in the same field, they are fairly inflexible, can't be individually searched or used in a relationship, and don't work well calculations."

So the only advice I can give you is that you are going to have to most likely give up on the idea of the repeating field and add either have more data entry or redesign your database.

Hope this helps...but it'll probably just add to your frustration

[LiveOak]

The major problem with repeating fields is that you can't easily sort and report the information they contain. If you instead create an Events file related to your Contacts file by a ContactID (number field, auto enter serial number, can't modify), you will be able to find all events for a single day, summarize events by type across multiple clients, etc.

-bd

[ginger]

Thank you so much for your quick response. That is what I was afraid of, oh well, back to the drawing board. Thanx again

[danjacoby]

Put all the event stuff in a related file, then enter the data through a portal. Create a script that goes to the related file, finds all the records for the contact you want, sorts and creates a summary. (I know, I'm skipping all the details.)

Not meaning to repeat info on repeating fields, but they were created back in the days before FMP had relational capabilties. Once the new options kicked in, repeating fields became essentially obsolete.

[ginger]

Oh thanks for all the help.Am doing away with the repeating field. Now have created 10 date fields and 10 event fields in the entry screen. Have read through a "ton" of forums trying to find a solution for doing a find on multiple fields - created a calcultion field which combines all of the date fields (separated by a paragraph return), cannot get a return on a range of dates. i.e. need to know how many contacts had an event from 11/01/01...11/30/01?? Thank you danjacoby, but my slow old okie brain cannot rap around the portal/related file - stuff. Guess you got that my usage with FM was mainly before the "P" was added. Could you give more detail - Please??

[Vaughan]

No no no! Not multiple date fields. At lease as bad as repeating fields!

Create a related file and use a portal to create/display as many or as few rows (records) as is needed. If you don;t know how to do it then learn! Not especially hard.

[ginger]

Thank you for the help Vaughn. Have established a related file, named "Existing contacts". Defined a relationship with the original file "contacts", using the match field "defined event", in the contacts file this should be ex (existing). Then in the existing contact file added fields, customer, event, date of event. Each field is a lookup going back to the corresponding field in contacts. I am getting nothing, have read the book and several forums. I guess to experienced users this is not particularly hard - but i'm really having a time of it...please advise or point in the right direction. Thanking you in advance for your patience.

[Vaughan]

I'm not familiar with your db, but why use a lookup? Just use a related field. Lookups need to be re-looked-up if the value changes, and if they are set up in an existing database. Related fields are dynamic.

I'm not suggesting that lookups are bad, in appropriate circumstances they are great (like when you want to enter a value -- say tax rate -- and leave it at that value for historical purposes even if the rate changes in the future.).

[ginger]

Thanks again, Vaughan. Took out the look-ups, and is retrieving information well now. However, why did you say "no no no! not mutliple date fields"? How would you suggest controlling multiple events on one entry screen in the contact master file? Where each event happens on a different date and we cannot create an additional record for the contact, all information needs to stay with that customer?

[danjacoby]

Use one date field, then import the info from the file with the repeating field. It'll ask you if you want to separate the repeating field into different records. Say yes.

Now you have individual records for each date, all related to your contact.