Show Unique Values from Repeat Field within a Record

[else2244]

Hi all,

Hope I found the right forum. I'm lost. I've looked for "contains" and "unique" but so far all I have found refers to between records and I need within a record.

I have a repeating field for artists' names. There are 16 repetitions. I want to be able to set a second field on the same record to the unique names in the artists' name field.

If this is what is in Artists' Names field in record 1

repetition 1 =Bob

repetition 2 =Sue

repetition 3 =Sue

repetition 4 =Bob

repetition 5 =Jack

and so on up to 16

I want Assigned Artists field in record 1 to be:

Bob Sue Jack

Is there a contain function? I think more like an AppleScript person than a FMP person and I keep getting hung up on the AS ability to say "If list x contains "Bob" then.... Maybe a value list? I've seen lots of posts about getting a list of unique values by using a value list but then how do I show the whole list in a field and can it change with every record?

Thanks bunches for any help.

[sbg2]

See my reply in this thread:

http://fmforums.com/forum/showtopic.php?tid/173117/post/190358

and before you say but hey I have a repeating field... see attached

Repeater.zip

Edited January 19, 2006 by Guest

[else2244]

Hi sbg2,

I did say that!

Your solution works like a charm. I thought value lists would be the solution but I couldn't get a handle on it.

Can you explain to me why the Pop-up Menu setting will show the data from both fields and the Drop-down Menu setting will only show the ID and not the name?

Also, what if I make a mistake selecting an artist and then realize I don't need him on this record? If I'm using a Pop-up Menu, I can't delete the entry. Why is that? How to I get rid of the entry?

I know I haven't had enough sleep and that's probably making me very dense this morning so please bear with me.

Thanks so much!

else2244 :)

Edited January 20, 2006 by Guest

[sbg2]

"Can you explain to me why the Pop-up Menu setting will show the data from both fields and the Drop-down Menu setting will only show the ID and not the name?"

I assume you mean "POP-UP LIST" not "drop-down menu" and you are talking about the MailingList file? Both types of formatting should show the ID and the Name, unless you change the actual value list settings through File>Define>Value Lists.

"Also, what if I make a mistake selecting an artist and then realize I don't need him on this record? If I'm using a Pop-up Menu, I can't delete the entry. Why is that? How to I get rid of the entry?"

Assuming you are talking about the Repeater file...Either:

a) Click the field in question and select any value then immediately use the backspace or delete key to clear the field.

Right click the field and use Cut to clear the field.

If the field was in a portal you could use the Delete Portal Row script step.

[kristel]

Thanks for the tip. Now the other way around.

For ex. in record 1, I have a text field with:

Bob & Sue & Jack

and I would like to have it split in repeating fields:

repetition 1 =Bob

repetition 2 =Sue

repetition 3 =Jack

and maybe more...

Any idea ?

[bruceR]

WHY do you want to use repeating fields? They are almost always the wrong choice, a relationship should generally be used.

[kristel]

Difficult to explain, so I will try to describe the situation as good as possible (sorry for my poor english):

I have these 3 tables:

-Releases

-Songs

-Composers

1 release features several songs

1 song can be on several/ different releases

1 song can be written by several composers

for that I create another 2 tables:

- Order

(which songs are on which releases)

- songs per composer

(which songs are written by which composers)

the records of these 2 tables are automatically created via portals / relationships

But NOW, to calculate the royalties I need an extra (temporary) table with records of "order" split by "composers". I do not think I can do this via relationship. So I would like to make a script to Import/split the records.

It would have been easier if my composers would have been a repeating field (instead of a separated table), as the import function automatically split the records with repeating fields. That's why I asked above "how to re-create a repeating field".

But if you have another solution (script). Please feel free to share.

Greetings.

Kristel