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

Combining two calc fields


vjpettes

Recommended Posts

WINXP FMP 5.5V1

 

I have two seperate calc fiels that, by themselves work the way they were intended. Recently, I have the need to have the work of both calcs work as one calc in one field. For example, the ones calc looks for all the Street, Drive, Place, Avenue, Circle extentions in an address and parses it out leaving everything else. Works great! The second parses out the North, South, East and West designations and abreviations in an address so that all that's left is the number and street. How do I combine the two so that they work as one calc? I know I should combine all the test using the "and" operator or the "or" operator and the results the same way, how? The brain is not clicking rignt now. Any suggestions? Here are the two calcs.

VINCENT

 

CALC#1:

Case(

 

PatternCount( Location of Offense, " dr"), Left( Location of Offense,

Length(Location of Offense) - (Length(Location of Offense) - Position(

Location of Offense, " dr", 1, 1) + 1)),

 

PatternCount( Location of Offense, " st"), Left( Location of Offense,

Length(Location of Offense) - (Length(Location of Offense) - Position(

Location of Offense, " st", 1, 1) + 1)),

 

PatternCount( Location of Offense, " ln"), Left( Location of Offense,

Length(Location of Offense) - (Length(Location of Offense) - Position(

Location of Offense, " ln", 1, 1) + 1)),

 

PatternCount( Location of Offense, " ave"), Left( Location of Offense,

Length(Location of Offense) - (Length(Location of Offense) - Position(

Location of Offense, " ave", 1, 1) + 1)),

 

PatternCount( Location of Offense, " cir"), Left( Location of Offense,

Length(Location of Offense) - (Length(Location of Offense) - Position(

Location of Offense, " cir", 1, 1) + 1)),

 

PatternCount( Location of Offense, " pl"), Left( Location of Offense,

Length(Location of Offense) - (Length(Location of Offense) - Position(

Location of Offense, " pl", 1, 1) + 1)),

 

PatternCount( Location of Offense, " ct"), Left( Location of Offense,

Length(Location of Offense) - (Length(Location of Offense) - Position(

Location of Offense, " ct", 1, 1) + 1)),

 

PatternCount( Location of Offense, " rd"), Left( Location of Offense,

Length(Location of Offense) - (Length(Location of Offense) - Position(

Location of Offense, " rd", 1, 1) + 1)),

 

PatternCount( Location of Offense, " arc"), Left( Location of Offense,

Length(Location of Offense) - (Length(Location of Offense) - Position(

Location of Offense, " arc", 1, 1) + 1)),

 

PatternCount( Location of Offense, " #"), Left( Location of Offense,

Length(Location of Offense) - (Length(Location of Offense) - Position(

Location of Offense, " #", 1, 1) + 1)),

 

Location of Offense)

------------------------------------------------------------------------------

 

CALC#2:

Case(

 

PatternCount( Location of Offense, " N ") = 1,

 

LeftWords( Location of Offense, 1) & " " & Right( Location of Offense,

Length(Location of Offense) - Position( Location of Offense, " N ", 1,

1) - 2),

 

PatternCount( Location of Offense, " S ") = 1,

 

LeftWords( Location of Offense, 1) & " " & Right( Location of Offense,

Length(Location of Offense) - Position( Location of Offense, " S ", 1,

1) - 2),

 

PatternCount( Location of Offense, " E ") = 1,

 

LeftWords( Location of Offense, 1) & " " & Right( Location of Offense,

Length(Location of Offense) - Position( Location of Offense, " E ", 1,

1) - 2),

 

PatternCount( Location of Offense, " W ") = 1,

 

LeftWords( Location of Offense, 1) & " " & Right( Location of Offense,

Length(Location of Offense) - Position( Location of Offense, " W ", 1,

1) - 2),

 

Location of Offense)

Link to comment
Share on other sites

Tried your suggestion, didn't work. I think it has to do with each test. Maybe by the time it gets to the test for the second calc it has quit. I will think on it again and see what I come up with.

Link to comment
Share on other sites

Hi Vincent ...

 

Here's something a bit simpler than your calcs, but it only handles your exact request.

 

Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute

(Substitute,

 

Location of Offense,

 

" E ", " "), " W ", " "), " N ", " "), " S ", " "), " dr", ""), " ave", ""),

" st", ""), " ln", ""), " cir", ""), " ct", ""), " tr", ""), " rd", ""), " arc", ""), " pl", ""), " hwy", ""), " wy", "")

 

But, there is a problem with this calc and also with the way your calcs are structured. Look at this partial list of abbreviations you're trying to remove, and some possible street names your calc and mine (above) will truncate:

 

dr: "Dresher st" becomes "esher"

st: "Stevens st" becomes "evens"

arc and cir: "Arctic Circle dr" becomes "tic cle" -- hey, stop that! smile.gif

pl: "Pleasant pl" becomes "easant"

E: "E st" becomes ""

N: "N st" becomes ""

 

There are many more possibilities, but do you see where I'm going with this? The calculation has to be smarter about where it looks for the abbreviations. Since it's likely that " rd", " ave", etc. will be the last word in the field, this might work better:

 

