ITA_PositionIndexValues ( textList ; searchList ; searchValue ; occurrence )

Returns list of values found in searchList at the same index as the position of SearchValue in textList.

Average rating: 3.0 (2 votes) Log in to vote

Thomas Myers - Show more from this author
Innovative Technology Applications
http://itainc.com

Sample input:

ITA_PositionIndexValues(
List( "TimeOff"; "Bonus"; "TimeOff") ;
List("2018-1";100;"2018-2"; "2018-1");
"TimeOff";1)

Sample output:

2018-1
2018-1

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

/*
ITA_PositionIndexValues( textList; searchList; searchValue; occurrence)

Returns list of values found in searchList at the same index as the position of SearchValue in textList.

ITA_PositionIndexValues( List( adj::TypeName_t; List(adj::Quarter); "TimeOff";1)

An enhancement of https://www.briandunning.com/cf/1290
Bruce Robertson, Concise Design PositionValues v 1.0 5/25/2010

2018/04/04 Thomas Myers V1.0
*/

Let ([
	adjustedText = "¶" & Substitute( textList; "¶"; "¶¶" ) & "¶" ;
	thisValue = GetValue( searchValue; 1) ;
	target = "¶" & thisValue & "¶"  ;
	P1 = Position ( adjustedText ; target ; 1 ; occurrence );
	P2 = Position ( adjustedText ; target ; 1 ; occurrence +1 );
	SVCount = ValueCount( searchValue);
	chunk = Middle ( adjustedtext ; 2; P1  + Length ( thisValue )  ) ;
	result = Case( P1 ; ValueCount ( Substitute( chunk; "¶¶"; "¶" )))  
];

List( 
	GetValue( searchList; Result) ; 
	Case( P2 ; ITA_PositionIndexValues( textList; searchList; thisvalue; occurrence+1));
	Case( SVCount>1 ; ITA_PositionIndexValues( textList; searchList; MiddleValues( searchValue; 2; ValueCount( searchValue)); 1))
	)
)

I often have a relation that I have filtered on. Example all pay adjustments for THIS year. When I need a quick count of values without making new relations (and not using slow sql). I can use this function. So the list( adj:type) will return a value list of all the adjustments. But I need to count all the quarter 1 items.

ValueCount(FilterValues( ITA_PositionIndexValues ( List( adjThisYear::type) ; List( adjThisYear::yearQuarter) ; "TimeOff" ; 1 ); "2018-1"))

If I want to see dates I could just pass in List( adjThisYear::Date) instead of quarter.

Comments

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

ITA_PositionIndexValues ( textList ; searchList ; searchValue ; occurrence )

Comments

Support this website.

Support this website.