[Update] 28 mei 2019: Met de custom-function “ListLogic” kan je deze functies in één functie stoppen. Je hebt dan wel minsten FMP(A) 16 nodig. Zie: https://www.filemakertips.nl/listlogic/ [/Update]
Enkele weken geleden heb ik hier de Custom-Functie Unique Values gepubliceerd. Met die functie kan je uit een hoeveelheid waarden, de unieke waarden filteren en naar behoefte (geen) rekening houden met hoofdletters. Deze functie leent zich echter ook prima om logische problemen op te lossen met value-lijsten. Om te beginnen geef ik eerst de 3 belangrijkste functies, die je nodig hebt en waarmee al het andere valt af te leiden.
De eerste is alle waarden tonen die wél in “Lijst A” staan, maar niét in “Lijst B” (NOT), Not_Values ( Lijst A ; Lijst B ) :
| Lijst A | Lijst B | Not_Values |
|---|---|---|
| Nummer1 Nummer2 Nummer3 nummer1 nummer2 nummer4 G P T |
J F G Nummer4 P T nummer2 |
Nummer1 Nummer3 |
Deze functie is in dit voorbeeld zoals je ziet niét hoofdlettergevoelig, maar dat is simpel in te stellen.
Met calculatievelden (en één Custom Function)
De NOT-functie als calculatie ziet er zo uit:
Let ( [
lista = FieldA ;
listb = If ( lista ≠ “” ; FieldB ) ;
blist = UniqueValues ( listb & ¶ ; 0 ; “” )
] ;
Substitute (
TrimAll (
Substitute (
UniqueValues ( List ( blist ; lista ) ; 0 ; “” ) ;
[ blist ; “” ] ; [ ” ” ; “@$space$@” ] ; [ ¶ ; ” ” ] ) ; 1 ; 1 ) ;
[ ” ” ; ¶ ] ; [ “@$space$@” ; ” ” ] )
)
De Substitutes zijn nodig om de overtollige ¶ tekens te verwijderen. De functie is redelijk eenvoudig gebeleven, maar als je hem gaat hergebruiken, dan wordt al snel het veel code die je in de gaten moet houden en dat zie je bij de XOR-functie hieronder.
De volgende vraag die je over dezelfde waardelijsten zou kunnen stellen is alleen de waarden die exclusief in Lijst A óf in Lijst B staan.
Dat is de Exclusive OR (XOR) , Xor_Values ( Lijst A ; Lijst B ) :
| Lijst A | Lijst B | Xor_Values |
|---|---|---|
| Nummer1 Nummer2 Nummer3 nummer1 nummer2 nummer4 G P T |
J F G Nummer4 P T nummer2 |
Nummer1 Nummer3 J F |
De XOR-functie maakt gebruik van dezelfde berekening als die voor de NOT-functie, maar dan vanuit 2 gezichtspunten en wordt dus ietsje uitgebreider en complexer:
Let ( [
nota = Let ( [
lista = FieldA ;
listb = If ( lista ≠ “” ; FieldB ; “” ) ;
blist = UniqueValues ( listb & ¶ ; 0 ; “” )
] ;
Substitute (
TrimAll (
Substitute (
UniqueValues ( List ( blist ; lista ) ; 0 ; “” ) ;
[ blist ; “” ] ; [ ” ” ; “@$space$@” ] ; [ ¶ ; ” ” ] ) ; 1 ; 1 ) ;
[ ” ” ; ¶ ] ; [ “@$space$@” ; ” ” ] )
) ;
notb = Let ( [
lista = FieldB ;
listb = If ( lista ≠ “” ; FieldA ; “” ) ;
blist = UniqueValues ( listb & ¶ ; 0 ; “” )
] ;
Substitute (
TrimAll (
Substitute (
UniqueValues ( List ( blist ; lista ) ; 0 ; “” ) ;
[ blist ; “” ] ; [ ” ” ; “@$space$@” ] ; [ ¶ ; ” ” ] ) ; 1 ; 1 ) ;
[ ” ” ; ¶ ] ; [ “@$space$@” ; ” ” ] )
)
] ;
Case (
FieldA = “” ; UniqueValues ( FieldB ; 0 ; “” ) ;
FieldB = “” ; UniqueValues ( FieldA ; 0 ; “” ) ;
List ( nota ; notb ) )
)
De eerste nota van de list-functie is 100% identiek aan de not-functie, zoals je zelf kan zien en de tweede notb bijna, behalve dat de afwerkvolgorde in de tweede cell precies is omgedraaid. Wat er overblijft zijn 2 lijsten met alleen de unieke waarden die alleen ofwel in Lijst A, danwel in Lijst B worden gebruikt en door deze weer met de List-functie te concateneren, blijft de XOR-lijst over (mits beide lijsten waarden bevatten!)
En de laatste is alle waarden tonen die beide lijsten voorkomen, de AND-functie
And_Values ( Lijst A ; Lijst B ) :
| Lijst A | Lijst B | And_Values |
|---|---|---|
| Nummer1 Nummer2 Nummer3 nummer1 nummer2 nummer4 G P T |
J F G Nummer4 P T nummer2 |
Nummer2 nummer4 G P T |
Het valt je misschien op de Lijst A dominant is over Lijst B, dat is vrijwel nooit belangrijk, maar soms is het handig dat je het weet. Deze formule gebruikt dezelfde formule als de XOR, maar wordt op zijn beurt ook weer ietsje uitgebreid:
Let ( [
clist = Let ( [
nota = Let ( [
lista = FieldA ;
listb = If ( lista ≠ “” ; FieldB ; “” ) ;
blist = UniqueValues ( listb & ¶ ; 0 ; “” )
] ;
Substitute (
TrimAll (
Substitute (
UniqueValues ( List ( blist ; lista ) ; 0 ; “” ) ;
[ blist ; “” ] ; [ ” ” ; “@$space$@” ] ; [ ¶ ; ” ” ] ) ; 1 ; 1 ) ;
[ ” ” ; ¶ ] ; [ “@$space$@” ; ” ” ] )
) ;
notb = Let ( [
lista = FieldB ;
listb = If ( lista ≠ “” ; FieldA ; “” ) ;
blist = UniqueValues ( listb & ¶ ; 0 ; “” )
] ;
Substitute (
TrimAll (
Substitute (
UniqueValues ( List ( blist ; lista ) ; 0 ; “” ) ;
[ blist ; “” ] ; [ ” ” ; “@$space$@” ] ; [ ¶ ; ” ” ] ) ; 1 ; 1 ) ;
[ ” ” ; ¶ ] ; [ “@$space$@” ; ” ” ] )
)
] ;
Case (
FieldA = “” ; UniqueValues ( FieldB ; 0 ; “” ) ;
FieldB = “” ; UniqueValues ( FieldA ; 0 ; “” ) ;
List ( nota ; notb ) )
) ;
andlist = Substitute ( UniqueValues ( clist ; 0 ; xorlist ) ; [ xorlist & ¶ ; “” ] )
] ;
Case (
xorlist = “” ; UniqueValues ( FieldA ; 0 ; “” ) ;
FieldA = “” or FieldB = “” or xorlist = andlist ; “” ;
clist ≠ xorlist ; andlist )
)
Eerst worden de XorValues bepaald en vervolgens worden de unieke waarden uit de optelling van Lijst A en Lijst B gehaald en van dat resultaat worden de eerder bepaalde XorValues weer weggelaten. Wat er overblijft zijn de waarden die zowel in lijst A als Lijst B staan 😉
Zo dat zijn dan de lijsten met de resultaten die je zou krijgen als je daar de bewerkingen op los zou laten en moet ik zeggen dat ik nog steeds een hekel heb aan “Niet Autonome” custom-functies. Er wordt in de berekeningen uiteraard wél gebruik gemaakt van de UniqueValues custom-function van enkele geleden, maar daar was het ook allemaal om te doen.
Helemaal met custom-functions (is dit toch gemakkelijker)
De voorgaande formules zijn best uitgebreid zoals je ziet en het is een stuk handiger om ze wél als custom-function te implementeren. Die CF’s maken dan wél gebruik van UniqueValues en ook van elkaar en zo zijn ze dan niet autonoom. Het gebruik in berekeningen wordt dan natuurlijk heel eenvoudig: NotValues ( Lijst A ; Lijst B ), XorValues ( Lijst A ; Lijst B ) resp. AndValues ( Lijst A ; Lijst B )
Even verderop kunnen jullie het voorbeeld met CF’s downloaden, maar ik zal eerst de formules even tonen:
De NOT-functie is meest complexe en lijkt veel op de gewone berekening:
Let ( [
listb = If ( lista ≠ “” ; listb ) ;
blist = UniqueValues ( listb & ¶ ; 0 ; “” )
] ;
Substitute (
TrimAll (
Substitute (
UniqueValues ( List ( blist ; lista ) ; 0 ; “” ) ;
[ blist ; “” ] ; [ ” ” ; “@$space$@” ] ; [ ¶ ; ” ” ] ) ; 1 ; 1 ) ;
[ ” ” ; ¶ ] ; [ “@$space$@” ; ” ” ] )
)
De XOR-functie maakt gebruik van de NOT-functie en wordt daardoor een stuk eenvoudiger dan de gewone berekening:
Let ( [
nota = NotValues ( lista ; listb ) ;
notb = NotValues ( listb ; lista )
] ;
Case (
lista = “” ; UniqueValues ( listb ; 0 ; “” ) ;
listb = “” ; UniqueValues ( lista ; 0 ; “” ) ;
List ( nota ; notb ) )
)
De AND-functie op zijn beurt maakt weer gebruik van de XOR-functie en dat wordt dan:
Let ( [
clist = List ( lista ; listb ) ;
xorlist = XorValues ( lista ; listb ) ;
andlist = Substitute ( UniqueValues ( clist ; 0 ; xorlist ) ; [ xorlist & ¶ ; “” ] )
] ;
Case (
xorlist = “” ; UniqueValues ( lista ; 0 ; “” ) ;
lista = “” or listb = “” or xorlist = andlist ; “” ;
List ( lista ; listb ) ≠ xorlist ; andlist )
)
De formuleringen worden zo een stuk eenvoudiger en dus is het uiteindelijk toch handiger dit in CF’s te gebruiken en niet met gewone berekeningen te maken. Het kan en als een noodoplossing kan het dienst doen, maar wanneer je veel van dit soort berekeningen doet, dan wordt je DB snel een zooitje. De beide voorbeeldbestanden (een met de calculaties en de andere met alleen Custom Functions) staan hier klaar om te worden gedownload