look up value from a 2 way table

[Bmiller4evr]

I have a series of map co-ordinates, they coordinates are always 3 numbers followed by a letter. the first 3 numbers represent a page number of a map, and the letter represents an area on that page. I don't know if any of you are familiar with a mapsco, they are very popular in Texas, but that is where the coordinates have come from. Sample data would be as follows:

063B ... page 63 letter B

121A ... page 121 letter A

009R ... ect.

102P

I need to assign postal codes to these boxes. the only way to accomplish this task was to lay out a table where the page numbers run vertically and the letters run horizontally. if i were using excel i would use an index function and match the page numbers to the corresponding rows and the letter to the correct column to produce the zipcode. that is not a feasible solution for me. any thoughts on how to break up the page number and letter from this 1 field, and then do a 2way lookup? (is that the correct terminology?)

Would be very open to a different type of solution if it still solves the underlying problem. Im not especially jazzed about converting my look-up data into a 1 dimensional table, but if it would be easier then I would most certainly welcome a solution that worked that way instead.

[comment]

if i were using excel i would use an index function and match the page numbers to the corresponding rows and the letter to the correct column to produce the zipcode.

You would do almost the same thing in Filemaker, except your Zipcodes table would have a separate record for each zipcode - with fields for page number, letter and zipcode.

You could also do the lookup from a "two-dimensional" table of zipcodes - but it would have to be done by auto-entering a calculated value. In any case, your location data should be in separate fields.

they coordinates are always 3 numbers followed by a letter.

I don't see that in your sample data.

[Bmiller4evr]

is your handle really comment?

to your first remark, i am brand new to scripting, a bit more assistance would be of much appreciation.

to your second remark, the creator of the original dataset big on keeping fields of a uniform length, that is why there are leading zeros on the page number.

[comment]

i am brand new to scripting, a bit more assistance would be of much appreciation.

No scripting is required here - it can be done with a relationship and a lookup of related data. I can't be more specific until you clarify exactly what your data looks like - in both tables.

to your second remark, the creator of the original dataset big on keeping fields of a uniform length

If you have a field that contains (only) the string "063B", then splitting it into "63" and "B" is rather trivial.

is your handle really comment?

Yes.

[Wodin]

I don't see that in your sample data.

I believe he means 3 digits followed by a letter. Perhaps that clarifies it? All the data in his first post does indeed have 3 digits followed by a letter.

[Bmiller4evr]

yes, 3 digits.

But I still have no idea how to do a 2 way lookup. I'm not even sure the proper way to import the data into FMP to facilitate the process. Right now it is a table in excel, 26 columns wide (1 for each letter) and 150 rows long (1 for each page). I get the concept of doing a calculated look-up, and have no problem separating the page number from the coordinate letter into 2 fields. But the act of using those together to pull out the zipcode, I am not grasping how to do it.

I was thinking that I would create a record for each page and have 26 fields in each record with each containing the appropriate zipcode. Which is what I have already done. But from there I get lost.

Hope there are more thoughts on the matter.

Thank you

[comment]

As I said earlier, it would be best to have the zipcodes as individual records. Then you would "pull" the correct zipcode via a relationship matching on both page and column.

I was thinking that I would create a record for each page and have 26 fields in each record with each containing the appropriate zipcode.

As i also said earlier, that too is possible - it's just a bit more complex and likely to be slower. Say your Zipcodes table has a field named Page and 26 fields named A, B, C ... Z. Define a relationship matching on Page, and a calculation field =

GetField ( "Zipcodes::" & Column )

 

where Column is a text field holding the column letter. Or just calculate the letter directly from the original ###@ field as =

 

GetField ( "Zipcodes::" & Right ( OrignalField ; 1 ) )

[Bmiller4evr]

Thank you again. Using GetField ( "Zipcodes::" & Column ) works great. Unfortunately I am doing the same thing with a different set of county records now and they do not store their mapsco coordinates in the same way. Perhaps you could give some assistance on calculating from a field in a different way. Here is a sample of the data:

66-a (dallas)

55-z (dallas)

9-r (dallas)

29a-v (dallas)

19a-f (dallas)

30a-x (dallas)

36-e (dallas)

4b-k (dallas)

on this particular mapsco they had to go back and add in additional pages to include areas further out that was originally intented. as such, pages may appear as 19a, 19b, 19c. the hyphen separates the page from the letter block and the "(dallas)" part is of no use to me.

what calculation will yield me results of:

Mapsco_Page , Mapsco_Letter

66, a

55, z

9, r

29a, v

19a, f

30a, x

36, e

4b, k

from the above sample.

I think I know the steps to make this happen, but am not quite sure of the syntax.

For the Mapsco_Page do a Left of how ever many characters occur before the "-" and then a Mid for the 1 character after the "-" to get the Mapsco_Letter.

am I on the right track? hope you can help out.

Thank you

[comment]

Try =

LeftWords ( Substitute (  YourField ; "-" ; " " ) ; 1 )

 

for the page, and =

 

MiddleWords ( Substitute (  YourField ; "-" ; " " ) ; 2 ; 1 )

for the column.