noob missing basic concept - again!

[Briardlady]

Not sure if this is the right part of the forum. Using FM 9. This is for the index to a book. I have a table with a unique field called "entry #", and several fields that can have the same name in them, "owner last name", "co-owner last name", "third owner", "fourth owner". All of this is in one table.

I want to be able to do a find for a name over all 4 fields that have owner names in them, and get all the entry #'s associated with that last name inserted into a field called "owner entries". Any owner could have 1 to 10 entry numbers associated with it.

I thought it would be easy, just use find and then have a List field, but that didn't work. I've tried about 6 different functions, but all return a list giving each instance of the name, with a different entry #, instead of giving the numbers in a list separated by commas, suitable for an index. I read all the topics going back about 3 months in calculations, finding, and scripting, but can't find anything similar. I tried doing a script (complete noob there), but it gave me the name over and over also. This seems like such a simple thing, but I am finding that I don't know enough to see when something is possible - or not. Thanks in advance for any help.

[bruceR]

Same answer: post a file for us to look at. Maybe your actual file; maybe your actual file empty of data; maybe a simplified version of your file.

In order to get useful help, you will need to be forthcoming with information. Basically you've said, I've got a buncha fields, and I want a report, and nothing I have tried has worked. What are we supposed to do with that?

You appear to have a data structure problem. Why are there all these fields that hold names?

[Briardlady]

Sorry of I offended with the information I posted - I thought it was enough to get steered in the right direction. I hope I figured out how to post a stripped down version of my database. I was very nervous about posting real names, so I did some random search and replace to scramble them. Don't know if that will make it impossible to help, but I've been an identity theft victim, I'm paranoid.

Dog shows are EXTREMELY political and jealous. Owners names have to be listed in an exact order. Saying a dog is owned by John Smith and Mary Jones is completely different than saying a dog is owned by Mary Jones and John Smith. And a dog can have many different owners and breeders. I am lucky - the max in my group is 4. A top special at Westminster can have 7 or 8, with financial backers! My index has to show every dog that an owner has an interest in. The individual dog pages list owners in very specific order.

Thanks for any help you can provide. Is there any site online that has a large number of examples of calculations, scripts, etc? I'll spend the time reading, but I don't extrapolate well. Especially under a deadline.

copy_for_help.zip

[bruceR]

Is there any site online that has a large number of examples of calculations, scripts, etc?

VERY helpful to have the file.

Examples are right in your FileMaker install folder. There are lots of templates; and a tutorial.

Edited August 1, 2010 by Guest

[bruceR]

Saying a dog is owned by John Smith and Mary Jones is completely different than saying a dog is owned by Mary Jones and John Smith. And a dog can have many different owners and breeders. I am lucky - the max in my group is 4. A top special at Westminster can have 7 or 8, with financial backers!

None of these quantities matter if it is set up right, where each person is a separate record.

[bruceR]

Especially under a deadline.

Mind saying what the deadline is? You have bitten off (!) a task that may be more complex than you think and is probably going to be pretty difficult for a newbie.

I suspect you may get a considerable amount of help here; but a lot of changes will need to be made.

[bruceR]

As an example of a calc change:

This:

owner first &" " & owner last & "¶" & co owner first &" "&co owner last&"¶"&third co owner&"¶"&fourth co owner

Can be changed to this:

List(

owner first & " " & owner last;

co owner first & " " & co owner last;

third co owner;

fourth co owner

)

But actually some other changes will need to be made, the whole first, second, etc thing is the wrong approach.

[bruceR]

Here's a start.

copy_for_help.fp7.zip

[comment]

You do have a serious structural issue. I'd suggest you start with the model shown in the attached ERD. This way you enter each person's and dog's details only once.

Something similar should work for the pedigrees, but I am afraid I didn't understand your file at this point.

[Briardlady]

I really appreciate your help. I added the people table, and the fields to my entry info table, and the relationships - all copied from your example into a copy of my database. Figured out how to add Owner_people and Breeder_people to the relationships, that took a while - had to learn about adding a table more than once. Also copied the layouts.

On the fields, you defined:

Breeders = Substitute( List( Owner_People::FirstAndLast); "¶"; ", ")

and

Owners =Substitute( List( Breeder_People::FirstAndLast); "¶"; ", ").

Is this right? Seems to be flipping owners and breeders.

Now for a biggie - how did you import ALL the names from Entry Info into people, and ID them as breeders or owners? All I have been able to import is the entry #'s, once each. I'm sure you didn't do that one person at a time - I think?

I'm starting to understand the value of relationships. When I first designed this database 2 years ago, I did have a table for people, then took it out. Must have been right after I did the tutorials, then the reason got lost in time.

[bruceR]

Note the example file is from Bruce, well thought out ER diagram from Comment.

Owner/Breeder list calcs: oops.

Records: exported, created a script.

Edited August 1, 2010 by Guest