Post

4 followers Follow
0
Avatar

Calculation to return a partial address

I am trying to create a calculation that will return part of an address, where item @PropertyAddress is the entire property address written as:
123 Main St, Los Angeles, CA 90001

I wrote the below function to return everything before the comma, but I keep getting an error message that says: Invalid value "123 Main St" (string): must be a number

I have tried to delete and recreate the calculation, but I can't seem to fix it. Any ideas? Thanks!

var name = @PropertyAddress
name.substr(0,name.indexOf(','));

James Bowie

Please sign in to leave a comment.

11 comments

0
Avatar

Hi James,
try with the regular expression like this

var reg = /[^,]+/
var a = @Nom.match(reg);
a[0]

the reg will return all strings between the commas and a[0] will return the first one

Hamid 0 votes
Comment actions Permalink
0
Avatar

Hi James, don't care about that error message. Just click done and save it.

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

thanks guys! Something very strange happened. I tried it in two apps, a "Leads" app and a "Closed Sale" app (which is there the leads convert to after they close). Rainer's suggestion of hitting done worked in the "Closed Sale" app, but not the "Leads" app - and from what I can tell, they are identical. So the Closed Sale calculation populates, but the Leads one does not, and I've tried refreshing and deleting and so forth...

James Bowie 0 votes
Comment actions Permalink
0
Avatar

Now that I have the Calculation field for the Name of the property finished in App1, I would like to carry this @Property Name (described as partial address "123 Main St") to all of the incoming related apps. So the title of each incoming related app would be:
App2 Title: @Property Name + @Transaction Type (where @Name references the calculation field we just made in App1)
App3 Title: @Property Name + "Construction"
...etc.

I really appreciate help on these, I only ask after I've spent a couple of hours trying to figure it out myself and get stuck. I tried using [All of] @Property Name and doesn't seem to work.

Here is a link to a screenshot for reference: http://bit.ly/1PLRkoU

James Bowie 0 votes
Comment actions Permalink
0
Avatar

Hi James,

1. for this question: "So the Closed Sale calculation populates, but the Leads one does not,"
What does the preview show? Did you check if the calculation field is number or text type?

2. for this question: "Now that I have the Calculation field for the Name of the property finished in App1, I would like to carry this @Property Name (described as partial address "123 Main St") to all of the incoming related apps. "
What doesn't work? What does the preview show?

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Is there a way to pull a partial string out of an address? The problem I see is that the addresses are not very consistent. I just want my calculation to populate with the street name only, but street name can very.

For Example:
123 Main Street, New York
456 East Cypress Street, Boston
789 Fern Gully Road, Dallas
654 E Golden Eagle Rd, Los Angeles
In each case, I would like to pull: Main, Cypress, Fern Gully & Golden Eagle for the calculation. Is that possible?
If not, is it possible to parse/grab anything more specific than what comes before the comma with the indexOf calc?
"name.substr(0,name.indexOf(','));"

James Bowie 0 votes
Comment actions Permalink
0
Avatar

Hi James,

you can try this, it's a step by step code and only tested for your examples:

var a = @address field;
var notWanted = ["E ","W ","N ","S ","East ","West ","North ","South "];
var b = a.substring(a.indexOf(" ")+1,a.indexOf(",")); //gets the string between number and comma
var c = b.substring(0, b.lastIndexOf(" "));  //gets the string before the last word like "Street", "Road" , "Rd."
var d = c.substring(0,c.indexOf(" ")+1); // gets the first word incl. the following blank
var result = notWanted.indexOf(d) > -1 ? c.replace(d,"") : c; // looks for the d-string in the array "notWanted" . If d is found it will be replaced from c, if not found show c
result

What kind of field is the address field?

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

I'm using the new multi value address/map field.

Thanks for your code Rainer, I will implement that step by step and see how it goes.

James Bowie 0 votes
Comment actions Permalink
0
Avatar

Then you've to change the code a bit:

var a = @Map.join();
var b = a.split(",")[0];
var notWanted = ["E ","W ","N ","S ","East ","West ","North ","South "];
var c = b.substring(b.indexOf(" ")+1, b.lastIndexOf(" "));
var d = c.substring(0,c.indexOf(" ")+1);
var result = notWanted.indexOf(d) > -1  ? c.replace(d,"") : c;
result

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

@Rainer - Thanks, this formula has worked for the last year, but I just ran into an issue where the actual street name was "East"

9356 S East Ave, Los Angeles

is there a way to amend the variable to filter out North, South, East, West unless it is followed by: ["St","Street","Ave","Avenue","Blvd,"Ct","Court","Ln,"Lane","Rd","Road","Pl","Place","Dr","Drive","Cir","Circle"]


var a = @Map.join();
var b = a.split(",")[0];
var notWanted = ["E ","W ","N ","S ","East ","West ","North ","South "];
var c = b.substring(b.indexOf(" ")+1, b.lastIndexOf(" "));
var d = c.substring(0,c.indexOf(" ")+1);
var result = notWanted.indexOf(d) > -1 ? c.replace(d,"") : c;

result

 

James Bowie 0 votes
Comment actions Permalink
-1
Avatar

Hi James,

did you test it? What was the result? Cause for me it works (s. screenshot).

Rainer Grabowski -1 votes
Comment actions Permalink