Newbie Question: Static, Unrelated Data Lookup

[GeorgeM]

I'm new to Filemaker Pro, and if this is an old issue or posted in the wrong forum, I'm sorry - I've tried searching the forum but perhaps I don't have the right vocabulary for what I want to do.

I need to provide a database with access to certain static reference data that is unrelated to any given record. Globals are not an option here, at least as far as I understand them.

To be specific, I am creating a database to track classified ads in the publication I work for. This is currently done in the ancient DOS database Q&A. Our publication comes out every two weeks. Under normal circumstances that means each volume will have 26 issues - but since the year has 52 weeks and 1.25 days, occasionally we'll add a 27th to keep the "Issue 1" of each volume from slipping through the year.

So, I want to be able to query a table with a particular volume number, and get back how many issues are planned for that volume. If I use globals, I'll have to keep adding fields to the database as the years go by, and that just doesn't seem right. Back in ancient Q&A, there was a simple look-up table attached to each database for such information. How do I get a similar result in FileMaker Pro 8?

[Razumovsky]

How about creating a separate issue table that is linked to your volume table, then use count(issue::IssueID) function in a calc?

Alternatively, you could write a calc that would automatically give you how many issues for a given year, but you will probably want a separate record for each issue anyway...

hth

-Raz

[GeorgeM]

How about creating a separate issue table that is linked to your volume table, then use count(issue::IssueID) function in a calc?

Perhaps I didn't explain this well... there is no "volume" table; what I'm talking about is a simple two-field table that stores a volume number and the issue count in each record. There is no "relationship" between this information and any classified ad record - it's simply reference data to be used in calculations - such as when someone takes out an ad for a number of issues that spans two volumes. Say someone placed an ad for 6 insertions, starting with issue 24; I need to know if that volume will have 26 or 27 issues in order to determine whether the ad's run will finish on issue 3 or issue 2 of the following volume.

Maybe I'm just not seeing this the "FileMaker way" yet, but I can see all kinds of places one might need reference data like this that isn't directly relatable to particular tables. Or perhaps I'm just not seeing how to relate it.

Alternatively, you could write a calc that would automatically give you how many issues for a given year, but you will probably want a separate record for each issue anyway...

The problem is that the decision of when to insert an extra issue is not entirely programmatic - it's done when management feels the date of Issue 1 of the next volume will have slipped "too far." Maybe it should be done by calculation, but that's not my decision to make. :

[Razumovsky]

Hmm, I didnt notice that you were a first time poster- Welcome!

I think you should review the 'About planning a database' topic in the manual. You most likely will want at least three tables, Ad, Issue, and Volume.

Each ad record in the ad table would have an Issue ID that would relate it to the Issue record in the issue table, Each Issue would have a VolumeID which would relate it to your volume record in your volume table. Sketch out how they might relate on a sheet of paper first, it might make things clearer.

HTH

-Raz

[GeorgeM]

New poster here... and new to Filemaker Pro, though not new to database concepts. It's like having to use an unfamiliar word processor - I know what I want to accomplish, I just don't know this "dialect," if you will.

I actually have eight tables defined:

Classified (for the ad and unique metadata)

Advertiser (Name, address, phone, etc...)

Category

Photo (for tracking images)

ThirtyTwo (for tracking our "business card" ads)

Credit (When someone overpays or cancels)

Transaction (for retaining financial data)

and finally...

IssuesPerVolume

It suddenly dawned on me that there is a relation betweein IssuesPerVolume and Classified; it's the field in Classified I call "StartVolume" - that's the volume number that I need to know the issue count for. :

Sorry for wasting your time - I just didn't see the relationship at first. Isn't learning new software fun.... *heh*

[Razumovsky]

Glad to see you are in better shape than I first thought! Have fun.

-Raz