Find in a value list

[Vinnyg]

Perhaps I'm having a brain fart, but I'm stumped... I want to isolate "n" number of characters at the beginning of each line in a value list field and put the extracted values into another list field via calculation. The number of values is dynamic based on the user adding items to the list.

An example:

Value List................ Desired result

0001|Chocolate...... 0001

0002|Christmas...... 0002

, etc.

Of course, if I use Left, Middle, etc. I only get the first line of the list.

 

P.S. - I'm forced to go no higher than FileMaker 17, so the While function is off the table.

[comment]

First I would ask why do you have a value list that combines two values? IOW, why do you put your needles in a haystack, then ask how to find them?

Now, to answer your question as asked, i.e.:

1 hour ago, Vinnyg said:

I want to isolate "n" number of characters at the beginning of each lin

the answer would have to be that this requires recursion, therefore either the While() function or a custom function - provided you have the Advanced version to install a custom function. Or an interim repeating calculation field. Or a looping script instead of a calculation.

However, if you look ar the problem from a different point of view, you may be able to do simp[y:

Filter ( YourField ; "0123456789¶" )

provided that the list does not contain any digits in the right-hand side parts of the values.

 

[Vinnyg]

I agree that it

s a bad idea to have a value list combines 2 values, but I inherited it and am trying to fix it.  Your filter idea is good, but I stupidly didn't point out that there could be numbers in the second value, i.e., chocolate2.  Filter would return 00012 in that case. I think I'm going to have to go the script route...

[comment]

8 hours ago, Vinnyg said:

there could be numbers in the second value

Well, you could use:

Evaluate (
"\"" &
Substitute ( YourField ; [ "|" ; "\¶\" /*" ] ; [ ¶ ; "*/ & \"" ] )
& "*/"
)

instead - if you don't mind unreadable code, that is.

 

8 hours ago, Vinnyg said:

I think I'm going to have to go the script route...

Do you not have any Advanced version?

I have only now noticed that your profile says Version 18. So you could use that to install a custom function, even if users will be using lesser, non-advanced versions. 

 

Edited May 16, 2021 by comment

[comment]

Another option, slightly less convoluted:

Let ( [
values = Substitute ( YourField ; "|" ; ¶ ) ;
filter = Substitute ( YourField ; "|" ; "¶-" ) 
] ;
FilterValues ( values ; filter ) 
)

 

 

Edited May 16, 2021 by comment

[Vinnyg]

Beautiful... I thought FilterValues was the way to go, but couldn't figure out the logic.  Thanks greatly.