I have an problem that, try as I might I just can't get my head around.
I have looked in the forums but can't seem to find anything quite the
same. I would be very grateful for any help.

I am working with 5 tables in FM8 (Win XP) that relate to this problem

Clients
Inventory
Contracts
Contracts2Inventory
Tasks

So each Client has a unique ID, as does each Contract and Inventory.
Contracts and Inventory also have descriptions. Contracts and Inventory
records also have a ClientID (to say who they belong to). I have
relationships between Clients and Contracts and between Clients and
Inventory by the ClientID field.

I then have a join table, Contracts2Inventory, so I can have a
many-many relationship between Contracts and Inventory.

The 5th table, Tasks, has a set of multi-purpose global fields, and
works as a wizard for doing relatively complex with the Inventory and
Contract records.

So what I am trying to figure out is all about value lists. In Tasks I
currently have 3 drop down boxes as part of the interface for a script.
The first selects the ClientID. The second selects a ContractID and the
third an Inventory ID.

I have a relationship between this first field in Tasks and the Client
ID fields in Contracts and Inventory. I then have conditional value
lists, based on the ClientID relationship for both the ContractID and
InventoryID fields so you can only select a Contract and an Inventory
item that matches the selected ClientID.

Note, not all Inventory items are attached to a Contract via the join
table.

My problem is this. At the moment, the Inventory value list is only
based on ClientID. What I would like is that if I select a Contract
also (which is optional), I restrict down the choice of Inventory in
the value list to items that match the ClientID but, somehow using the
Contracts2Inventory join table, also match the ContractID I have
selected. If no ContractID is selected, I would like a choice of all
Inventory for the selected Client.

Apologies if I have not explained this terribly well. I would be happy
to provide more info as required.

I have considered multi-line keys, compound keys and getfield functions
but I just can't figure out a way to do this.

Any help would be much appreciated.
Many thanks in advance
Michael

MichaelR wrote:
In case anyone was thinking about this, I have found a solution.
I created a field in each Contract2Inventory for the ClientID (looked
up from the related Contract). I then also created a text field defined
to be calculated as the
"ClientID & ContractID"

In the Inventory table, I created a field that was defined as the
ValueList of all related Contract2Inventory join-table entries.
Annoying this could not be set as an unstored calculation for reasons
that will become clear later, so I had to change a couple of procedures
elsewhere so that this field would be updated (by a script) every time
a new entry in the join-table was created. This meant creating the
joins using a scripted interface rather than a drop-down in a portal as
before.

Anyway, then I created a second field ("iMatchField")in the Inventory
table that was calculated as the ValueList field I had just defined and
on its own line, just the ClientID

In the Tasks table, I then created a field("tMatchField") that was
calculated (auto-enter text) as
ClientID & ContractID
as entered in the task wizard as described previously.

I then created a relationship between this tMatchField and iMatchField
and used this to build by list of Inventory items for selection. This
meant that if a ClientID was present in the Task interface, it would
match the ClientID line of any Inventory item that was suitable, but if
a ClinetID and a ContractID was specified the tMatchFiled would be
compounded so only Inventory items that were both the same client and
the same contract would match.

The key is really in having a multiline match-field in Inventory and
making a more specific match-field in the Tasks table.

I could probably refine this down, now I have some principles to work
with but this wil do me for now.

Thanks to anyone who gave this some thought.

In article <1163578991.113160.273000 (AT) m73g2000cwd (DOT) googlegroups.com>,
"MichaelR" <michaelirudge (AT) gmail (DOT) com> wrote:
I was going to post that "Client ID & ContractID" solution yesterday
(although I usually use ClientID & "-" & ContractID since the "-" makes
it easier to see the two separate codes when trying to debug things),
but I ran out of online time. I did type it up off-line last night and
was about to double-check and to post it today, but you've beaten me to
it by finding your own solution. )

You can't use an unstored calculation because the relationship doesn't
like them, but in newer versions of FileMaker you may be able to use an
Auto-enter Calculation that is also set to re-calculate ... I'm not
quite sure how those work (and they still strike me as a little silly,
if useful in this type of situation).


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

Many thanks for taking time to look at this Helpful Harry. Keeping the
ValueList as unstored is annoying here but it doesn't work otherwise.
Letting the calculation index (stored) results in the field reverting
to "?" which is not much use! Auto-Enter calculations don't "notice"
changes or additions to related records so that is not much help
either... So I have reverted to a scripted method of keeping the fields
up to date. It has some benefit in that the scripted interface for
finding records to relate in the first place is more user friendly than
a drop-down, which can be a struggle to search through...

Anyway thanks again.
Michael


Helpful Harry wrote: