Makinga relationship based on related fields

[keshalyi]

I KNOW! You can't make a relationship to a field that cannot be indexed, so I can't relate to a calculation that uses related fields. But what am I to do? Let me tell you my situation, and hopefully somone has some good suggestions. I'm designing a system that paginates classified ads onto Quark pages. It works like a charm, as far as the actual pagination goes. The problem is, I want to present a list of pages so they can select which page to put the ads onto. In the list, I only want to show pages that are not already full of ads - but there's the problem. How can I relate to only pages without ads if I can't relate to a field that refers to the database of ads? Thoughts? Feelings? 0

[LaRetta]

Hi keshalyi,

You can always relate two unrelated dBs by using a calculation (num) = 1, maybe called cGlobal.Key. You didn't describe your structure so I can't help any further without knowing your specific situation, but once the relationship is established, you can use a calc to identify pages without ads.

If you can describe your existing relationships, we'all could probably help you further. Good luck to you!!

LaRetta

[keshalyi]

Sorry, you know how it is, panic makes one undescriptive

Anyway, here we are. The whole system is huge, and would take too long to describe, but for the purposes of the illustration, there are four DB's: PagePixie, Pages, Gaps, and Ad Instances.

PagePixie is the mother DB, and it basically stores issues of the newspaper being constructed. Each record has a unique key called Paper Number, and each record is one issue of the paper.

Pages is a db of statistics on the actual pages of the paper. The pages are built in Quark XPress, with space left open for the classified ads to go into, then an applescript imports them in the Pages DB. Pages stores the information about the page itself (filename, size, columns, gutter width, etc).

Gaps is a db of the individual gaps on the pages that are waiting to be filled with ads. Each Gap has a column it resides in, a top y value on the page and a bottom y value on the page.

Ad Instances is a db of every printable instance of every ad entered into the system. For the purposes of the illustration, think of it simply as a db of Ads to fit into the gaps. Each has a length, and a body of text, possibly a graphic.

Now...

Here's the problem.

In Page Pixie I need to display a portal of Pages. This portal displays the pertinent specs of each page, as well as buttons to let you preview a page, insert ads into the gaps on the page, revert the page to aan empty one, etc, etc. But, here's the dilemna. Many of these newspapers (they are those dorky free weeklies that just have ads in them) have upwards of 50 pages, all squeezed in at once. Now, lets say you've finished paginating pages 1 thru 30, and are working on 31, alright? Now, you scroll this big long portal down to row 31, and twiddle the button, and it does what its supposed to do, and then you need to press another button, right? Well, you have to scroll ALL the way down to page 31 AGAIN, and then when you're finally done with the page you have to scroll down ALL the way to page 32, because (and I must say this is irritating) when the screen refreshes, or even sometimes when I go and do a script in another DB, or something, suddenly it scrolls BACK TO RECORD 1. Now, even if it didn't, they should still be able to filter the list down and see just pages that aren't full yet, right? I mean, with 50 pages, you should be able to parse otu the ones you're done with!

There's the trouble.

See, its not really the pages that fill up, its the Gaps, and then when every Gap related to to the page is full, THEN the page is full, right? Simple enough, I make two relationships to Gaps, one that displays all GAps on the page (Gaps on Page) and one that displays only those that are full (Full Gaps on Page). Then I make a field: Filled is the result of (Count(Gaps on Page:Gap Id) = Count(Full Gaps on Page: Gap ID)). Voila, that field works GREAT.

But, then to make a relationship to only filled pages from Page Pixie, I make a Key that's: Paper Number & " | " & FilledStatus. BANG! There

[slstrother]

The only way I can see to create the key is to use a script and set the value.

Set Field["Key","Paper Number&"|"&FilledStatus"]

[Ugo DI LUCA]

You could also create a lookup field and a calculation Case(lookup=X,relationkey,"")

[falkaholic]

CobatSky has a good artical in the articles section about indexing unindexible fields.