I posted this question in the FM7 forum, and am reposting it here where it probably belongs.

Two tables:

1) People - Person ID (auto-serial), Name

2) Places - Place ID (auto-serial), Person ID, Place Name

A portal on a People layout contains a list of all places the person has visited, where (People::Person ID = Places::Person ID).

Person #1 has visited Chicago, Seattle, Chicago, New York, Seattle.

Person #2 has visited Seattle, Fresno, Seattle, Seattle, Fresno

Is it possible to make a relationship for a portal on the People layout that condenses similarly visited places? I.E., Person #1 sees "Chicago, Seattle, New York", while Person #2 sees "Seattle, Fresno"?

Problem #1: I'm able to indicate when a record is unique to the whole list, but not per person.

Problem #2: It's easy enough to make a valuelist for unique cities, and Queue showed me a really elegant use of the "valuelistitems" function (included in the attached file, although sometimes it doesn't seem to update very well! Did I misuse it?). A workaround would be to use the valuelist to could create a mock portal, but I can't figure out how to make it behave like a portal (i.e. click on the city name to jump to another table about the city, or list all the People who visited the Place, etc.) The best solution I can come up with is to make a radiobutton and button combination.

Are there any one-click solutions to this problem?

Thanks!

n1k0

Portal Limiting #2.fp7.zip

Hi n1k0,

how about a third table in between with only the unique places. This could be displayed via portal if you also export the PersonID to this table.

Or perhaps a relationship based on the unique-key you have already generated, matched to a variable with value "1", and the PersonID?

holger

Thanks for the thoughts! I had wanted to stay away from a 3rd table for several reasons. Having a 3rd table would mean I'd have to constrain the list of cities to those I had pre-entered. There are a number of workarounds I can think of with scripts and such, but a portal would be ideal.

Maybe it's too complex for FileMaker's relational engine. (Blasphemy!)

n1k0

Hi, n1k0. I can't think of any RDBMS that can handle many-to-many relationships without a join table. In fact, i can't even conceive of it. (I may be saying more about my lack of experience than i am about database structure.) The good news is, FM7 handles this type of scenario much better than previous versions. You will have to script creation of new cities, but i don't think that's so bad--instead of allowing a user to click directly into the City field, put a button on that field that, after entry, checks for the existence of that city, and creates the City record if necessary.

Jerry

Actually you can create a many to many without a join table in FileMaker. I don't recomend it but it can be done.

I am not sure that this is situation that requires a many to many relationship.

What is needed is a way to display the data free of duplicates. If you mark the duplicates you could then use a filtered portal to show only the first instance of a city. But you could not use this portal to add new trips.

You might take a look at the Troi Text Plug-in http://www.troi.com The SumText function will provide you with a list. The UniqueLines function will reduce this list to the list you want.

Hi Ralph, can you post an example of a many-to-many that works without a join table, just out of curiosity? (If it's not too much trouble.)

Hey n1k0, did this idea not work for you?

It did! It works great as a valuelist, but I was seeking a away to show every city visited at once such that each city is a button that launches a script based on city name ("go to related portal"-like behavior). The possibilities I can see are:

1) pulldown (good for scripted entry and subsequent action, but doesn't show all citynames onscreen),

2) radiobutton list and button (I can't figure out how to make this a one-click solution), or

3) portal + script to flag first place record upon creation (like QuinTech suggests).

Hope my descriptions are clear! Thanks everyone for all the ideas, it really helps.

n1k0

Makes sense. I think I could do this in 5.5. 7 needs its related repetitions bug fixed first, then it would work. Bummer though.

Here's one way to do it, if you don't mind a 'submit' type button on your layout. First click into the repeating field on the right, into the repetition that contains the desired place. Then click 'Go'.

Portal Limiting #2.zip

That calculation is amazing! Never seen anything like it. Think I'll stick with a radiobutton valuelist, even though it displays "<no values>" or something when the list is empty. Really dumb that one can't get ride of that!!

It's a fairly common calculation for parsing through lines of data. I only expanded it using the new Get(CalculationRepetitionNumber) instead of a traditional 'index' field.

You could possibly use a visibility portal to hide the radio button field when there is no data for the list. Would you prefer to do that?

Hey JT!

This is one sweet treat! Get ( ActiveRepetitionNumber ) is a great Selector and it incorporates the best of both worlds - popups and portals. Thanks for coming up with this! And it does so by using the dreaded 'repeating field' and our new love - script parameters. I wonder how we ever lived without repetitions and script parameters now.

With this type of Item Selector (menu across top), one should even be able to make several selections from it (as a list) before finally 'submitting,' right? The possibilities are exciting. I would be interested in seeing how you would best accomplish that also (selecting several and even unselecting again). And you can even split the list into 1-5 and 6-10 if you wish. It also doesn't jump like portals can occasionally do.

Yep! This one woke me up! I will certainly be using this! Thank you.

LaRetta

Unfortunately, I can't see how it could be used for multiple selections. You can't make a repeating field into a button and pass its current repetition; it only passes the first repetition if you try. That's why I made the 'Go' button. Once you enter a repetition, it's easy. If you didn't enter one, it would always use the first repetiton's value. So multiple selections would have to be done in the same manner but with at least two buttons for action (save and go): select, save, select, save, go. Very kludgy, if you ask me.

I see your point on that.

A plugin might work; like SecureFM. It can activate from a calculation change (I believe I read). I haven't done much with SecureFM yet - still lining out the Menu selections and Windows controls but there may be ways to do it.

I nonetheless see great possibilities for this and I can't wait to try them all.

LaRetta

Man, I'm out of my league! Can you point me to an example of a visibility portal? Sounds really useful..

Go to John Mark's site, click on Resources, and search for 'visibility' in either all FM versions or version 6.

Very useful exploit. Thanks for pointing it out!