Sustitute, in text field, occurrences BETWEEN a range that occurs multiple times

[Cabinetman]

Well lets see if I can get this right ... I have been looking but can't seem to find quite what I'm looking for.

I need to substitute every occurrence of ":" that is between each occurrence of <Name>*</Name> but not between <DateTime>*</DateTime> within a text field where the occurrence of <Name>*</Name> may be once or may be 50 times.

Why would ":" be in there? It shouldn't be but someone did anyway....

Example:

<Name>John Jones: C/O Jack Jones</Name><DateTime>10-17-2011T15:30:25</DateTime>

<Name>Jackie Smith</Name><DateTime>10-17-2011T15:30:25</DateTime>

<Name>Tom Smith</Name><DateTime>10-17-2011T15:30:25</DateTime>

[Raybaudi]

Is there always a space after a : inside the name tag ?

If yes, then it could be very simple:

Substitute ( YourText ; ": " ; " " )

[Cabinetman]

Thanks but no..... it could be " :" or ":" or ": "

I found this:

SubstituteRange ( text; beginString; endString; replaceString )

but it replaces the whole string and I only need to replace whatever character I specify....

More like:

SubstituteRange ( text; beginString; endString; replaceThis ; replaceWithThis )

[comment]

Couldn't you solve this in the XML domain, where it's trivial - instead of laboring over a complex custom function in Filemaker?

[Cabinetman]

I guess I could ..... if I knew how.

But I don't have a clue where to start .... and hadn't even considered it based on that.

I'm sure there is an easier way to do this but.......

In short I'm doing a set field with a response from Amazon where I've used Groovy to get orders through MWS and added in the XML tags to the response.

I substitute out unwanted characters and then export the field contents to a *.txt for import.

EDIT : Ooops .... *.xml

[comment]

a response from Amazon where I've used Groovy to get orders through MWS

I am afraid that doesn't mean much to me. Is there any point where you have the data structured in records and fields (3 records x 2 fields, using your example)? Because then it would be equally trivial to substitute the colon in the Name field, while leaving the DateTime field as is.

added in the XML tags to the response.

That's also something that would be better handled by the built-in XML exporting engine (again, assuming you are exporting structured data - not just manipulating text strings).

[Cabinetman]

At no point before the import phase is there data structured in records and fields.

I am creating the XML tags around a response from a server as I import that response into a text field. I then export the text field contents and save as *.xml.

Next I import that xml file back to get the individual orders.

As I see things from my limited knowledge I can either use a calc. on the text field before exporting the text or as you mentioned figure out how to do it in my xsl during the import process...

[comment]

In what format is the response? Or better, in what formats can the response be?

[Cabinetman]

None? As best I can explain it....

Here is a snippet from my Java/Groovy edited to hopefully give an idea of what I'm working with.

"					<Name>"; if (shippingAddress.isSetName()) {shippingAddress.getName();  } "</Name>\n";

"			   	 <PurchaseDate>"; if (order.isSetPurchaseDate()) {order.getPurchaseDate();} "</PurchaseDate>\n";

 

Translates as

			   	

			   	 <Name>John Doe </Name>

			   	 <PurchaseDate>2011-10-24T15:30:000Z</PurchaseDate>

I've added all the tags in order to create some importable xml.

[comment]

Sorry, I don't follow. The response MUST have a format - and it's obviously structured, since you seem to be querying it. I think you could adjust your Groovy code so that (shippingAddress.getName) would become something like Substitute ( shippingAddress.getName ; ":" ; "" ) - in Groovy syntax, of course.

The point here is that at this stage the data is still individually addressable. The next stage this will be true is during the import - where the XSLT stylesheet extracts the contents of the <Name> element, and can modify it before passing it to Filemaker. And of course, you could have Filemaker itself modify the Name field immediately following the import.

Instead, you are trying to solve the problem while the data is no more than a bunch of meaningless text.

[Cabinetman]

Sorry, I don't follow. The response MUST have a format - and it's obviously structured, since you seem to be querying it.

What I meant is it doesn't come back as a .txt, .csv, .xml or any other FILE type. Line by line text is returned.

I think you could adjust your Groovy code so that (shippingAddress.getName) would become something like Substitute ( shippingAddress.getName ; ":" ; "" ) - in Groovy syntax, of course.

Next, that is probably true and I'm looking at that closer. It's not much different though in that I still have to come up with a sort or calculation to strip out the character.

Strip it as it comes into the text field in when it's already in there? I don't see how it really matters much EXCEPT that if I come up with the Groovy code I can set it to the specific lines desired... as noted below.

The point here is that at this stage the data is still individually addressable. The next stage this will be true is during the import - where the XSLT stylesheet extracts the contents of the <Name> element, and can modify it before passing it to Filemaker.

So I have to come up with something for my stylesheet that will do it. Again I don't see much difference EXCEPT that I can do it line by line.

And of course, you could have Filemaker itself modify the Name field immediately following the import.

Not if it wont import because of the character and you get an error referring back to that line in the file...

Instead, you are trying to solve the problem while the data is no more than a bunch of meaningless text.

Which I am doing for other characters already ... It just so happens that I can't or don't want to strip out all of these ':' in the file, just those in <Name> and a few others.

So OK .... It would be best to come up with either some Groovy or XSL.

So anyone ...... can you either:

1. Answer my original question?

Which I will modify slightly to ... SubstituteRangeEveryOccurence ( text; beginString; endString ; replaceThis ; replaceWithThis )

