Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Isolationg part of an URL


basicus
 Share

Recommended Posts

Hi there everybody.

 

I am trying to figure out a way to isolate the main part of an url. I have tried to use all the examples I could find, and almost got it working with a calculation filed and using Left. The problem was that it did not work with domain names containing a number.

 

I'll try to illustrate under what I am trying to achieve:

 

In the following url I need to get out and put into a new field the domain part. I have highlighted it in red:

 

http://www.biogime.no/component/page,shop.browse/category_id,4/option,com_virtuemart/Itemid,100/

 

Everything else I need to drop, so I end up with http://www.biogime.no as the only content of the new field.

 

Any ideas?

Link to comment
Share on other sites

Middle(Text, Start, Size)

Position(Text, SearchString, Start, Occurrence):

 

 

Middle(

Table::URLfield,

Position(Table::URLfield, "/", 1, 2)+1,

Position(Table::URLfield, "/", 1, 3)-Position(Table::URLfield, "/", 1, 2)-1

)

Link to comment
Share on other sites

  • 1 month later...
Middle(Text, Start, Size)

Position(Text, SearchString, Start, Occurrence):

 

 

Middle(

Table::URLfield,

Position(Table::URLfield, "/", 1, 2)+1,

Position(Table::URLfield, "/", 1, 3)-Position(Table::URLfield, "/", 1, 2)-1

)

Hi FileMaker Guru,

 

I just read your post and I have a similar problem. I have an address field that looks like this: C/O Family Trust*123 Main Street*City CA\

 

I'd like to break out this single field into 4 fields:

address1

address2

city

state

 

Can you help me? I've been trying for hours and I have 38,000 records to fix. Much appreciated.

Link to comment
Share on other sites

C/O Family Trust*123 Main Street*City CA\

 

 

Middle(Text, Start, Size)

Position(Text, SearchString, Start, Occurrence);

live it, learn it, love it, daydream about it ;)

 

 

Set Field [TableName::address1, Middle(TableName::UglyField, 1, Position(TableName::UglyField, "*", 1, 1)-1)]

 

Set Field [TableName::address2, Middle(TableName::UglyField, Position(TableName::UglyField, "*", 1, 1)+1, Position(TableName::UglyField, "*", 1, 2) - Position(TableName, "*", 1, 1)-1)]

 

Set Field [TableName::city, Middle(TableName::UglyField; Position(TableName::UglyField; "*"; 1; 2)+1; Position(TableName::UglyField; " "; 1; PatternCount(TableName::UglyField; " ")) -Position(TableName::UglyField; "*"; 1; 2) - 1)]

 

Set Field[TableName::state, Middle(TableName::UglyField, Position(TableName::UglyField, " ", 1, PatternCount(TableName::UglyField, " ")+1, Length(TableName::UglyField))]

 

 

EDIT: formula for city corrected after error pointed out

Link to comment
Share on other sites

Thanks for the help. No way I could've figured that out. The script for address1 and address2 worked perfect. However, I can't seem to get the 3rd script (City script) to work at all. What am I doing wrong?

Link to comment
Share on other sites

Not you, me :o

 

Typo. Parenthesis in the wrong place. (that'll teach me to freehand them!)

 

Make that:

 

Middle(

TableName::UglyField;

Position(TableName::UglyField; "*"; 1; 2)+1;

Position(TableName::UglyField; " "; 1; PatternCount(TableName::UglyField; " ")) - Position(TableName::UglyField; "*"; 1; 2) - 1

)

 

 

What that long blue clause is doing (the size part of the equation) is get the position of the LAST space in UglyField (because city is differentiated from state by a space not an asterisk) — Position in UglyField of space (" ") starting at beginning, the PatternCount'th occurrence that you come to (i.e., the last one; "as many as there are" always means "the last one");

 

and then subtract from it the starting point of city, which is the position in UglyField of an asterisk, starting from the beginning, the 2nd occurrence, ...minus one (because you don't want either the space or the asterisk so, for length of city, you're one character shorter than the distance between the two delimiters, see?).

Link to comment
Share on other sites

FWIW, here are some alternate calculations. The logic is to turn the data into a value list and then use GetValue function to extract data.

 

address1 =
GetValue ( Substitute ( UglyField ; "*" ; "¶" ) ; 1 )

address2 =
GetValue ( Substitute ( UglyField ; "*" ; "¶" ) ; 2 )

city = 
LeftWords( 
   GetValue ( Substitute ( UglyField ; "*" ; "¶" ) ; 3 ); 
   WordCount ( GetValue ( Substitute ( UglyField ; "*" ; "¶" ) ; 3 ) ) - 1 
)

state =
RightWords(
 GetValue ( Substitute ( UglyField ; "*" ; "¶" ) ; 3 ); 
 1)

 

See what you make of that ;)

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use