querying junction table

[dhartin]

wondering if someone know a way to do this.

I have a many to many relationship between a matters and clients table.

I am displaying the clients for a matter via a portal on the matters table and am using merge fields to display the data "nicer" (eg Surname,Firstname rather than two fields that will likely leave alot of white space)

So in order to add clients I'm using a lookup layout for the user to select the one they want.

Is there a way I can query the contents of the junction table before adding a client to make sure their not allready associated with the matter.

I figgured I could add a loop in a script and check each row of the junction table for a match but dont think that's overly efficient.

Is there any way for me to something like: "SELECT FROM tblMattes_Cliets WHERE ClientID=1 AND MatterID =8" and if it returns a value then dont add it ??

While I'm at it - I'm figguring that there's not a "free form" code type window for FM? Is it all controled via the built in scripts? (Am comming from MSAccess/VB type background)

Any comments appreciated

Thanks

anyways.. a merge field

[Ender]

See this thread for a couple ideas for filtering out choices based on what's already been selected:

http://www.fmforums.com/forum/showtopic.php?tid/128500/

It's not necessary to use Chuck's custom function for this, if you use the valuelistitems trick. Check the UserGroups.fp7 demo to see it in action.

[Søren Dyhr]

make sure their not allready associated with the matter

Enders suggestion is what some call a strainerlist, but there are more to the issue ...how should uniques from each of the lists be dealt with (left/right inner join)? Another approach is full outer join... http://www.briandunning.com/cf/39

--sd

[comment]

Since you are already presenting the user with a list of clients to select from, you can make it very simple:

Go to Related Record [ From table: "Clients"; Using layout: "SelectClient" (Clients) ] [ Show only related records; New window ]

Show Omitted Only

Pause/Resume Script [ Indefinitely ]

...

This will show only clients that are unassociated with the matter at hand.

A slight adjustment is required to deal with matters that do not yet have ANY client assigned to them:

If [ Clients::ClientID ]

Go to Related Record [ From table: "Clients"; Using layout: "SelectClient" (Clients) ] [ Show only related records; New window ]

Show Omitted Only

Else

New Window []

Go to Layout [ "SelectClient" (Clients) ]

Show All Records

End If

Pause/Resume Script [ Indefinitely ]

...

[dhartin]

wow, excellent responses !! Thank you very much

I havent started playing with custom functions yet, so thanks for the introduction - I think I will enjoy getting my head arround those.

And comment's script seems like a very neat solution indead. Had it implimented and working perfectly in all of 2 minutes

Thanks once again !