Reader Service Card

[flyzone]

I'm trying to write a DB capable of processing information from a Reader Service Card.

The card asks people to indicate the companies that they would like to receive additional information from.

They can choose from 1 to 300 odd different companies. It also collects contact information and an area of interest.

Each company has a unique RSC # and of course the other variable is the magazine issue.

I'm basically extending a previous DB that I did that was recording the advertising contracts for each company based on the magazine issue. Meaning one table is all company info a second table is recording the publication issue.

I would like to be able to send a report to each company with the name, email, address and phone numbers for everyone who expressed an interest in their products that month.

People usually ask for more than one company in each card.

I tried to relate the different table so a search per company will be able to give me a montly list of the readers but I can't find a solution.

Any idea how I could relate the different tables?

Thanks,

fly

[Kent Searight]

Try this example as a starting point.

CompaniesToPeople.zip

[flyzone]

Thanks for your sample. I tried to use a joining table but I guess I'm still doing something wrong. Anyway here's an idea of what I'm trying to do. It's still a work in progress using different pieces of software. (why reinventing the wheel all the time : )

The RSC file is getting the info from two tables from the ad_tracker FileMaker.

In order to insert the different RSC on for each reader I’m using a variable with multiple values. But I guess that’s wrong too. Or at least is wrong the way the relation is working because it should recognize each individual value and match it for the unique company RSC.

As you will see the publication issue is also playing a role. But again I’m probably connecting the tables in the wrong way.

Thanks,

Fly :qwery:

[Kent Searight]

I'm a little confused with the methodology in your example. Here are some suggestions for cleaning it up that will likely lead you down the path you're trying to go.

First, just have one table to store all your Issue records in. You don't need (or want) a separate table for each issue.

Second, get rid of the repeating fields for your RSC numbers.

Third, create a join table between the "company" TO (table occurrence) and the "person interested in getting info from that company" TO (I'm not sure what TO that is in your example). Relate them as my example shows. In the join table you can put an "issue" field to call out which issue the reader got the card from.

Use a portal to facilitate record creation and viewing which person relates to which company and which issue they got the card from.

[flyzone]

Ok, I went out and got a nice heavy book on FM7 Interesting lecture:)

I do have a couple of questions from your last post.

Can you explaing what you mean by

>First, just have one table to store all your Issue records in. You don't need (or want) a separate table for each issue.[color:red]

There's only one table for the issue, the other tables are other publications issues.

The fields about the issue on the Company table aren't really doing anything at the time. I guess they were there because I didn't know if I needed to separate them from the companies table or have them as an attribute.

Second, regarding the RSC numbers. How am I suppose to enter them for the reader, because each of them can ask for info on 1 or 20 or N companies.

Sorry for the confusion.

Thanks

Fly

[Kent Searight]

There's only one table for the issue, the other tables are other publications issues.

Just have one table for your publication issues. In this table you should have a field that allows you to define which publication it is (Azure, Review, Design Lines, etc.). You can then create TO's (table occurrences) for each specific publication based on that table. These will be helpful for displaying the different publications in portals.

Second, regarding the RSC numbers. How am I suppose to enter them for the reader, because each of them can ask for info on 1 or 20 or N companies.

I assume that the RSC# for any given company stays the same from issue to issue. If that's true, then you just need a table for the RSC#'s. You create a record in this table for every box your reader checks on the card.

[flyzone]

Ok, the the table issue is clear.

I don't think it will be easy for me because we're already using the Ad tracker system and move all all the issue in one table will create to me a hell of a mess for all the records that are already in place

The reader service is just an integration to something already in place.

But the Reader Service is only available on one publication, the others do not have RSC.

Anyway in the meantime I did some changes to the file, working with the portals.

the RSC are unique for the companies and will remain the same all the time, so what I did I've considered them as an attribute to the company.

I'm still having some trouble relating the issue. I'm sure I'm doing something wrong.

Thanks for your patience.

Fly

[Kent Searight]

the RSC are unique for the companies and will remain the same all the time, so what I did I've considered them as an attribute to the company.

I'm still having some trouble relating the issue. I'm sure I'm doing something wrong.

The relationship looks fine What isn't it doing that you wish it were doing?

[flyzone]

For example on the company_readers layout when I choose an issue the portal should give me, per each company, the name of the readers that asked about them in that particular issue.

Instead what I'm getting is just the companies that relate to that issue.

