Jump to content

querying junction table


dhartin
 Share

This topic is 5694 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 ]

...

Link to comment
Share on other sites

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 !

Link to comment
Share on other sites

This topic is 5694 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.