GetValue and LeftWords

[steigrafx]

I have an address field, formatted like this:

Monkey Time Productions, Inc.

455 Gerald Avenue

Bronx, NY 10452

I need to populate a field with the first line of the address (which I can do), plus add the city name from the third line (sometimes it's on the fourth line, but it's always on the last line), enclosed in parentheses only when my "Active" field is set to Yes. I need the result to look like this:

Monkey Time Productions, Inc. (Bronx)

I'm tearing my hair out trying to figure out how to combine LeftWords with GetValue to "strip" the city name out. Here's the calculation with the most success I've had thus far:

Case ( Active? = "Yes" ; GetValue ( Address ; 1 )) & " (" & GetValue ( Address ; 3 ) + LeftWords ( Address ; 1 ) & ")"

But that returns:

Monkey Time Productions, Inc. (10452)

Can someone tell me how to accomplish what seems to be so simple? I'm just not getting it.

Thanks,

Kevin

[Raybaudi]

Try:

Case(

Active? = "Yes" ;

GetValue ( Address ; 1 ) & " (" &  LeftWords ( GetValue ( Address ; ValueCount ( Address ) ) ; 1 ) & ")"

)

This will work if the city name is ONLY one word.

[steigrafx]

Thank you, Raybaudi. Your calculation does, in fact, work perfectly when the city is one word, and whether or not the city, state and ZIP are on the third or fourth or whatever line.

Since some of my city names do have multiple words, is there a way to fine-tune the calculation to "grab" everything before the comma so as to return the entire city name?

Thanks again,

Kevin

[comment]

If one can assume that there will always be a comma after the city name...

[steigrafx]

Yes, there is always a comma after the city name. I guarantee that, since I am the one who enters the addresses.

[Raybaudi]

So try:

Let([

lastValue = GetValue ( Address ; ValueCount ( Address ) ) ;

pos = Position ( lastValue ; "," ; 1 ; 1 ) - 1 ;

city = Middle ( lastValue ; 1 ; pos  )

];

Case(

Active? = "Yes" ; GetValue ( Address ; 1 ) & " (" & city & ")"

)

)

[comment]

... then =

Case (Active? = "Yes" ;



Let ( [

lastLine = GetValue ( Address ; ValueCount ( Address ) ) ;

city = Left ( lastLine ; Position ( lastLine ; "," ; 1 ; 1 ) - 1  )

] ;

GetValue ( Address ; 1 ) & " (" &  city & ")"

)



)

should work for you.

[steigrafx]

Thanks so much for your time, gentlemen. Both calculations are perfect. I'm fairly new at Filemaker and I'm learning bit by bit.

Have a great day, and thanks again.

Kevin

[Raybaudi]

The Comment's calculation looks a bit different, but does exactly the same thing.

Another way to learn more :)

[comment]

... I am the one who enters the addresses.

Why don't you enter them into separate fields for Name, Street, City, State and ZIP? It's always easier to put data together than to take it apart.

[steigrafx]

Normally, I would. This is a static list of services that we use, and its sole purpose is to print a mailing label. Only occasionally do I need to add a new one or mark one as inactive or change a telephone number or address.

And yes, I couldn't agree with you more, taking them apart can be quite difficult, especially when it's a person and there may or may not be a middle name or generation qualifier, etc.

I had to do just that a few years ago in a DOS-based database called Advanced DB-Master. The original programmer only had first and last name fields. The last name could be Smith or Smith Jr. or Johnson-Smith or van Johnson, and the first name could be Michael or Michael S. or Michael Samuel. It took me quite a while to write the calcs for that one.

As usual, thanks for all your help. I may be asking for it again!

Kevin

[steigrafx]

Although both calculations work perfectly, there's a twist. I use the calculated field in a looked-up value list in another table, and that looks fine. The drop-down looks like:

Alliance Feature Company (Bayshore)

Monkey Time Productions, Inc. (Bronx)

