I'm using FMPro 8 Advanced. My database has got a many-to-many between
tables A and B represented by a table AB -- so it's the normal

A -<AB>-B

sort of pattern. In my application I have a form layout for A in which I
want to allow the user to add a new AB record; i.e., relate an additional B
to the specific A. I want to do this by way of a pick list, a new window
that opens and presents a portal containing B records that might be added.
(Ultimately I'd like this to be a multiple select list, but that's another
story.)

The trick however is to populate the portal ONLY with B's that aren't
already related to A via AB. I'm at a loss as to how to define the
corresponding relationship between A (or AB) and another instance of B that
will provide just those records out of B. One thing I've considered is using
a global field containing a multi-key constructed from the elements of AB
related to the specific A, but I need a != relation and (as I understand)
using a != with a multi-key would use "or" semantics and not produce the
list I'm looking for.

Any suggestions?

Thanks in advance,
Bill

I have a similar need A-<AB>-B . Below is some script I wrote to;
a) check that the B (Brochure) record exists and create a new one if it
does not
b) check whether an AB (Broc_veh) record exists and create a new one if
it does not. This table contains a unique field BROC_ID that is a
combination of the A and B link fields.
The script is called from a button on the A (Vehicle_model) layout. The
variable are used to automatically populate the fields in the newly
created records.

Michael Paine

--------
Set Error Capture [ On ]
Set Variable [ $$MODEL_CD; Value:VEHICLE_MODEL::MODEL_ID ]
Set Variable [ $$BROC_CD; Value:VEHICLE_MODEL::AUST_PUB1 ]
Set Variable [ $$BV_CD; Value:VEHICLE_MODEL::AUST_PUB1 & "/" &
VEHICLE_MODEL::MODEL_ID ]
Enter Browse Mode
Go to Layout [ “BROCHURE” (BROCHURE) ]
Enter Find Mode [ ]
Set Field [ BROCHURE::broc_id; $$broc_cd ]
Perform Find [ ]
If [ Get(FoundCount)=0 ]
Show Custom Dialog [ Title: "Confirm New BROCHURE Record"; Message: "A
BROCHURE record does not exist for this
model & brochure. Create new record?[" & $$broc_cd & "]"; Buttons:
“Yes”, “No” ]
If [ Get(LastMessageChoice)=1 ]
New Record/Request
Show All Records
Set Field [ BROCHURE::broc_id; $$broc_CD ]
Set Field [ BROCHURE::ncap_org; "ANCAP" ]
Exit Script [ ]
End If
End If
Enter Browse Mode
Go to Layout [ “BROC_VEH” (BROC_VEH) ]
Enter Find Mode [ ]
Set Field [ BROC_VEH::broc_mod; $$BV_cd ]
Perform Find [ ]
If [ Get(FoundCount)=0 ]
Show Custom Dialog [ Title: "Confirm New BROCHURE/VEHICLE Record";
Message: "A link record does not exist for this
model & brochure. Create new record?[" & $$bv_cd & "]"; Buttons: “Yes”,
“No” ]
If [ Get(LastMessageChoice)=1 ]
New Record/Request
Show All Records
Set Field [ BROC_VEH::model_id; $$MODEL_CD ]
Set Field [ BROC_VEH::broc_id; $$broc_CD ]
Set Field [ BROC_VEH::broc_mod; $$bv_cd ]
Go to Layout [ “Model Master” (VEHICLE_MODEL) ]
Exit Script [ ]
End If
Else
Go to Layout [ “Model Master” (VEHICLE_MODEL) ]
Exit Script [ ]
End If



---------
Bill Cohagan wrote:

In article <MxV6f.16930$GQ.90 (AT) tornado (DOT) texas.rr.com>, "Bill Cohagan"
<mylastname (AT) ACM (DOT) ORG> wrote:

I'm not sure if I've quite followed all of that.

You've already got a relationship A -> B, so instead of opening a new
window with a portal to the B records not related to A, why not simply
open a B window itself that shows the non-related records?

All you need is a button / script in A something like:

