I'm sure this is pretty simple thing for most people here, but I'm a newbie. Using FMP 7, I have a database of people from all over the country. I need to sort first by state, then within the state, alphabetically by last name . That much I can figure out.

What I need some advice on is that the database has the state entered by the 2 letter abbreviation and what I need to do is to create a report that has the full state name as a heading above all the people in that state. I believe that is called a leading subsummary. I think I probably need another table in the database that has the full state names, but I don't know how to build a relationship (or is it a lookup?) between the two. I also want the database to be able to put any new entries under the correct state heading automatically. Any help would be much appreciated.

Randy

Hi, randini! Look attachment.

Sorry! On my internet attachment possibility not work. I will attach file later.

You must sort you records by state and city. Then in the report create subsummary part by state, body must have city. Then look it in preview mode. I did it in attachment, but can not attach here.

Edited January 30, 200620 yr by Guest

Thanks for the reply aaa. I don't understand what the city has to do with it and I'm still not sure how to convert the 2 letter abbreviated state names into full names for the leading subsummaries. Sorry for being so dense.

Randy

Hi Randy,

To get your full state names on your report, you might use a State table, where each record is one state, and has an Abbreviation field and a State Name field. Create a relationship between your state abbreviation field and the Abbreviation in the State table, then place the related State Name field in a Sub-Summary by State::State Name part.

Creating the actual relationship for this is very easy. In the Define Database dialog, after you've defined the State table with the correct fields, switch to the Relationships tab. You should see your existing table(s) and the new State table presented as boxes. These are called "Table Occurences". To create a relationship between two table occurences, simple drag from a field in one to a field in another. If you make a mistake, or you need to define a more complex relationship, double click the relationship line and select the match fields from the lists.

Good Luck!

Thanks Ender

That sounds like it will work great, I'm up to my ears right now in other work so I will have to try it later. Very clear explanation. I really appreciate it.

Thanks

Randy