Jump to content

  •  

Photo

Creating a new field from a street address


  • Please log in to reply
13 replies to this topic

#1 guyzo  member

guyzo
  • Members
  • 17 posts
  • FM Application:11
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 16h 46m 23s

Posted 03 March 2012 - 09:08 AM

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.
  • 0

#2 comment  consultant

comment
  • Members
  • 24,273 posts
  • Time Online: 333d 21h 30m 22s

Posted 03 March 2012 - 09:26 AM

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".
  • 0

#3 guyzo  member

guyzo
  • Members
  • 17 posts
  • FM Application:11
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 16h 46m 23s

Posted 03 March 2012 - 12:03 PM

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
  • 0

#4 comment  consultant

comment
  • Members
  • 24,273 posts
  • Time Online: 333d 21h 30m 22s

Posted 03 March 2012 - 12:50 PM

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

#5 Lee Smith  I like the v13 layout tools

Lee Smith
  • Staff
  • 10,192 posts
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch
  • Platform:Mac OS X Mavericks
  • Skill Level:Expert
  • Membership:TechNet
  • Time Online: 234d 4h 39m 31s

Posted 03 March 2012 - 01:29 PM

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
  • 0

#6 guyzo  member

guyzo
  • Members
  • 17 posts
  • FM Application:11
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 16h 46m 23s

Posted 03 March 2012 - 02:00 PM

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
  • 0

#7 comment  consultant

comment
  • Members
  • 24,273 posts
  • Time Online: 333d 21h 30m 22s

Posted 03 March 2012 - 02:43 PM

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?
  • 0

#8 guyzo  member

guyzo
  • Members
  • 17 posts
  • FM Application:11
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 16h 46m 23s

Posted 04 March 2012 - 07:23 AM

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?
  • 0

#9 comment  consultant

comment
  • Members
  • 24,273 posts
  • Time Online: 333d 21h 30m 22s

Posted 04 March 2012 - 09:53 AM

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).
  • 0

#10 guyzo  member

guyzo
  • Members
  • 17 posts
  • FM Application:11
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 16h 46m 23s

Posted 04 March 2012 - 10:13 AM

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

#11 comment  consultant

comment
  • Members
  • 24,273 posts
  • Time Online: 333d 21h 30m 22s

Posted 04 March 2012 - 10:20 AM

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

#12 guyzo  member

guyzo
  • Members
  • 17 posts
  • FM Application:11
  • Platform:Windows 7
  • Skill Level:Beginner
  • Time Online: 16h 46m 23s

Posted 04 March 2012 - 12:24 PM

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.
  • 0

#13 Lee Smith  I like the v13 layout tools

Lee Smith
  • Staff
  • 10,192 posts
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch
  • Platform:Mac OS X Mavericks
  • Skill Level:Expert
  • Membership:TechNet
  • Time Online: 234d 4h 39m 31s

Posted 04 March 2012 - 12:32 PM

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.
  • 0

#14 comment  consultant

comment
  • Members
  • 24,273 posts
  • Time Online: 333d 21h 30m 22s

Posted 04 March 2012 - 01:54 PM

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.
  • 0




FMForum Advertisers