Set Field [g_A_RelationKey, AtoB_RelationKey]
Go To Related Records [Relation_To_B, Show only related records]
Perform Script [External, B:AddToA]

This script copies the A -. B relationship key data to a temporary
Global field and then goes to file / table B, finding all the already
related records, and then runs a new script in B called AddToA which is
something like:

Show Omitted
Go To Layout [AddToA_Layout]

This simply swaps the records already related to A with those not
related to A. You end up looking at all the non-related records.

The AddToA_Layout is then shown in List View with each record having an
Add button that runs a simple script:

Set Field [BtoA_RelationKey, A::g_A_RelationKey]

That would be the basics, although I'm not sure how fast that is or if
it's too "screen flickery" for your needs.


A better approach would be to for the "Add" button to set a temporary
field in each B record to A::g_A_RelationKey, then you can have an "OK"
and a "Cancel" button at the bottom of the window. "Ok" would run a
script to find all the records with the temporary field set, Replace
the real BtoA_RelationKey data and then blank the temporary field
(ready for next time), while the "Cancel" button would simply blank the
temporary field and not change any links.

You would probably also need to have a button on each record to "Don't
Add" it in case the user changes their mind about a particular record
before pressing "OK". (Or if you want to be fancy, a button that
alternates between "Add" and "Don't Add" with each mouse click.)



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

I agree with everything Harry said, but instead of 2 buttons just use a
checkbox.
If you wanted to you could even make the checkbox a calc field which
has a value list attached to it that shows it to be checked if the calc
evaluates to 1.
Now all you have to do is make your calc evaluate to 1 if an AB record
exists.
Put a script on the checkbox that either creates or deletes the AB
record but don't allow entry into the checkbox.
The user thinks they're selecting it even though a script is doing the
work.

In article <MxV6f.16930$GQ.90 (AT) tornado (DOT) texas.rr.com>,
"Bill Cohagan" <mylastname (AT) ACM (DOT) ORG> wrote:

Sounds to me like you really want a one-to-many, one A to many Bs:

A - B, allowing creation of B records by means of the relationship.

Then you could put a portal in A and create the Bs that way.

Making it a one-to-many without the intervening AB, and only creating Bs
via the A - B relationship would fulfill the second paragraph of your
description.

Is there some other reason you need the many-to-many, and need to create
AB records? If not, the one-to-many would seem a simple way to go.

Bill Collins

--
For email, remove invalid.

From what I've seen, I'll take a different approach then many of your
other responses.

Though tyhe site is down at the moment, you can go to
<http://www.briandunning.com/filemaker-custom-functions/> and search on
'value filter' for some custom functions that will do what you want.

Go to http://www.databasepros.com/resources.html and enter "True Value
Filter" (without the quotes) as your search criteria.

The key is in using the ValueListItems or GetNthRecord() functions to
get the values of B (or in your case, you'll want the record IDs for
those values) and filtering out the 'B' IDs from the AB records athat
are related from 'A'. The result would be a calc field that contains a
list of B_IDs for all the B's not already in the related ABs. Use this
in your portal relationship to show the B's that you want. You can do
all of this without scripts.

After taking a look at the examples on the above two websites, let us
know if you need help applying those examples to your specific situation.


Bill Cohagan wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

Brian Dunning's site is now back up and I found that my proposed search
criteria didn't get any results. So here are three custom functions
that will do what you want.

Though defined custom functions will work in any version of FM7/8, you
may only create them using FM Developer 7 or FM Pro Advanced 8. If you
don't have these versions of FileMaker, then you cannot create custom
functions. Of the following three calcs, the only one that doesn't use
recursion is RemoveByValueList(), which means you should be able to use
the same calc as just a regular calc field instead of a custom function.

RemoveByValueList()
http://www.briandunning.com/cf/119

SubtractValues()
http://www.briandunning.com/cf/230

ZapValues()
http://www.briandunning.com/cf/193



Howard Schlossberg wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

Thanks to everyone for the helpful responses. It'll take me a while to
digest all of the suggestions. I'll report back with whatever the final
solution turns out to be.

Regards,
Bill

"Bill Cohagan" <mylastname (AT) ACM (DOT) ORG> wrote