setfield in db A with data from db B quagmire

[Gilliam]

The strange thing is--it works--but randomly.

1. A contacts db with a city field is related to a city/county/tax rate db by city.

2. The user enters city name in contacts db and opens related record in city/county/tax rate db by way of a button based on city::city relationship.

3. A set field script attached to a button in the city/etc db copies the county and tax rate to the contacts db.

4. Problem is...in many instances it copies the data for some records but not for others; regardless of the fact that the same relationship works with a GotoRelatedRecord script from either db; meaning that both city fields are identical. Yes?

I'm stumped by this: if it works for the city of Brea why wouldn't it work for the city of Brentwood for instance?

Cavaet: If I type...let's say...a "1" after Brentwood, it works. Take away the "1", it doesn't.

I'm baffled.

And why wouldn't I use a simple lookup? Well, because there is more than one city of Brentwood, amongst others...

And why wouldn't I use a portal with a similar setfield script? It'll mess with my lovely layout.

Any insight would be appreciated.

[Ugo DI LUCA]

set a serial number in your city db and use only this number as the relationship.

Relationship on names can have this behaviour.

If you want to keep your settings, then make a pause script after the "go to related records", then look to the list of found sets, select the city from the list, click continue.

[jasonwood]

Actually, I think this is one of those cases where a name is better. I wouldn't want to change that to a serial number...

I believe what is happening is you have multiple contacts in the same city, then you go to your cities database and send back the country/tax rate. Problem is, this goes to the first record in the relationship only. That's why putting a one after the city makes it work again (it becomes the first city to be called Brentwood1!

Why are you performing this script from the cities database?

Why not just run it from the contacts database? You'd simply need to deal with the problem of multiple cities with the same name. Would I be correct in assuming there could never be two cities with the same name AND state/province? You could make a calculation field based on both (=cityField & "-" & stateField). Normally tax rates aren't by city anyway, so I don't see why you don't just use the state for the tax rate/country lookup.

[Gilliam]

Thanks fellows.

Solution:

1. calc field in db A: concate city & recordID, which is copied to...

2. global field in db B with GotoRelatedRecord script based on city::city

3. relationship in db B is based on relationship city/recordID::city/recordID and therefore...

4. copies data accordingly.

Rather stupid of me actually, since I've employed the same formula in the past.

Thanks for the reminder...

re: why not do just create a city or county drop-down with corresponding lookup fields?

1. A CA city drop down value list would contain 837+ cities (not very pragmatic) and would not include multiples of the same city in different counties without appending a tag to one of them. This would require that all users would be famaliar with the tag (again, not very pragmatic). Of course, the value list could also associate with the county field to resolve the multiple city name issue, but this would still mean a user must wade through a value list 837+ names long.

2. A county drop down value list has been in effect since the beginning. This is more than satisfactory when the user is provided the name of the county, but in those instances when the county name is not provided, the user must resort to internet lookup or pulling out a hard copy listing. This procedure is a nuisance.