2. Point me to Groovy code?

or

3. Point me to some code for my stylesheet?

I realize the last 2 need to be in another forum but I'll worry about that if no one wants to answer the calculation part of the question.........

[comment]

It's not much different though in that I still have to come up with a sort or calculation to strip out the character.

It's very different, because here the calculation can work on the entire string, while it's still separated - before you throw the needle into the haystack.

So I have to come up with something for my stylesheet that will do it. Again I don't see much difference EXCEPT that I can do it line by line.

It's the same difference as before: the XSL stylesheet can easily parse out the contents of every <Name> element out of the XML source; Filemaker, OTOH, does not understand XML and doing this as a text manipulation is tedious.

Not if it wont import because of the character and you get an error referring back to that line in the file...

It won't import because of a colon in the text? Now that makes absolutely no sense. If that is the reason for your original request, then I'd suggest a re-examination of the entire process.

[Cabinetman]

Someone entered : Michael Brennan:C/O

My code replaced the forward slash resulting in this...

Brennan:C&#47O

I don't know ... and to be honest I really don't care ... as to why this throws an error when trying to import. That's not the point.

What I am looking to find out is how to strip out whatever characters I want. So either an answer to the original question or an answer as to how to do it another way.

I appreciate the suggestions because they cause me to look at other options that may/probably be better but they don't answer my question.

[comment]

to be honest I really don't care

To be honest, if you don't care, why should I? You are asking how to solve a problem that shouldn't exist in the first place AND you insist upon solving it the hardest way possible.

So either an answer to the original question or an answer as to how to do it another way.

So, to answer your original question: it requires writing a custom function. I don't think a "slight modification" of the SubstituteRange() function will do and personally I find the task uninteresting. And I already suggested three alternative ways.

---

P.S. Since you have brought up the SubstituteRange() function without posting a link, it's:

http://www.briandunning.com/cf/122

Edited October 26, 2011 by comment

[Cabinetman]

To be honest, if you don't care, why should I?

You shouldn't care either why the below throws an import error with an XML import.

Brennan:C&#47O

You are asking how to solve a problem that shouldn't exist in the first place

AND you insist upon solving it the hardest way possible.

You're missing the point.

I didn't ask how to fix an import error or why did I get this error.

I'm working on a principle and not an error.

The principle is I want to strip out ANY unwanted characters I specify within a given section.

So, to answer your original question: it requires writing a custom function.

I think we already established this in some of the previous posts as a possible method.

I don't think a "slight modification" of the SubstituteRange() function will do and personally I find the task uninteresting.

That is fine and I understand. I didn't ask you to write something just for me. There are more people out there and someone may already have something like this.

And I already suggested three alternative ways.

I guess I missed 1 as I only caught the 2 .... which I've already said were GOOD ideas and I was looking at them.

But they're just alternatives with little to no actual answer.

The Groovy was the best and I have a java code friend trying to help me with it ......

Just some FYI.

I'm sorry but I just get tired of chasing rabbit's all the time.

Umpteen posts ago all you had to say was.....

"As I see it you can do one of several things. I don't have a CF like that but maybe someone else does.

However if no one else has one why don't you re-post in in the XML forum and see if someone will help you edit your stylesheet.

Either that or try to find out how to edit the Groovy/Java code you're using to do it for you."

You know a lot and you help a lot of people.

I just can't ever seem to get a 'how to do something' or 'go here to see how' out of you....

So again ... thanks for the input.

I did listen.

But the fact that I'd like to still see if someone has a CF like this doesn't mean that I insist on solving it the hardest way possible .... or that you have to help with it.

[comment]

I don't mean to turn this into an argument. Suffice it to say that this is a public forum - and I thought explaining why I find your strategy flawed may be of interest to other members, if not to you.

Re XML, you didn't post your stylesheet, so it's difficult to be specific. I suggest you look at the XPath translate() function.

[Tom Elliott]

I believe the following Custom Function will do what you require but my testing has been minimal

//SubstituteBetweenTags ( theText ; startTag ; endTag ; searchString ; replaceString )

Let ([

startPos = Position ( theText ; startTag ; 1 ; 1 ) ;

startLength = Length ( startTag ) ;

endPos = Position ( theText ; endTag ; startPos ; 1 ) ;

searchPos = Position ( theText ; searchString ; startPos + startLength ; 1 ) ;

searchLength = Length ( searchString ) ;

replaceLength = Length ( searchString ) ;

theRest = Replace ( theText ; 1 ; endPos + Length ( endTag ) ; "" )

] ;

Case (

startPos = 0 or endPos = 0 ; theText ;

searchPos = 0 or searchPos + searchLength ≥ endPos ; Left ( theText ; endPos + Length ( endTag ) ) & SubstituteBetweenTags ( theRest ; startTag ; endTag ; searchString ; replaceString) ;

SubstituteBetweenTags ( Replace ( theText ; searchPos ; searchLength ; replaceString ) ; startTag ; endTag ; searchString ; replaceString )

)

)

[Cabinetman]

Thanks Tom! And thanks to LaRetta to for a similar private message.... with a file.

I was able to do some Java/groovy editing with the help of a friend who wrote 2 lines of code for me to add/alter. This fixes one front where I have a lot of control.

The CF allows me to work in some other areas that I don't especially want to publish... however I will post back any mod.'s and how well it does.

So thanks again ......