How to get all characters before x from a text with multiple occurrences

[JohnDing]

Hi

I'm trying to get the first numbers of a text string that has the following format numberxnumber,numberxnumber (5x2,10x6,20x30)..n times. The field can also be empty.

And i want to take the result and put it in another field and look like this:

5+10+20

I have something like this:

Left( text; Position ( text; "x" ; 1 ; 1 ) - 1 )

But that only works for the first number, and I'm not sure how to get it to iterate through rest of the text

Note. the calculation is done on a on object exit script.

Any help is appreciated.

Edited February 26, 2021 by JohnDing

[comment]

49 minutes ago, JohnDing said:

(5x2,10x6,20x30)..n times

Is n known in advance? If not, you are looking at a recursive calculation which requires either a custom function or the While() function. If you are using the regular version of FMP 16, neither of these is available to you.

49 minutes ago, JohnDing said:

the calculation is done on a on object exit script

If it's possible to do the calculation as part of the script, then you could do it using a looping script. The procedure itself could be quite simple: start by substituting the comma with a carriage return; then loop over the resulting list of values and extract the part before "x" from each one.

 

Edited February 26, 2021 by comment

[comment]

Actually, come to think of it, there is a non-recursive way:

Let ( [
values = Substitute ( text ; [ "," ; ¶ ] ; [ "x" ; "¶x" ] ) ;
filter = Substitute ( values ;  "¶x" ; "¶yx" ) ;
mutlipliers = FilterValues ( values ; filter)
] ;
Substitute ( mutlipliers & ¶ ;  [ "¶¶" ; "" ] ; [ ¶ ; "+" ] )
)

But it may take some time to understand how this works.

 

[JohnDing]

2 hours ago, comment said:

Is n known in advance?

The field is part of a portal view where the user can write the sequence they want so the n is known when they are done.

And my thinking was that when they are done writing in that field i would be able to take the calculation and fill in some other fields. (You helped me with that in my last post, only that time it was data from a PHp site where i had done some regex string splitting)

1 hour ago, comment said:

Actually, come to think of it, there is a non-recursive way:

Let ( [
values = Substitute ( text ; [ "," ; ¶ ] ; [ "x" ; "¶x" ] ) ;
filter = Substitute ( values ;  "¶x" ; "¶yx" ) ;
mutlipliers = FilterValues ( values ; filter)
] ;
Substitute ( mutlipliers & ¶ ;  [ "¶¶" ; "" ] ; [ ¶ ; "+" ] )
)

But it may take some time to understand how this works.

 

Tested it and it seems to work great. Thanks!

Would you be able explain (just a few lines) how it works?

Edited February 26, 2021 by JohnDing

[comment]

5 hours ago, JohnDing said:

Would you be able explain (just a few lines) how it works?

The best way to understand this is to look at the result of each step. Eventually, you have values =

5
x2
10
x6
20
x30

and filter = 

5
yx2
10
yx6
20
yx30

As you can see, the multiplier values in both lists are identical, while the multiplicands are different. Applying FilterValues() keeps only the identical values.

 

But of course the proper way to handle this would be to provide the user with separate fields for multiplier and multiplicand, and a portal to enter multiple multiplications. Then you will have structured data that does not require precarious text parsing.

 

[Joshua Willing Halpern]

My friend pointed me to this thread asking how I might solve this, and once I saw @comment's solution I just laughed. Brilliant idea.

Edited February 26, 2021 by Joshua Willing Halpern

[JohnDing]

On 2/26/2021 at 5:07 PM, comment said:

But of course the proper way to handle this would be to provide the user with separate fields for multiplier and multiplicand, and a portal to enter multiple multiplications. Then you will have structured data that does not require precarious text parsing.

I will keep that in mind if i need to expand functionality. Again thanks for the help.