Case(

 

RightWords(Location of Offense, 1) = "dr" or

RightWords(Location of Offense, 1) = "ave" or

RightWords(Location of Offense, 1) = "st" or

RightWords(Location of Offense, 1) = "ln" or

RightWords(Location of Offense, 1) = "cir" or

RightWords(Location of Offense, 1) = "ct" or

RightWords(Location of Offense, 1) = "tr" or

RightWords(Location of Offense, 1) = "rd" or

RightWords(Location of Offense, 1) = "arc" or

RightWords(Location of Offense, 1) = "pl" or

RightWords(Location of Offense, 1) = "hwy" or

RightWords(Location of Offense, 1) = "wy",

 

Trim(LeftWords(Location of Offense, WordCount(Location of Offense) - 1)),

 

Location of Offense

 

)

 

Of course, it won't cover every contingency.

 

In terms of " N ", " S ", " E " and " W ", I'm still trying to conjure an elegant way to discriminate between when the letters mean a direction and when they mean a street name: "A st", "B st", "C st" ... " E st" ... " N st" ... " S st" ... " W st". How to deal with that? How about "666 East E St." hmmmmm ... a devilish problem. :rolleyes:

 

It's late and my brain is fried, but I hope I at least gave you some ideas. Maybe someone else can improve on this.

 

Good luck!

Link to comment
Share on other sites

Hi Vincent

 

Can you give some actual examples of the range of street names you are dealing with? I am assuming the following:

 

14 Smith St E

12 E Smith St

10 Smith St

18 Smith E St

 

And combinations with Rd, Ln, Ave, etc. Where can the E appear in relation to the street name and type? We must also assume that some street names are two words.

 

Is this assumption correct? If so, then we can work to provide the best calculation. wink.gif Jim has started the ball rolling.

 

Awaiting your response.

Link to comment
Share on other sites

Here are some examples of addresses I deal with:

 

120 E Madrid ave

1560 Palo Verde Ave

1401 E Court St

1500 N Mesquite St

2490 N Main St

230 Three Crosses Ave #39

1023 W Espanola Cir

1002 Luna Pl

Link to comment
Share on other sites

OK, so from your original post, what you want is to eliminate parts so that "all that's left is the number and street". So each of these examples becomes:

 

120 E Madrid ave ---------120 Madrid

1560 Palo Verde Ave ------1560 Palo Verde

1401 E Court St-----------1401 Court

1500 N Mesquite St--------1500 Mesquite

2490 N Main St------------2490 Main

230 Three Crosses Ave #39--230 Three Crosses

1023 W Espanola Cir--------1023 Espanola

1002 Luna Pl--------------1002 Luna

 

So here is the formula you need (drum roll please):

 

Substitute(Substitute(Substitute(Substitute(

Case(

Position(Address & " ", " ave ", 1, 1),

Left(Address, Position(Address & " ", " ave ", 1, 1) - 1),

Position(Address & " ", " st ", 1, 1),

Left(Address, Position(Address & " ", " st ", 1, 1) - 1),

Position(Address & " ", " cir ", 1, 1),

Left(Address, Position(Address & " ", " cir ", 1, 1) - 1),

Position(Address & " ", " ct ", 1, 1),

Left(Address, Position(Address & " ", " ct ", 1, 1) - 1),

Position(Address & " ", " dr ", 1, 1),

Left(Address, Position(Address & " ", " dr ", 1, 1) - 1),

Position(Address & " ", " ln ", 1, 1),

Left(Address, Position(Address & " ", " ln ", 1, 1) - 1),

Position(Address & " ", " pl ", 1, 1),

Left(Address, Position(Address & " ", " pl ", 1, 1) - 1),

Position(Address & " ", " rd ", 1, 1),

Left(Address, Position(Address & " ", " rd ", 1, 1) - 1),

Position(Address & " ", " arc ", 1, 1),

Left(Address, Position(Address & " ", " arc ", 1, 1) - 1),

Address)

, " N ", " "), " E ", " "), " W ", " "), " S ", " ")

 

How does it work?

Each two lines of the Case statement test for the existence of one of the street types with leading and trailing spaces. The trick here is that they are checked for in the Address with a trailing space added. Think about that. If the street type exists, it gets all characters to the left of it.

 

The default result for the Case statement is simply the address in the event that there is no known street type.

 

The whole thing is then wrapped in a nested Substitute statement to parse out the N, E, W, S.

 

This calculation accounts for such tricky addresses as "123 E Average St" and "124 E St".

 

Hope this is what you are after. If not, or if it breaks in some circumstance, email me direct. smile.gif

Link to comment
Share on other sites

Way to go, David!

 

I especially like the way you isolated the alphabetical street names by first removing the street type, which places the street name at the end of the string without a trailing space. Then replacing the directions " N ", " E ", " S ", " W ", which would still have leading and trailing spaces, with " ". Nice job!

Link to comment
Share on other sites

*VERY* impressive David,

 

I had read this post and decided to leave, convinced it was, as Jim assumed, some kind of impossible mission.

 

Congrats Mr Phelps... cool.gif

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use