Turner Industries, Inc. (Queens)

etc.

But now when I select a service company from the drop-down, the field fills in with the same thing as is on the value list and not the entire address from the address field that the value list is set to look up. I've tried pointing the value list look-up to a different field and then back to the address field, but no matter what I do I can't get the looked-up value from address to appear as it did previously.

I also noticed that none of my looked up fields associated with the service company (contact name, telephone number, etc.) are updating. It's as if looked-up values are now completely turned off.

How is it that altering a calculation in one field affects the performance of another field?

Thanks,

Kevin

[comment]

When you select a value from a value list (e.g. by using a drop-down list), you are populating a field with the selected value. It seems like your value list should be defined to use the CompanyID field from the service companies tables - so that a relationship to the selected record is established. Once you have such a relationship, you can either display the full address from the related record, or look it up into another field in the local record.

[steigrafx]

That's exactly how it worked before. The drop-down value list showed the values from my field called "Nicknames" (the calculated field) and, when a value was selected, the field populated with the entire address from my "Address" field. That was when I was using this calculation in the Nicknames field:

Case (Active? = "Yes" ; GetValue ( Address ; 1 ))

When I change the calculation to either of the calculations suggested above, the field no longer populates with the address, nor do the looked-up fields, such as telephone number, update. Everything stops working. But when I change the Nicknames calculation back to the original one, everything works again.

Is there something in the "new" calculations that would affect the operation of the value list look-up?

Thanks,

Kevin

[comment]

1. Do you have a field defined as a lookup?

2. What exactly is the relationship between the tables (the table where you are doing the selection and the table where the service companies are)?

[steigrafx]

I believe everything is in order since it works perfectly with the old calculation -- both of the new calculations seem to stop all look-ups from working.

I've attached a copy of the database for your perusal. But no laughing, I'm new at this.

Thanks again,

Kevin

Transcript Requests.zip

[comment]

See if this makes sense. Note that the lookups are necessary only if you need to protect the looked-up data against subsequent changes (e.g. contact name at the time of lookup).

Transcript Requests2.zip

[steigrafx]

For some reason I can't extract your file. Windows XP reports the file is being blocked, and Windows 7 says it's an invalid file.

[comment]

It works fine for me, but here's another try anyway:

Transcript Requests2.zip

[steigrafx]

Got it now. Thank you.

I'm going to have to spend some time with this since I need to get it through my head.

Thanks so much for your time and help.

Kevin

[steigrafx]

There's no question that your fix definitely works, but I'm wondering why changing the Nickname calculation stopped everything dead in its tracks. I'm attaching a copy of my original file. If you click on the Service Address field, a drop-down shows the Nicknames of the services, and clicking on a name populates all the fields properly. The only difference between the two files is that single Nickname calculation.

I don't mean to make you crazy with this, it's just that I'm still wondering why changing the calculation in the Nickname field breaks the look-ups.

Thanks,

Kevin

Transcript Requests3.zip

[Ron Cates]

Just looked at your file and noticed that you are not using serial number ids. The basic structure of a relational database starts with each record having it's own auto enter serial id as a unique identifier. That is what should be used in your relationships. You are using data to link your records. That data changes and your relationships are broken. For example, if you use a name field to relate to all invoices for a single person what is happenning is that all invoice records for that person now have the name used when creating them. A relationship is essentially a find. When you look accross a relationship to a group of child record you are actually saying something like "show me all records with the name Jane Smith in the foriegn key field". But if Jane gets married or you find that you misspelled her name or for any reason you change the name. Now all records that were related via that name before are not related to the new name because it no longer matches. But when you use an auto enter serial number that can not be changed as your primary key, no matter how much the rest of the data changes your related records remain related.

[steigrafx]

Thanks, Ron. Yes, that is exactly what comment did in his example. Thank you for the lesson -- your explanation provided the clarification I needed.

Apparently I was doing it wrong. I'll know better in the future.

Have a great day.

Kevin