Extracting data from one field into another

[philipcaplan]

I have the following Calculation field called "extract_card_type" in a FMPro16 database:

Let ( [
lineStart = Position ( PASTED_INPUT_to_use ; "¶Card Type: " ; 1 ; 1 ) ;
lineEnd = Position ( PASTED_INPUT_to_use ; ¶ ; lineStart + 1 ; 1 ) ;
start = lineEnd + 1 ;
end = Position ( PASTED_INPUT_to_use ; "¶Organisation name: " ; start ; 1 )
] ;
Middle ( PASTED_INPUT_to_use ; start ; end - start )
)

and the text field 'PASTED_INPUT_to_use' is the following:

incoming order
Card Type: ORG WITH BORDER
Organisation name: org-name
Organisation type: org-type

I expected the field "extract_card_type" to contain the value "ORG WITH BORDER" but it is empty!!

Could somebody please tell me how to fix it so it does.

Thanks in anticipation.

 

[comment]

1 hour ago, philipcaplan said:

I expected the field "extract_card_type" to contain the value "ORG WITH BORDER"

I wouldn't. Just check your numbers:

lineStart = 15
lineEnd = 42
start = 43 (!)
end = 0 (!)

Note also that the position of the first ¶ after "¶Card Type: " is the same as the position of "¶Organisation name: ". I don't see why you need to calculate both. Can Card Type contain more than one line?

 

[philipcaplan]

Hi comment

So could you give me the correct calculation to get the result I want.

And yes, "Card Type" can contain more than one line.

[comment]

If Card Type can contain more than one line, then you can do:

Let ( [
lineStart = Position ( PASTED_INPUT_to_use ; "¶Card Type: " ; 1 ; 1 ) ;
start = lineStart + 12 ;
end = Position ( PASTED_INPUT_to_use ; "¶Organisation name: " ; start ; 1 )
] ;
Middle ( PASTED_INPUT_to_use ; start ; end - start )
)

Note that the number 12 is the length of the "¶Card Type: " search string.

 

[philipcaplan]

Thanks for this, comment.  It is working perfectly for me.

I also have another request. I have a Calculation field (based on what you gave me) as follows:

Substitute (

Let ( [
lineStart = Position ( PASTED_INPUT_to_use ; "¶Message: " ; 1 ; 1 ) ;
start = lineStart + Length("¶Message: ");
end = Position ( PASTED_INPUT_to_use ; "¶Font: " ; start ; 1 )
] ;
Middle ( PASTED_INPUT_to_use ; start ; end - start )
)
; ¶ ; "<p>" )

This nicely gives me a result containing the multiple lines of content of "Message" with each line-ending replaced by <p>

What I would appreciate is a variation of that Calculation which ensures that if the result ends with a <p> that is removed.

[comment]

I don't see how the result can end with a <p> - unless you have two carriage returns before "Font: ". Such possibility raises  other questions, for example: can there be more than two? And also: can there be more than one consecutive carriage returns within the message - and if so, should they be preserved?

If the only issue is a possible single trailing carriage return in the extract message,  you could do:

Let ( [
lineStart = Position ( PASTED_INPUT_to_use ; "¶Message: " ; 1 ; 1 ) ;
start = lineStart + Length ( "¶Message: " ) ;
end = Position ( PASTED_INPUT_to_use ; "¶Font: " ; start ; 1 ) ;
message = Middle ( PASTED_INPUT_to_use ; start ; end - start )
] ;
Substitute ( message & Char (31) ; [ ¶ & Char (31) ; "" ] ;  [ Char (31) ; "" ] ; [ ¶ ; "<p>" ] )
)

 

[philipcaplan]

Thank you, I will try that later.