Greets, all:

I'm trying to use the Position command in a script to parse text (after an Excel file import) from a cell (named F1) that's typically formatted like this:

Sequence: 1; Item ID: 8536/1, Item Desc: Brandecker, CARD, Clinical presentation and evaluation of ischemic heart disease

...where  I want to:

• Extract the Item ID number (which can be up to five characters long) and push it into a field named IDNumber.
• Extract The Item ID's question number which is the number immediately to the right of the forward slash ("/") symbol. (The question number can be up to four digits long.) That'll be shoved into the field, QuestionNumber.
• All the text to the right of the phrase, "Item Desc:" ...which will be shoved into the ItemDesc field and it, too, can be any length.

Obviously, I'll need a configured Position command for each of the three different script lines. I tried using this one to extract the ItemID:

SetField [FEEDBACK::IDNumber; Left ( FEEDBACK::F1; Position ( FEEDBACK::F1 ; "/" ; 1 ; 1 ) - 1 ) ]

...but it's pulling everything left of the "/" symbol instead of just the ID number. In the Position example online ( https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help/position.html ), it pulls the first name from the selected field so at first I thought the -1 was parsing just the first word to the left of the "/", but obviously I'm mistaken.

I could use some serious enlightenment. : )

Cheers,

Rich

 

To extract the item ID:

Let ( [
start = Position ( FEEDBACK::F1 ;  "Item ID: " ; 1 ; 1 ) + 9 ;
end = Position ( FEEDBACK::F1 ; "/" ; start ; 1 )
] ;
Middle ( FEEDBACK::F1 ; start ; end - start )
)

To extract the question number:

Let ( [
start = Position ( FEEDBACK::F1 ;  "/" ; 1 ; 1 ) + 1 ;
end = Position ( FEEDBACK::F1 ; "," ; start ; 1 )
] ;
Middle ( FEEDBACK::F1 ; start ; end - start )
)

To extract the item description:

Right ( FEEDBACK::F1 ; Length ( F1 ) - Position ( FEEDBACK::F1 ;  "Item Desc: " ; 1 ; 1 ) - 10  )

--
P.S. Are you sure the separator between Item ID and Item Desc is a comma, not a semicolon? 

Yes, it's a comma. ...and MANY thanks (again) for your help!

I was playing with the calculation some more before reading your post and of course the calculation I came up was far more convoluted than it needed to be, e.g., for ItemID I came up:

Case ( LeftWords ( F1__lxt ; 1 ) = "Sequence" ; Trim ( Right ( GetValue ( Substitute ( F1__lxt ; "/" ; ¶ ) ; 1 ) ; 5 ) ) ; "" )

Your way is MUCH more elegant. : D

It could have been even more elegant if your data provider had been consistent and used a semicolon to separate the key/value pairs. Because then we could substitute it with a carriage return to get:

Sequence: 1
Item ID: 8536/1
Item Desc: Brandecker, CARD, Clinical presentation and evaluation of ischemic heart disease

and process each value individually by getting the substring after ": ".

Come to think of it, you could set a variable to =

Substitute ( FEEDBACK::F1  ;
[ "Sequence: " ; "" ] ; 
[ "; Item ID: " ; ¶ ] ; 
[ ", Item Desc: " ; ¶ ] 
)

and then populate the fields using the GetValue() function.