Dazed & Confused

[laker_42]

I believe this is the appropriate section for my question. If not I apologize. Here is the situation:

we have three fmp databases. One is a program database that contains information on radio programs. It contains a program & station ID field along with other programming info. A program can be aired on several different stations and a station can air several programs.

The second database is a zips database that contains stationID info and the zipcode of the station. So the two fields to work with are stationID and Zip(which is the first 3 numbers of the zip code). A station can air across several different zipcodes and a zipcode can have several different radio stations airing within it.

The third database is a member database that contains our list of members and their addresses.

We send out letters to our members on a regular basis. We would like to be able to print out program info on the back of the letters that we send out to our members. We want to print out the programs that are aired on stations that air in the zipcode of the particular member.

For example:

John Smith

1234 Lane Dr

Chicago Ill 45987

These programs would print on back of letter:

Program 1 Station1 Airs mon - fri 7am - 8 am

Program2 Station 1 airs tues 10am - 12pm

Program 1 Station2 Airs tues - sat 3pm - 5m

Did I explain that clearly enough? If not, please ask and I will try better. We tried creating a serial # field in the program & zip databases. We then created a join database and tried linking based on those links. We believe we were able to accomplish this but we had to create the link manually via a drop down list. We want to automate this so we don't have to intervene. Has anyone done anything similar to this that they could share with us? Or have any sample databases that we could look at? We would appreciate any help that anyone can offer.

Thanks,

John

Sorry about the long post!

[CobaltSky]

A solution to your problem would be achievable by:

1. Creating a relationship from your members file to the zip file based on the zip fields in both files.

2. Creating a value list in the zip file called 'ZipStationIDs' which picks up related values only, from the stationID field in the zip file, via the relationship created at step 1.

3. Create an unstored calculation in your members file with the formula ValueListItems("membersdb.fp5", "ZipStationIDs")

4. Create a relationship which links the new field created at step 3 to the stationID field in the programs file.

Via the relationship created at step 4, you will be able to view (and/or print) a summary listing of all programs in the local area for each member (eg in a portal for each member record).

[laker_42]

Thanks for the suggestion Ray. We will give it a try and let you know what happens!

John