Match and copy selected entries from one field to another

[Sholly]

Hi there,

I would like to start by saying "Hello!". I am new to the forum and am new-ish to Filemaker, particularly to scripting. I am trying to save myself time by creating a script to automatically enter some data for me! I will attempt to logically explain my problem here, and I have included an Excel file that hopefully makes things clear.

I study chimpanzees. Every 15 minutes I write down who is in the group I am watching, both boys and girls. I am primarily interested in the girls who were present at all that day, and which ones were present at which times. I have these fields...

date

time

individuals in group (multiple entries)

females in group/day (multiple entries)

females in group/scan

I am trying to create a script to fill in the females in group/scan field. I want the script to do the following for each record:

-look at the entries in the females in group/day field

-go to the individuals in group field

-check for matches between the individuals in group and females in group/day fields

-enter any matches in the females in group/scan field

Does this make sense? I'm sure it's easy and I apologize if this is too simple to be posting here. Any help is appreciated.

database_example.pdf

[bcooney]

You study chimpanzees! How could I resist a Monkey Business database.

I put together a demo of how I'd structure what you need. It's not clear if you have more than one group, but I see this as a typical monkey-membership-group model.

Take a look at the attached file and welcome to the Forums.

MonkeyBusiness.fp7.zip

[Sholly]

Thanks for your response! I see what you've done, and it makes sense. However, I actually simplified my example a bit to keep out some of the graphic details of chimpanzee research. What I'm really looking for is the females that are in estrus (have the big sexual swellings) in the group. In many cases I have several females in a group, but only some are in estrus. So, while I do have individual files for each chimp with their sex (which remains constant), their "estrus swelling" status changes every day.

I attached a new example.

database_example.pdf

[comment]

I think the FilterValues() function could be handy here.

[Sholly]

That definitely seems like it could work if there is a way that I can specify the filter values as "whatever is in field xx" instead of specifying the exact text. For every record, my filter values change depending on the values in the "individuals in group/day" field. Can you do that?

[comment]

Yes, of course: FilterValues ( FieldA ; FieldB ) will work perfectly well. But if you are using comma-separated values, you'll need to convert them to return-separated first, do the filtering and convert them back, e.g.

Substitute (

FilterValues (

Substitute ( FieldA ; ", " ; ¶ ) ;

Substitute ( FieldB ; ", " ; ¶ )

) ;

¶ ; ", " )

BTW, I am not sure what you intend to do with the result - in general, having multiple values in the same field is very limiting in terms of reports and statistics. These should really be separate records in a related table.

[bcooney]

I would just add a field to membership, flag_InEstrus, format it as a checkbox and put it in the membership portal on the Groups form.

[Sholly]

comment: Thank you thank you a million times over for telling me to use that function! I know it's probably common knowledge, but it wasn't to me, so I really appreciate it.

bcooney: The way you suggested is definitely the best way to have organized my database. I wish I knew more when I started entering my data. I think I will go back and fix it so I can do what you're saying, but for now I just did a quick fix with that function so I could get my colleague this data.

Thanks so much both for your help.