reporting values not chosen in a list

[cebrown]

Hi, I just found this forum & already love it!

Here's my first question (I'm sure I'll be posting a lot more!)

I have a housing database and a few of the fields have value lists that contain the room numbers for different areas. (For example, NewDorm may list room numbers 1 through 100 and OldDorm would list room numbers 1 throgh 50) The value lists are set up to ensure that each record has a unique item. I'd like to know if there is an easy way to run a report in a layout that will show me all of the rooms still available for each area, or not chosen on each list. Has anyone done something similar to this request before? Thanks in advance for your help!

[CobaltSky]

Hi cebrown,

You can get a fair way by judicious use of parsing and nested Substitute( ) functions.

You'll find an example of what I'm talking about in a demo download file available from my website at:

http://www.nightwing.com.au/FileMaker/demos/DynamicValues.zip

But bear in mind that since the length of the calculation string maxes out at a certain point, to handle very long lists with this method, you will need to take more than one pass (each picking up the input string where the previous one leaves off). Multiple passes can readily be incorporated into a single script, or be placed into a cascading calc sequence, but it involves a little work.

However if you prefer, an alternative way to approach the problem is available in the form of the Text plug-in from Troi Automatisering:

http://www.troi.com/software/textplugin.html

...which, among other things, enables retrieval of field values and, most importantly, offers 'NOTText' and 'XORText' functions, which will compare text strings and retrieve lines that are in one (or either) text string but not in the other. This can be used effectively to retrieve an index of selected values and compare them to the value list, returning the difference (ie values not yet selected).

There are a few other ways to approach the problem also, but hopefully the above suggestions will give you something to work with?!