Pulling data from value lists with script

[Hew]

There must be a simple solution - but I can't find it.

When a user enters a ctiy name I would like to add the postcode and state at the press of a button. The postcodes come from a value list based on all cities and postcodes already entered. I would like to do this by using the vlaue lists and there seems to be all the functions except one. The value list for postcode and town can be put into two global fields with 'ValueListItems ( Get ( FileName ) ; "Postcodes")' and ValueListItems ( Get ( FileName ) ; "City"). I would like to be able to find the value number of the city so that the script could pull the postcode from the coresponding value postion in Postcodes with 'MiddleValues ( Postcodes ; position of city value ; 1 ), something like "ValuePosition( City ; in value list called city)" would be wonderful!

A solution with a self join woul work but I just wondered if this wonderful new Value function was fully implemented?

[underhat]

Hi Hew,

There are some problems with the idea as you outline it. (Detailed below.)

In short, you're going to have a lot less work if you do something slightly different: Have the user enter the state FIRST, then pick from a list of cities in that state (using a simple self-join on state). From there, using a self join on city AND state, you can get the postal code. The reason you have to pick the state first is that there may be cities of the same names in different states. If you're sure this isn't going to happen in your case, the problem is much easier... but I'm going to assume that you DO have to take it into account.

Now, here are the problems with what you have:

1. The list of cities and the list of postal codes will NOT be sorted in the same order. Each value list sorts independently (and alphabetically). So the 4th city on the list does not necessarily correspond to the 4th line of the postal codes value list.

You can get around this by making a single value list showing two fields. The first field is the postal code, and the second is the city. Sort it on the second field. But this leads into the second problem...

2. How are you going to handle it when there are cities with the same name in different states? First, a technical issue: when you sort the value list by city (as I've said above), it will only show ONE value for any given city name. So if you have three cities with the same name in different states, only one is going to show up. So this value list approach quickly gets more complicated.

More important, you need to give the user a choice if there is more than one possible state for the city name they've entered. So your button is going to have to show a message if there's more than one option, and ask the user which one to use.

So, two options (I'm assuming you're using FM7, since you mention MiddleValues):

A. Use a self-join from City Name to City Name. Now create a value list for the State field, using this relationship. Your button should run a script which checks whether there's more than one entry in the state value list. If there is, it asks the user which state to use. Then, you have a second self-join on two match fields: City Name AND State. You can use this relationship to get the postal code for the correct city and state (you don't need a second value list for this). That's two self-joins. A bit messy.

B. You're going to need three value lists and the ValuePosition function (included below) for this approach, but no relationships. Create a calculated field = City Name & " " & State. Call it City Calc. Now create:

Value list "Cities", which shows two fields: Cities, and City Calc; sort by City Calc (second field).

Value list "State", which shows two fields: State, and City Calc; sort by City Calc (second field).

Value list "Postal Codes", which shows two fields: Postal Code, and City Calc; sort by City Calc (second field).

Now, you can use the ValuePosition function to find the position of the city in the "Cities" list, and use MiddleValues to pull out the appropriate State and Postal Code from the other two lists (because they're all sorted the same). The reason for having both the city name and the state in the calculated field is so that the sort will show cities with the same name in different states (and not collapse them into one value). This is the sort of approach you were trying to do originally. Note that you'll still have to ask the user if there's more than one state for the city... but it's harder to tell when this happens than with the self join. (You have to have your script notice that the city appears more than once in the "Cities" value list.)

And here's a ValuePosition(valueList; searchString; occurence) custom function I whipped up:

Let(

listItems = ValueListItems(Get(FileName); valueList);

If (not IsEmpty(listItems); PatternCount(Left("

[Hew]

Wow! Thank you for your quick and detailed reply. I guess you'r just starting the day and I'm just going to bed so I need some time to work through your ideas, and thank you for the custom function model, hadn't thought of those.

Just a note on synchronising the two value lists, you are correct in '1' para 2. List one contains postcode and 'also show fields from..' the city field. Records are sorted by the city field. Value list 2 only contains the city and is sorted by city. Both lists then have the same number of values and in the same order. (works so far)

Pity to have to enter the state, so perhaps, 1st click brings up the first occurance, 2nd click brings up the 2nd etc. It is pretty easy to recognise a city by it's postcode in Aus, two cities in the same state with the same name is pretty rare. Also, as the postcode list is built from the users records the occurrences of duplicate city names becomes lessened.

Once again, thank you for your reply.

[underhat]

Heh, just starting the day? I'm in western Canada... was just up way too late.

Since you're already sorting your postal codes by city, that will work for you (using the ValuePosition function), as long as you don't have two cities with the same name. Note that it's two cities with the same CITY name, but in DIFFERENT states that causes the problem. I think it's quite reasonable to assume there will never be two cities of the same name in the SAME state. So that's why I say you should make sure the state is included anywhere you're sorting by city, etc.

Good to see there's some FileMaker work going on in Australia!