y

[raymanj]

Is there a function that will do the inverse of the filtervalues function? Currently this fuction will filter all values out except the ones that I pass it. What I want is to pass what I don't want and for it to give me everything else.

Problem:

I have two text lists. Each value is sepparated by a return. List1 is the list I need to filter through. While list2 is a list of items that I don't want in list1.

So does anyone know of a simple solution?

[CyborgSam]

I think I may be misinterpreting what you want:

The Substitute function can eliminate a text string from another text string by specifying a blank replacement string.

For example: Substitute( Text2BFiltered ; TextToEliminate ; "" )

[Raybaudi]

Hi

I think that this custom function makes what you need ! ???

[Søren Dyhr]

Since you not are on 7Dev or 8Adv are custom functions out of reach ...so your data needs to be restructured according to 1NF

http://databases.about.com/cs/specificproducts/g/1nf.htm

...which says one fact per field ...well we all ought to abide to it, and leave the cleverness for multiline keyfields only. If this is the case could this be utilized:

http://www.briandunning.com/cf/193

--sd

[comment]

If the purpose is to generate a multi-line key, it can be done without a custom function - using a repeating calculation field.

[Søren Dyhr]

repeating calculation field

Good point!!! Which I tend to forget in eagerness to use CF's, my apologies!

--sd

[raymanj]

The two functions ZapValues and DifferenceValues do exactly what I needed. Too bad Filemaker did not add an option to their built in function to handle inverses. It would have been very simple to implement, such as:

FilterValues ( textToFilter , filterValues , InverseResults )

Where inveseResults a "0" will give you the standard result of FilterValues and a "1" will give you the opposite result.

I think I may be misinterpreting what you want:

The Substitute function can eliminate a text string from another text string by specifying a blank replacement string.

For example: Substitute( Text2BFiltered ; TextToEliminate ; "" )

CyborgSam, the substitute function would only work if the TextToEliminate was a single value. I would not be able to pass a list of values. If I wanted to use this function I would need to break down list2 into single values and loop through list1 and check for duplicates to eliminate.

So now I have to choose, use one of the custom functions or loop through the list and use the substitute function. I would guess that the looping would be slower than a custom function, but the custom function has a limitation of 10000 iterations (10000 values in the list).

Well, thanks for all the help.

[Ender]

If you have more than 10,000 items in your value list, then you should consider putting those values in a separate table. Depending on what you're doing, there may be a way to filter them directly through a relationship.

[raymanj]

So now I have to choose, use one of the custom functions or loop through the list and use the substitute function. I would guess that the looping would be slower than a custom function, but the custom function has a limitation of 10000 iterations (10000 values in the list).

Well, I learned something new today. When I tested the speed of the various options that were given to me above, I found out that custom functions are not faster than looping in scriptmaker.

I compared two lists List1 contained 2500 values and List2 contained 1400 values.

Here are the times for each option:

FilterValues (FileMaker built in function) - 0:03 sec.

DifferenceValues (Custom function link above) - 2:10 min

ZapValues (Custom function link above) - 1:57 min

Own Loop in Script Maker (Code below) - 0:21 sec

ScriptMaker Loop Code

Set Variable [ $List1; Value:globalText1 ] 

Set Variable [ $List2; Value:globalText2 ]

Set Variable [ $ListCount; Value:ValueCount ( $List2 ) ]

Set Variable [ $Count; Value:Case ( $Listcount > 0 ; $Listcount; 0 ) ]

Set Variable [ $ListResult; Value:$List1 ] 

Loop

Exit Loop If [ $Count ≤ 0 ]

Set Variable [ $ListResult; Value:Substitute ( $ListResult ; GetValue( $List2 ; $Count ) & "¶" ; "" ) ]

Set Variable [ $Count; Value:$Count - 1 ]

End Loop

Set Field [ globalText3; $ListResult ] 

[Søren Dyhr]

But where is the test both Ender and I talk about, the normalized data structure??

There is another flaw in the reasoning, the custom functions do not consider the sortorder in the lists.

--sd

[raymanj]

But where is the test both Ender and I talk about, the normalized data structure??

I am not sure what test you are talking about.

There is another flaw in the reasoning, the custom functions do not consider the sortorder in the lists.

There is no sorting involved. It is not used in any of the methods to compare the two lists. All I wanted to do was filter out some data in one list based on another list.

Now , regarding Enders suggestion, this could be done and maybe faster. How would I create a relation that would give me the inverse of filtervalues.

My first list of records contains:

Apple

Cherry

Banana

Pinapple

Grape

Plum

A123

Bean

Chair

Avocado

Car

Bus

Second List of Records

Plum

Apple

Airplane

Dog

Chair

Banana

Bird

My resulting List should be:

Cherry

Pinapple

Grape

A123

Bean

Avocado

Car

Bus

[comment]

This is very similar to finding duplicates in the same list. The relationship would be ListA::Value = ListB::Value.

A calc field in ListA (result is Number) =

not ListB::SerialID

flags the passed records. A Find for 1 in the (unstored) flag field will produce the clean list.

Or you could loop thru the records, setting the flag as a stored number field.

---

If you have version 8 (as your script above would indicate), you coud also GTRR from ListB to ListA, using the 'Match all records in the current found set' option, then show omitted.

[Ender]

I was thinking of something along the lines of using a multi-key to hold the list of values to omit, and relate that to a table of the records you want to sift through. The relationship would be a 'not equal to' relationship:

Interface <=> List =

Interface::OmitList <> List::Value

If the OmitList is also made up from individual records in some table, then you'll need to build that OmitList using a Copy All Records or Set Field-Loop.

Then again, comment's GTRR option sounds easier (and faster.)

[comment]

I don't know - seems to me a value list of 1400 values to omit is not much different from a value list of 2500 values to check...

Speed will always be an issue here. Filemaker is not a calculation engine. Subjectively, if the fields are indexed, the find/GTTR may appear fast - but the total time of indexing AND finding should be about the same. And if the values to omit are frequently changed, the advantage of pre-indexing will be lost.

It is difficult to judge these things without knowing the purpose.