find text string position and add XML tags relative to this

[DRoberts]

Hi,

I've got myself into a knot trying to solve a problem of adding XML tag pairs into a text string:

Intial text:

The plant is 30cm (12in) tall with dark green leaves 12-16cm (5-6in) long.

Required result:

The plant is 30cm (12in) tall with dark green leaves 12-16cm (5-6in) long

I think I need to find the position of "cm" and work back until I find the first preceeding space then inset after that space.

I can change the "in)" with a substitution for "in) but was wondering how I might do this with a caluculation for future reference i.e. find the position of "in)" and work forward until I find a space then inset before the space. - the second bit isn't necessary for the above problem....just trying to learn how it might be done

Can somone please help as I just can't seem to get it to work.

kind regards

David

Edited October 30, 2010 by Guest

[fseipel]

I'd suggest using Position function to find the beginning and end of each substring. Once substring locations are known, the results string can be built up from the positions. So you would use

Set field result =

Let ([$thestring=; $Start_pos_height=Position ( $thestring; " " ; Position ( $thestring; "cm" ; 1 ; 1) ; -1 );$End_pos_height=Position ( $thestring; ")" ; $Start_pos_height ; 1 ); $Start_pos_length=Position ( $thestring; " " ; Position ( $thestring; "cm" ; 1 ; 2) ; -1 );$End_pos_length=Position ( $thestring; ")" ; $Start_pos_length ; 1 )];Left ( $thestring; $Start_pos_height) & "" & Middle ( $thestring; $Start_pos_height + Length(" "); $End_pos_height-$Start_pos_height) & "" & Middle ( $thestring; $End_pos_height + Length(" ") ; $Start_pos_length - $End_pos_height) & "" & Middle ( $thestring; $Start_pos_length + Length(" "); $End_pos_length - $Start_pos_length ) & "" & Right($thestring;Length($thestring)- $end_pos_length ))

and substitute your field for

Use of LET and local variable should make the code a bit more reusable if there are other XML substitutions required. If you upgrade to Filemaker Advanced you can use the custom functions feature which might prove useful for making this more compact.

[comment]

Since I didn't understand fseipel's suggestion, I'll add my own:

Let ( [

end = Position ( text ; "in)" ; 1 ; $i ) ;

endSpace = Position ( text ; " " ; end ; 1 ) ;



start = Position ( text ; "cm " ; 1 ; $i ) ;

startSpace = Position ( text ; " " ; start ; -1 ) 

] ;

Case ( start and end ;

Replace ( 

Replace ( text ; endSpace ; 0 ; "" ) ;

startSpace ; 1 ; " " ) ;

text

)

)

Note:

This requires a recursive process, and (in the absence of Advanced) must be scripted in a loop. The $i variable is a counter that needs to be raised by 1 in each iteration, until you run out of dimension instances in the text.

[fseipel]

Sorry for any confusion, the OP's profile indicated they weren't running FMPA; so my code was not meant for a custom function. An example is attached -- always good to test what I posted anyway to confirm it worked as indicated.

Add_XML_Tags.zip

[Lee Smith]

Hi fseipel,

