Jump to content

Extracting data from one field into another


Recommended Posts

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.

 

Link to post
Share on other sites
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?

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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>" ] )
)

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.