1 2 Previous Next 20 Replies Latest reply on Sep 25, 2016 10:11 AM by dtcgnet

Script Question

How do I write a script to combine comma delimited fields without duplicating words into a list?

I currently have 10 fields that have comma delimited lists that I would like to combine into one field in list form.

• 1. Re: Script Question

I'm not 100% certain I understand what you want, but let's take a look at this.

You can use a Set Field script step and use the following calculation (substitute your own field names):

Let ( [

field1List = Substitute ( table::field1 ; "," ; "¶" ) ;

field2List = Substitute ( table::field2 ; "," ; "¶" ) ;

.

.

.

field10List = Substitute ( table::field10 ; "," ; "¶" ) ;

allFieldList = List ( field1List ; field2List ; field3List ... field10List ) ;

allFieldList = cfDedupe ( allFieldList ; "" )

] ;

Substitute ( allFieldList ; "¶" ; "," )

)

Now, you're probably asking, "What the heck is cfDedupe?"

It's a Custom Function I use for removing the duplicates from a list. It's reproduced below:

/*

Author: Tom Seidler, 2012 [based on Peter Roots CF: http://www.briandunning.com/cf/1109]

Method: tail end recursion and use of substitute means this can handle lists of (theoretically) infinite size with as many as 50k variants in the list.

An essential aspect: ["¶" & _this & "¶" ;"¶¶" & _this & "¶¶"]

This allows the guaranteed removal of all occurences of _this, even if they are next to each other, in a list...

Also borrows Ray Cologon's Trim4 methodology to clear all empty values.

input = return-delimited list of values

output = initially empty

*/

Let (

[

_this = GetValue ( input ; 1 ) ;

_null = If ( Left ( input ; 1 ) = "¶" ; 1 ) ;

_valueList = If ( not _null ; Substitute ( "¶" & input & "¶" ; ["¶" & _this & "¶" ;"¶¶" & _this & "¶¶"]; ["¶" & _this & "¶" ; ""] ; ["¶¶";"¶"] ) ; input ) ;

_l1r = If ( Left ( _valueList ; 1 ) = "¶" ; 1 ) ;

_r1r = If ( Right ( _valueList ; 1 ) = "¶" ; 1 )

] ;

Case ( Length ( _valueList ) ;

cfDedupe ( Middle ( _valueList ; 1+_l1r ; Length (  _valueList ) - ( _l1r + _r1r ) ) ; List ( output ; _this ) ) ;

output )

)

If you don't have FileMaker Advanced (and hence can't use the CF), then let us know and we'll use another method.

• 2. Re: Script Question

Thanks so much!  I do not have FileMaker Advanced.  Only pro.

My fields are:

Percussion 1

Percussion 2

.

.

.

Percussion 10

and I want to combine those to a field called Instrumentation.  All of the fields are comma delimited and I do not want duplicates in the combined Instrumentation field.

Thanks!

• 3. Re: Script Question

Hi,

using Mike's method of creating the list, here's another way to build a de-duped list from that.

• 4. Re: Script Question

Sorry - there's a bug in there.  The line 7 calculation should be:

not IsEmpty(\$value) and PatternCount("¶" & \$newList  & "¶"; "¶" & \$value  & "¶") = 0

• 5. Re: Script Question

David's method will work fine. Another way to parse the list is to use FilterValues to compare what you have against what you've already added to the destination.

Set Variable [ \$count ; Value: 1 ]

Set Variable [ \$originalList ; Value: {my stuff above} ]

Loop

Set Variable [ \$curVal ; Value: GetValue ( \$originalList ; \$count ) ]

If [ IsEmpty ( FilterValues ; \$newList ; \$curVal ) ]

Set Variable [ \$newList ; List ( \$newList ; \$curVal ) ]

End If

Set Variable [ \$count ; \$count + 1 ]

Exit Loop If [ \$count > ValueCount ( \$originalList ) ]

End Loop

Set Field [ Instrumentation ; \$newList ]

• 6. Re: Script Question

Thanks!  Very helpful, yet I really am a beginner at this.  I'm not sure how to combine both of your scripts.

• 7. Re: Script Question

to populate Mike's \$originalList make it equal to:

Let (

[

field1List = Substitute ( table::field1 ; "," ; "¶" ) ;

field2List = Substitute ( table::field2 ; "," ; "¶" ) ;

.

.

.

field10List = Substitute ( table::field10 ; "," ; "¶" ) ;

allFieldList = List ( field1List ; field2List ; field3List ... field10List )

];

Substitute ( allFieldList ; "¶" ; "," )

)

• 8. Re: Script Question

To populate my \$rawList, use the above calculation, but substitute Substitute ( allFieldList ; "¶" ; "," ) with just allFieldList

• 9. Re: Script Question

Oops! That won't work since we're now parsing in a script instead of using the CF. It should be:

Let (

[

field1List = Substitute ( table::field1 ; "," ; "¶" ) ;

field2List = Substitute ( table::field2 ; "," ; "¶" ) ;

.

.

.

field10List = Substitute ( table::field10 ; "," ; "¶" ) ;

allFieldList = List ( field1List ; field2List ; field3List ... field10List )

];

allFieldList

)

(Because if you use the Substitute, you'll lose the list.)

Then, last line of my script becomes:

Set Field [ Instrumentation ; Substitute ( \$newList ; "¶" ; "," ) ]

• 10. Re: Script Question

I see.  I missed that.

• 11. Re: Script Question

The attached file uses only native FM functionality, and it works without any scripts. There is a calculated field similar to what Mike mentioned that creates one big list of all of the items in your 10 percussion fields. There is also an unstored calculated field which uses the following formula:

Substitute ( ValueListItems ( "" ; "PercussionValues" ) ; ¶ ; ", " )

PercussionValues is a value list based on a self join Table Occurrence based on PK = PK, and the value list uses only related values.

The resulting list of values is auto-alphabetized (not sure if you want that), and also not case sensitive ("sticks" and "Sticks" will be seen as the same thing).

Give it a shot.

• 12. Re: Script Question

Thanks!  This seems to be the best solution, however, I am unable to get the Deduped list to show up.  I think that I did everything right, but it is just blank.  It does work on your example.

• 13. Re: Script Question

It will update on record commits. If you incorporate it into your own solution, make sure that the deduped list field is an unstored calculation.

• 14. Re: Script Question

I did incorporate into my solution and it is an unstored calculation, but still not working. Not sure what I need to do.

1 2 Previous Next