Many ways there,
forget about words, count position of keyword.
Left ( text ; Position ( text ; " vs " ; 1 ; 1 ) - 1 )
Replace ( text ; 1 ; Position ( text ; " vs " ; 1 ; 1 ) + 3 ; "" )
How about this:
val = CONTENDERS;
V= position( val; " vs "; 1; 1 );
L = left( val; V - 1);
R = right( val; length( val ) - (V + 3 ) )
List( L; R )
Substitute( CONTENDERS; " vs "; ¶ )
2 of 2 people found this helpful
Substitute ( Contenders ; " vs " ; ¶ )
Thank for your responses. I tried the second method first and it worked perfectly. If I run into any difficulties, I'll look at the response from user19572. But since this one works initially, I'll try to incorporate it into my solution.
1 of 1 people found this helpful
I wrote 2 calculations for get each values separately, not thought of getting list of 2 values
Use the Position() function to work out where the "v" of "vs" is in the string.
I need to add something to this solution. What would I do if my original field was instead, a list? For instance, a list like this:
Boo Ray vs Legends
Lightning vs Sun Shine
California Rain vs Colorado Snow
Texas vs Missouri
...would need to produce another list with results like this:
Retain the original list, create another one.
My substitute function example will do that.
Thanks. I'll try it again. I tried it once, but I couldn't get it to work the same. It worked perfectly over a single field, but I couldn't get it work on a list.
It does remove the "vs" but in the new list, it just puts the words on a separate line. It doesn't make a separate line item.
If list A - which has the names as one line item has 5 lines, then list B should have 10 separate line items. But using the substitute function as is, there are still just 5 line items. I don't know if that's a clear explanation or not.
You said that you had a list. In FileMaker, that normally refers to a single field or variable with multiple values separated by returns. If that's what you have, this method works.
If that's not you mean by a list, please describe what you mean by "list" in more detail.
How about this? How would I take the data in this field: Boo Ray vs Liberty
Leave Boo Ray as a record, then create a new record with the word Liberty.
So one record with Boo Ray vs Liberty would become two separate records:
1 of 1 people found this helpful
Creating records requires a script, not just a calculation.
Set Variable [ $List ; Substitute ( YourTable::GlobalFIeld ; " vs " ; ¶ ) ]
Set Field [ YourTable::Field ; GetValue ( $List ; 1 ) ]
Set Field [YourTable::Field ; GetValue ( $List ; 2 ) ]
There are a number of unknown details that could require additional scripting here. For example, you might be creating records in a related table. If so, you also need to set your new record's foreign key field to the parent record's primary key and that might require copying the primary key of the current record on a layout based on the parent table to a variable so that it can be used to set foreign key fields in the new related records after changing layouts to a layout based on the related table. Or you might use method known as MagicKey (You can web search this term) to create the new related records.
Perfect. Thank you! I can make this work with what I am trying to do.