Brian Dunning's FileMaker Custom Functions

InsertValue ( list ; value ; index )

Returns a list with value 'value' inserted at position 'index'

  Average rating: 3.8 (35 votes) Log in to vote

Theo Ros   Theo Ros
n/a
n/a

Share on Facebook Share on Twitter

  Sample input:
InsertValue ( "John¶Paul¶Ringo" ; "George" ; 3 )
  Sample output:
"John¶Paul¶George¶Ringo"

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Returns a list with value 'value' inserted at position 'index'

Syntax: InsertValue ( list ; value ; index )

In: list - the return delimited list to search
value - the stringvalue to insert
index - the position (1-...) where the new value should go
If left empty or an integer value greater than the
total size of the list, the new value will be added
at the very end of the list. If <= 1 the value will
added at the beginning of the list.

Return type: Text (a list with leading and trailing returns removed)

InsertValue ( "John¶Paul¶Ringo" ; "George" ; 3 ) --> "John¶Paul¶George¶Ringo"

Theo Ros

 

Comments

Erik   Erik, New York
Jul 22, 2013
using the leftWords function to "Trim" line returns means this will also THROW AWAY any "non-word" characters ( like colons, etc ) from the inserted value
 
Justin Close   Justin Close, Salem, OR
Aug 3, 2017
(Did my comment not go through? Trying again...)

A long time later...In addition to Erik's comments, another non-word character that gets dropped is the negative/minus sign. This makes it not work if you are dealing with a list of numbers that includes negative values.

I have modified the above custom function so that it will fix both Erik's and my issues. This has NOT BEEN HEAVILY TESTED for various corner-cases. It worked for my numbering needs, and I ASSUME it will work for Erik's cases.

I have utilized another custom function in this re-write: "Trim4()", which removes stray whitespace characters from the start and back end of strings. I use it to remove the trailing char(13)/"¶" character that FM's various "leftvalues()" style functions append. (Grrr...) I have also reformatted things a bit and renamed the parameters to fit my style.
-------------------

Syntax: InsertValue ( aList ; aVal ; atIndex )

------------------
Let ( [
~val = Trim4 ( LeftValues ( aVal ; 999999999 ) )
; ~List = Trim4 ( LeftValues ( aList ; 999999999 ) )
; ~valCount = ValueCount ( ~List )
] ;
Case ( ~ValCount = 0 ; ~val ;
IsEmpty ( atIndex ) or atIndex > ~valCount ; ~List & "¶" & ~val
; atIndex < 2 ; ~val & "¶" & ~List
; Trim4 ( LeftValues (
Trim4 ( LeftValues ( ~List ; atIndex - 1 ) & ~val & "¶" & MiddleValues ( ~List ; atIndex ; 999999999 ) )
; 999999999
) )
)
)
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.