Show "non-existing" values

[Gogargirl]

Hi friends, long time no visit, and this site gets better and better!

Reckon my brain addled over the summer as I can't work this one out:

I have a database in which the records are people. There are two number fields, Area and Position. Data in Area can be any number between 1 and 100, and data in Position can be any number between 1 and 10, though not unique (as each Area has positions 1 thru 10). As I work, I need to know which numbers have not yet been allocated in Position. OK, through a relationship I can show in a portal that e.g. numbers 1,3,4,6 and 9 have already been used and exist in the database. But how can I show that 2,5,7,8 and 10 don't yet exist in the database? I don't want a script which finds the unused numbers, but rather a value list/relationship which instantly shows the unused numbers for each area as I click on the Area field. Here's hoping!

[LiveOak]

You can either have all values exist in your file and create a relationship that only shows unused values, build a value list (in another file?) on the fly (using a scripted operation)that only shows the unused values, or a third option. This third option is to use a Troi Text plug-in filtering feature to take a single record/single field with a carriage return delimited list of 1-10, filter out the used values taken from records in your file, calculate another single record/single field list and use this as a value list of available positions.

The trick here is that a carriage return delimited list in single field in one record will display as a value list.

-bd

[Gogargirl]

Thanks Live Oak -

The first option sounds easiest, but how do I set it up? Should the list of all possible values be in a value list? A multi-key? And how can I create a relationship that shows only unused values?

[LiveOak]

Use a separate file with every possible value (#areas x #position = #records). Build a relationship based upon Area. Create fields:

Position (text or number as appropriate)

PositionAssigned (number)

cPositionDisplay (calculation, number or text as appropriate) =

If(PositionedAssigned, "", Position)

From your main file create a relationship to this new file based upon Area. Define a value list for Positions using "use values from a field" and base it upon this relationship and set it to display the related field cPositionDisplay.

To take a particular position of the list for a particular area, have a scrip to to that records and set the PositionAssigned field to a "1".

-bd

[Gogargirl]

Yay! I tweaked it a little to avoid using a script, but it works a dream. The big thing I'd forgotten is that, if the test refers to a field that doesn't contain a value, the If function returns a 'false' result by default. Thank you!