I need to sort them and the readers.

instead on the portal I'm getting all the readers that asked info on the company ...

i need to tell that company that reader x asked about them on Jan, and maybe reader y asked about them on the march issue. This because the product advertised on the 2 issue could be different.

[Kent Searight]

For example on the [color:red]company_readers layout when I choose an issue the portal should give me, per each company, the name of the readers that asked about them in that particular issue.

I can't find the company_readers layout. Which file is it in?

[flyzone]

It's on the last file that I uploaded. you can choose the layout from the layout menu. It's the last one. The perfect name is companies_readers

[Kent Searight]

Found it...I was looking at an older one. And sorry for the delay in getting back to you...I got really busy all of a sudden.

Anyway, what you need to do is create a means to filter your portal. Take a look at the example accompanying this post and see if it makes sense.

Also, if you don't mind me making a suggestion... : ...I think it would be a good idea to draw out your relationships on paper so that you know what you're after. Are you tracking readers or are you tracking reader cards? or both? Where's your table for readers?

CompaniesToPeopleFiltered.zip

[flyzone]

Found it...I was looking at an older one. And sorry for the delay in getting back to you...I got really busy all of a sudden.

Anyway, what you need to do is create a means to filter your portal. Take a look at the example accompanying this post and see if it makes sense.

Also, if you don't mind me making a suggestion... : ...I think it would be a good idea to draw out your relationships on paper so that you know what you're after. Are you tracking readers or are you tracking reader cards? or both? Where's your table for readers?

Don't worry for the delay. I got your sample for the filter. I'm trying to understand it.

the pdf in attachment in giving you an idea of the problem. What I'm traking are Readers not Readers card. In the sample my Readers card is the "Readers Service Request". Sorry for the name it's just that I was trying to build a sample before tranfering the concept on the real DB.

I should be able to insert all the Readers that ask for info on the companies. Then go to the "Companies_readers" layout and filter based on the issue the companies that received a request.

Right now I can do that but I the issue filter is not acting on the readers. Meaning if I filter the A issue the result is giving me all the companies name for that particular issue but on the portal I still get the Readers for all the issues.

Hope I was clear enough :

relationship.pdf

[Kent Searight]

I think if you study my example long enough you'll be able to create your own many-to-many relationships and filtered portals as needed. Here are some pointers about where you should be concentrating your efforts to solve your problem:

* Notice how any single table can have more than one occurrence in the Relationship Graph.

*Notice how a global field is used as a key for filtering.

BTW I looked at your PDF of the relationship diagram, and it won't work. This is where you'll need additional table occurrences, since you can't have circular relationships in FMP.

[comment]

PMJI,

My humble experience - as someone who has filled out a RSC once or twice - would lead me to suggest the attached structure.

[Kent Searight]

Hi comment,

Wouldn't the Request for Info table need to have a field for Issue also in order for the filtered portal to work the way flyzone wants it to? And then wouldn't it make sense to have a separate Readers table since a Reader can fill out many Request for Info Cards over the course of time (various Issues)?

[comment]

Wouldn't the Request for Info table need to have a field for Issue also in order for the filtered portal

The Request already "knows" which Issue it belongs to, via AdvertID.

I don't quite see the need for a filtered portal. I think a report of Requests, grouped by Advertiser/Issue, needs to be sent to each Advertiser to act upon.

wouldn't it make sense to have a separate Readers table

The readers are general public. You get a request for information from Joe Shmoe. What use is tracking him? Next time he fills the card, he's going to misspell his name and use up another record in your db. From my experience, if you fill out the card twice in a row, you get the same brochure from the manufacturer twice. They just assume the first one got lost in the mail.

Perhaps at some point it might make sense to keep a db of readers, to track interest trends ("80% of readers interested in product A, also expressed interest in product B") and such. But I think right now we are struggling with the basics here.

[Kent Searight]

The Request already "knows" which Issue it belongs to, via AdvertID.

If I'm following your line of thought correctly, the AdvertID is not necessarily the same ID for any given company from issue to issue.

[comment]

That is correct - it can be simply a serialID in the Adverts table. This way, you can associate responses with specific ads and issues. A company can have several adverts in a single issue - say a general PR ad, and two specific products. Or the same ad can run several issues in a row. But the structure allows you to generate statistics from any point of view you choose.

[Kent Searight]

I hadn't looked at it that way before but it makes a lot of sense.