Convoluted value list

[faada]

Hey all,

I'm developing a multi-location inventory solution using a number of tables, 3 of these tables are:

1) Item - with fields Item# Description

2) Location - LocCode, Description

3) ItemLoc - Item#, LocCode, Quantity

There would be a record for each item in each location. So if there are 2 items (1,2) and 3 locations (A, B, C) there would be a maximum of 6 records(1A,1B,1C,2A,2B,2C).

After I create a new item in the Item table, I would like to create a record in the ItemLoc table if there is no matching record already existing. The solution I envision but do not know how to create is for a the user to be able to create a new record in the ItemLoc table where the enter the Item# from a value list based on the Item# field in the Item table (easy enough), but when it comes to selecting the location for that item, I want a value list to pop up which will only show the locations for which there is no ItemLoc record already existing.

I hope this explanation is not as unclear as I think, any help would be greatly appreciated.

[Ender]

Hey faada,

This idea of an exclusive value list sounds like it could be useful, and it seems like there should be a cool way to do this dynamically with FM7's new conditional relationships. To try this, I went with a more intuitive example: Assigning projects to people, where you would only want to show the projects that that person has not already been assigned. I came up with a good design (shown below,) but I wasn't able to get it to work. There are two problems I ran into:

1. If there are no records in the Assignment join table, then the relationship has no matches and the conditional value list won't work.

2. There seems to be a bug with the <> relational operator when trying to use it from more than one table away. It works when there is one portal row, but not more than one.

The value list is based on the Person table, showing only related ProjectsNotAssigned.

[Ender]

Instead what I came up with uses scripts to add and remove assigned projects. The scripts also set some IDs so that there can be a direct relationship between Person and ProjectsNotAssigned, avoiding the problems I noted above.

The attached file demonstrates this technique. It works fine, but man, I wish I could get the dynamic method to work. Maybe someone else has succeeded?

ExclusiveValues.fp7.zip