I checked your calculation, however I did change vars so that that it read easier (i.e. so that it didn't run off of the page). I also formatted it so that you can read it. Also, I didn't see the reason for the $ field naming?

Anyway, your code worked perfectly for the example given.

Let ([

	ss = DRoberts::text ; 

	s1 = Position ( ss; " " ; Position ( ss; "cm" ; 1 ; 1) ; -1 );

	e1 = Position ( ss; ")" ; 	s1 ; 1 ); 

	s2 = Position ( ss; " " ; Position ( ss; "cm" ; 1 ; 2) ; -1 );

	e2 = Position ( ss; ")" ; s2 ; 1 )];

	

Left( ss ; s1 ) & "" & Middle( ss; s1 + Length( " " ); e1 - s1 ) & "" & Middle( ss; e1 

+ Length( " " ) ; s2 - e1 ) & "" & Middle( ss ; s2 + Length( " " ) ; e2 - s2 ) & "" 

& Right( ss ; Length( ss ) - e2 )

	

)

Edited October 30, 2010 by Guest

[fseipel]

Yes, the line breaks definitely make that easier to read. And the $ prefix is unnecessary as you note; I only find it useful in debugging, wherein the local variable has scope across the script, so I can display it with a dialog or in debugger, whereas without the prefix, the variable scope is only within the Let.

If the text string always follows this format, it would also be possible to execute three successive substitutions:

Set Field A::Text_with_XML = Substitute ( A::Text_with_no_XML ; "The plant is " ; "The plant is " )

Set Field A::Text_with_XML = Substitute (A::Text_with_XML ; ") " ; ") " )

Set Field A::Text_with_XML = Substitute (A::Text_with_XML ; "leaves "; "leaves " )

This assumes all plants have leaves and all strings start with "The plant is", so it's a bit more brittle.

[comment]

:goodpost:

I think it's worth noting that the formula assumes there are EXACTLY TWO "dimensions" in the text.

[comment]

Why use a script, if there's no recursion? A calculation field could handle this without need for user action.

[comment]

A note on a more general level:

I wonder how you got yourself into this position - because frankly speaking, Filemaker is not the best tool for this task. A text editor with grep capability would be a better choice, in terms of both the data format and the required processing.

In a database, one would expect fields for:

• PlantHeight

• LeavesColor

• LeavesLength

etc.

with a calculation field putting all of this together into a sentence.

I also wonder what's the purpose of this tagging. It looks like you plan to export this to another application. If so, it might be better to export as XML and use a custom XSLT stylesheet to do the required transformations.

[LaRetta]

I don't believe script is required, nor a custom function (although I have nothing against them). I DO believe it can 'recurse' no matter the *number of entries *if* the word count for the 30cm (12in) portion remains consistent as two words (although it can (12-16in) or 5-6cm etc).

Calculation field name is cDisplay and the calc must be created and saved before entering the formula)

Let ( [

$i = $i + 1 ;

l = Substitute ( text ; ") " ; ¶ ) ;

string = GetValue ( l ; $i ) ;

words = WordCount ( string )

] ;

LeftWords ( string ; words - 2 ) & " " & RightWords ( string ; 2 ) & ") "

&

Case ( $i < ValueCount ( l ) - 1 ; cDisplay ; Let ( $i = "" ; [color:blue]GetValue ( l ; ValueCount ( l ) )

) ) )

... not necessarily pretty, I guess, but I wanted to try it anyway.

* within computer's memory limits, I suppose or how long a User is willing to wait. I have not tested this yet with large-data text fields.

UPDATE: And the text before, middle and after can be varied - no need to hard-code "plants" etc. Also, the blue could have been RightValues( l ; 1 ) instead ... :blush2:

[comment]

I don't believe script is required, nor a custom function

Well, your suggestion IS a custom function - in the sense that it is recursive. Personally, I tend to be rather suspicious towards this hack (despite being in some small part responsible for it) - esp. the part where the formula refers to its own field.

This is an undocumented behavior, and I don't think it has been explored enough in order to recommend it, certainly not to beginners.

[DRoberts]

Hi Comment,

The problem is the result of a poor inital XML structure - or more accurately NO structure.

I've imported XML from an InDesign document which has had some tagging applied - but as you point out it is in need of much more granularity.

The end point is to extract the contents of these XML tags into different holding fields and swap the values for publiction in UK and US markets - also remove the imperial measurements for European publication.

I will eventually be coding these as , , etc in the body text and holding the metric and imperial values for each value set in different fields (thus allowing a simple substitution to occur for the UK / US markets). The final output is styled XML for use in InDesign.

Note: the example I gave was concerning plant size and leaf size, however the data I'm working with uses such measurements thoughout the text for different things (depth of planting, distance from buildings, etc) - I'd prefer to code per "type" of measurement, but time and cost prohibit this level of data handling - just holding the different measurements is enough.

Thanks for all your ideas - I'll be spending the rest of a wet London Sunday getting my head around this!

David

[comment]

This might be a bit ambitious, but why not do it all at once?

Processor.zip

[DRoberts]

Hi Comment,

This is great - the problem you identified was that I cannot assume the number of strings that might appear within a field - infact this varies from zero to over eight or nine....

This approach gives me the starting point I require

Many thanks

David