Sign in to follow this  
Followers 0

Creating a new field from a street address

14 posts in this topic

Posted

Hi all.

Can someone help with this issue.

I have a field called "home address" it includes the house number as well as the street name. It looks like this:

"123 King Athur St"

I need to have the number in its own seperate field. I have created a field "Street No" which should then contain the number "123" from the above example, and the field "home address" should then only contain "King Athur St"

Thanks in advance.

Share this post


Link to post
Share on other sites

Posted

Perhaps you could use =

LeftWords ( home address ; 1 )




for the number, and =




RightWords ( home address ; WordCount ( home address ) -1 )

for the rest. I am saying "perhaps" because while it fits your example, it may not fit other cases, e.g. "123/B King Athur St".

Share this post


Link to post
Share on other sites

Posted

Thanks so much for the info. Where do I insert these calculation scripts? I am still quite new at all this. Do i put the calculation - LeftWords ( home address ; 1 ) in the "Street No" field and the calculation - RightWords ( home address ; WordCount ( home address ) -1 ) in the "home address" field?

Do I need to do the one calculation before the other? Will this effectivley permanently seperate the number from the street name?

Thanks

Share this post


Link to post
Share on other sites

Posted

I'd suggest you define two new calculations fields, using the above formulae. Close the Manage Database window, then go back and change both fields to type Text. Check your results and if everything is OK, you can delete the original home address field.

1 person likes this

Share this post


Link to post
Share on other sites

Posted

This is a double post. Did you examine the file that I attached here http://fmforums.com/...545#entry380545

Please do not double post your questions.

BTW, I actually attached to different files to that post the 1st one was for phone numbers and the 2nd one was for addresses.

Lee

Share this post


Link to post
Share on other sites

Posted

Ah! Thanks. So I think I was starting to go the right way. BUT. Is it possible to do a search on the same address field, but that will result in the correct alpha numeric sequence? After all, this is actually what I am trying to achieve.

Is there a script that I can use to sort the address correctly? I can get the result in Table View - sort options. if i first search by street with the field type set as "text", then once the search has completed i then change the field type to number. this gets the result i am looking at, but I cant make it work in filemaker go. Im thinking that as I am able to achieve this manually, I should be able to do it via a script??

Thanks

Share this post


Link to post
Share on other sites

Posted

I am afraid I don't understand what you mean by "sort the address correctly". IMHO, the address field - with or without the street number - should be a text field and sorted alphabetically. Why is this not working for you?

Share this post


Link to post
Share on other sites

Posted

So when I sort the address field, FM will sort the addresses alphabeticaly correctly, but the house number is not correctly sorted.

example:

I search in the address field for streets named "Any Street" FM returns a list of all records.

Then I need to sort the streets numericaly. In other words, 1,2,3,4,5 etc. HOWEVER, if I have a house number of, lets say, 21, then the sorted list will return, 1, 2, 21, 3,4,5 etc instead of 1,2,3,4,5,21. Theonly way to sort the number out is to change the field to "number" in list view. Does this make sense?

Share this post


Link to post
Share on other sites

Posted

It does. I suppose you want to sort by the street name first, then by the house number (after you have separated them as explained earlier).

Share this post


Link to post
Share on other sites

Posted

How can I write a script to do this so that it will work on FM Go for Ipad? Any suggestions?

Share this post


Link to post
Share on other sites

Posted

I don't know much about FM Go. Doesn't sort work the same way there?

Share this post


Link to post
Share on other sites

Posted

No. Not from what I can see. There appears no way to change the field type. As far as I can assertain, a script would be the only way. I dont know enough about scripts to help myself.

Share this post


Link to post
Share on other sites

Posted

You cannot modify your files in go. Your reports and stuff needs to be created in FileMaker. Take a look at the FileMaker go topic, and follow the link that I posted. http://fmforums.com/...post__p__349835

Once you have your file ready and doing your listings or reports the way you want them, you should prepare a file to be put on your mobile unit in FileMaker go.

Share this post


Link to post
Share on other sites

Posted

There appears no way to change the field type.

I don't see why you would need to change the field type on a regular basis. I thought you were asking how to sort the records. You can do this manually, or set up a script that does it for you.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0