Be nice if there was a built in function in FileMaker for this, but there isn't. Any calculation that does this would need to loop through the list of values in Field A or B order to generate the list you want in field C. This can be done with recursion--either in a custom function (Requires FileMaker Advanced to add to your file) or in a recursive calculation field.
Do you have FileMaker Advanced?
And you may be able to do a web search for a custom function where this has already been designed as I seem to remember references to such a function.
yes i am using advanced ; but not in a very advanced way
i'll try a web search
If you can't find it and another user doesn't post a link to such a source, let me know here and I'll take a swing at crafting such a custom function. It shouldn't be too difficult to create.
found two wich will remove a single value
both on briandunning.com
AddRemoveListItem and RemoveValue
as yet i haven't a clue how to go the next step
ok my brain can't cope with recursive functions yet
and i just need it to work for now
so i stole the substitute line from David Heads function(coppied bellow)
// AddRemoveListItem (theList; value)
// Author: David Head, uLearnIT
// theList: standard return separated list
// value: text
// this function will add an item to a list if it does not already exist; otherwise the item is removed from the list
novalue = IsEmpty ( FilterValues ( theList ; value ) );
listminusvalue = Substitute( "¶¶" & theList & "¶¶"; [¶ & value & ¶ ; ¶ ] ; ["¶¶¶"; ""] ; ["¶¶"; ""] ) ;
listplusvalue = List ( theList; value )
If ( novalue ; listplusvalue ; listminusvalue )
i have just used it in a simple calculation - which will work so long as the absent list (see below) does not exceed 20 (5 would be unlikely)
not elegant or clever - i would be interested to know if it can be done properly
"¶¶" & calc_list_ON_plus_EXTRA & "¶¶" ;
[¶ & GetValue ( calc_list_ABSENT ; 1 ) & ¶ ;¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 2 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 3) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 4 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 5 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 6 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 7) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 8) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 9 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 10 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 11 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 12 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 13 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 14 ) & ¶ ;¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 15 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 16 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ;17 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 18 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ; 19 ) & ¶ ; ¶ ];
[¶ & GetValue ( calc_list_ABSENT ;20) & ¶ ; ¶ ] ;
["¶¶¶"; ""] ; ["¶¶"; ""]
This custom function works for me:
// RemoveSubList ( SourceList ; SubList )
// Return a list of all values in SourceList that are not also listed in SubList
// SourceList: A return separated list of values
// SubList: a Return separated list of values to be removed from SourceList
Let ( [ ListItem = GetValue ( SubList ; 1 ) ;
SubListRemanent = RightValues ( SubList ; ValueCount ( SubList ) - 1 ) ;
FilteredSourceLista = Substitute ( ¶ & SourceList & ¶ ; ¶ & ListItem & ¶ ; ¶ ) ;
FilteredSourceListB = Middle ( FilteredSourceLista ; 2 ; Length ( FilteredSourceLista ) -2 )
Case ( IsEmpty ( SubList ) ; SourceList ;
RemoveSubList ( FilteredSourceListB ; SubListRemanent )
) // case
) // Let
@ Phil, can you point me in the direction of some good reading on recursive calc fields? They sound very usefull...
busy morning just had time to put it in - it works
if i sound amazed it's 'cos i am
broke it down - got it - but doing one from scratch 'll be a wee while
i'm with resteraunt charlie - need to do some reading - and thinking
and really many thanks - solved the problem - and several good lessons at the same time
Recursive expressions can really have a "smoke and mirrors feel" to them. I can still remember how strained my brain felt trying to understand the concept when I first saw a college instructor post one on the chalk board (yeah those dark colored rectangles where they wrote on them with hard white sticks called "chalk" back before we had whiteboards and other stuff. ;-) )
I wasn't "comfortable" with them, until I sat down with pencil and paper and "paper executed" one through a few recursive loops and then was able to write my own recursive function calls.
It may help to keep in the back of your mind that recursion works by breaking downt he execution into a series of steps where the value at each step is not computed, but rather is "stacked" in memory until the point is reached where the answer is trivial. The trivial answer is returned and then each "stacked" step is unstacked and evaluated one step at a time until the final answer is reached.
This "stacking of recursive calls", is in fact the biggest limitation of recursive calculations. Theres a maximum limit to how many such steps can be stacked up before you run out of memory. That's usually not a major issue with FileMaker Pro, but FileMaker Go users are finding that some recursive functions fail pretty quickly on an iPad or iPhone.
So they can behave like a script that goes through records and makes a return separated list of values? I've been trying to figure out a way to have a function that behaves like Select Distinct in SQL, where I could have a function return a list of distinct values in a related table.
When you say limits in the stack, are we talking about 10 steps? 20? 100? 1000?
I'm trying to break down the function you posted. I follow it through the let, but when you get to the case, I get lost. What does the RemoveSubList function do? Or what am I missing?
Note that RemoveSubList is the name of this function. It "calls itself", but passes on simplified values to this next call in the parameters. Eventually, the parameters passed get so simple that there's an obvious answer. In this case, the parameters are simplified by removing the left (first) value of Sublist from SourceList, then the modified SourceList and all remaining values of Sublist get passed on to the new function call. When there are no more values to remove from the sourceList, the sublist is empty and recursion starts to "unstack" back to the original call--returning a copy of the source list that has all items from SubList removed From SourceList.
This won't work for a UniqueItems query as it removes all instances from the list.
This is a type of query that can be much easier to do in SQL than in FileMaker. It can be done however. The best approach depends on what you want to do with this query. Are you trying to find a set of records with duplicates filtered out or are you trying to produce a list of values from the table where duplicates have been filtered out?
Let me know what you want and I'll explain further. (And you might consider starting a new thread for this if you want to increase the chance that other contributers will chime in with their suggestions.)
So the way to loop the calculation is to call the calculation within the calculation, and the equivelent of the "exit loop if" in a script happens when the result has reached it's most simplified form?
Any ideas on the limits of how many layers it can stack? FMP and FM Go numbers would really help.
*I added another thread for the Select Distinct question: http://forums.filemaker.com/posts/8b9da309b4
I really have no idea as to the limits and it can vary from system to system depending on the complexity of the function (Are you "stacking" large complex parameters each time?) and your available RAM. I only know that you can hit this limit much more quickly in Go due to the hardware limitations.