How to search only the first repeating field?

[Lord HellFire]

Hi

My database is a project database, where a repeating field is used for all the individual parts of the project and the first field is basically used as the overall status, except when there are more than one part in the project.

A project with only one part uses just the first field. A project with 10 parts uses 10 of the repeating fields. Where the first is used as overall status.

Problem is when searching for open and closed projects. A project can have closed parts, but still be open, which means the first field can be open, while some or all of the parts are closed.

I have a script, which is supposed to show only open projects.

It does so by omitting all projects, which contain the "closed" parts. Problem is that I cannot use ONLY repetition 1 to search in.

I can specify any other repetition where it only searches that one, but when 1 is specified (which is default), it searches through all the repeating fields.

If there any way to search ONLY the first repetition in a repeating field?

Edited October 12, 2005 by Guest

[Raybaudi]

Hi,

how about make a new calculated field (firstRep)

GetRepetition ( yourRepField ; 1 )

and search on this new field ?

[bruceR]

Ditch the repeats. This just one of a longlist of problems.

[IdealData]

The solution by Daniele will work fine, but it sounds like you should consider going to a relationship to replace the repeating fields.

[-Queue-]

A non-repeating calculation of

yourRepField

will work also.

[RodOfNOD]

I have a similar issue. Repetition fields allow for a really simple to make db for my needs but have one minor and yet completely necessary draw back to using them.

When a user does a FIND within the field it searches all parts of the field and not just the one that is being displayed.

ie. I have a layout that has each rep field showing as a seperate field to the user. If I could make FM search ONLY that iteration then I could solve a major issue I am having.

If not then I need to find a way to do a SETFIELD that allows me to use a STRING instead of a hardcoded parameter.

Anyhelp would be great.

Edited October 27, 2005 by Guest

[Lord HellFire]

[color:gray]My own solution to the problem...

I found a way. I'm not proud and it gives extra work in making the scripts do the correct work, but I did the following.

I have a repeating field with 50 repeats.

I need to be able to search the first repeating field only, which apparently will not work as the search request searches all repeats when 1 is specified.

Instead I used the 50th repeat field as the first.

Insert the field to the layout, set it to show only repeats "50 to 50". This will show only the 50th field as the first line.

Copy the field and set the new field to show repeats 1 to 49.

This enables me to use field 50 as the first field.

I had to adapt all the scripts to reflect this, however. A lot of extra work to make them consider the 50th field as the first.

But it works...

Maybe Filemaker should be forced to consider repeat 0 as all the repeating fields, so repeat 1 could refer to that field alone.

Otherwise Filemaker is great

Well.. Maybe I'm not really as smart as I thought I was.

I assumed it would work.

It doesn't. Repeating fields act as one field, when searching.

The only option is to have a seperate field to use for searching, which hold a copy of the data (probably through a calculation to keep the data updated)

Once again, I assumed that will work. Prove me wrong. It's not hard.

[color:brown]I need repeating fields, as they make it MUCH easier to create the database. If I had to create one field for each needed field, I'd have to create hundreds of fields, instead of just 15 repeating. (50 repetitions each).

I'd also have to specify EACH field in the calculations, as I still have not found a way to calculate a fields name, which can be used to specify a field.

The SET FIELD option in scripts would be a killer to create, if I could not use repetitions.

(hides the long list of wants and needs, which probably tell more about my inability concerning databases, than filemakers shortcomings)

Edited October 28, 2005 by Guest

[comment]

I don't think it matters which repetition you enter your search criteria into. A repeating field is still a single field.

A requirement to search individual repetitions indicates that a repeating field is NOT suitable for storing such data.

[Lord HellFire]

You're right...

I've edited my previous post to reflect this.

[comment]

If I had to create one field for each needed field, I'd have to create hundreds of fields, instead of just 15 repeating. (50 repetitions each).

Have you considered creating a related table (with 15 fields), and putting the data there?