Help with Limiting Results

[dfiller]

I'm doing a small mailer for a project in a Datbase Publishing Applications Course. One of the programs being taught among PSM, RagTime, and Plugins for InDesign, is FileMaker Pro.

I've created 3 tables. One that is a database of customers. The other is a database of wines. Lastly a table to tie these two together that lists the customer id and the wine id of the wine they've purchased.

My layout currently utilizes this last table, but uses portals to retreive information from the other tables related to the wines purchased table. Still with me?

Currently, the mailer displays the prices of all the past wines they've purchased. Because I would like to limit how many are output to keep the length of the piece under control, I need to make sure there are only 8 total wines shown from their previous purchases. How would I go about getting the values from these fields I need only 8 times? Can I display random ones? I've looked into Calculations, but I'm slightly lost on how to go about this.

Any help is greatly appreciated, feel free to email me, I can even send the 2MB file or discuss my problem over messenger as well. My professor isn't quite sure how to go about it either. Thanks in advance.

~Daniel

[RalphL]

Welcome to the Forum,

Is this cheating? This might be a good time to use a portal in your printout. Limit it to 8 rows. Sort it by any number methods, date, price, even a random number.

[Fenton]

I would agree with Ralph. If this print job has fixed sizes, so you would know where to put a portal, so that it will not break over a page break, that would be easy. I wonder though why you're doing this print from the "join" table. While that would the preferred table for reports, it seems like what you're doing would work best from the Customer table; where there's only 1 record/customer, and where the relationship for the portal already exists.

[dfiller]

Ralph, Fenton -

Thanks for your quick replies. First off this isn't cheating. I've talked with my professor and he advised me to browse the vast world wide web for some help.

Fenton, if I do this from the Customers table, will I be able to get data via a portal from the Wines table as well? I'm basically displaying prices on past wines the user has already purchased.

I am currently using a Portal to get data from the Wines table. But I'm new with FileMaker Pro (a few weeks now). How would I go about limiting the results the Portal returns? Thanks in advance guys.

~Daniel

[RalphL]

If you only show 8 rows in the portal, fixed no scroll bar, it will show the first 8 rows depending on sort order.

[Fenton]

As Ralph says, a portal can only return as many rows as you set it for, on the layout, so limiting it is not a problem. As far as showing data from the Wines that a customer has purchased, that would be the wines in the portal. Any other data about the wine would be accessible from the Wines table "through" the portal; as they are in a straight logical path on the Relationship Graph. Being able to access the Wines table this way is new to 7, but used to be commonly done with an intermediary calculation field in the join file in 6. I am getting the impression that they are not giving enough emphasis to FileMaker in the course :-]

[dfiller]

Well it's a hodge-podge of variable data applications being taught to us. So you

[Fenton]

The recommendation of a wine matching one of his "favorites" for color or varietal would be possible. But it would be somewhat difficult. First you'd have to determine which was his favorite color, which would mean counting the colors of the wines he bought, seeing which had the Max() hits. You'd have to calculate and store which one it was, in an unstored calculation, or in a global.

So then you'd have a favorite color, which you could use to get a list of same-colored wines in the Wines table. But you'd want to filter out all the wines he'd already bought; it would look silly to recommend one of his previous purchases. You'd want to capture his purchases, of that color, with a calculation using the ValueListItems() function, or a Find (or Go To Related Record) and Copy All Records (to get the WineIDs), possibly then set into a global multi-line field.

To complicate matters, if you wanted to only recommend 1 wine, then you'd have to compare whether he bought more of one color, or more of one varietal. Possible, but fuzzy, IMHO. It would be easier to recommend one for color and one for varietal; but that's 2 operations.

You'd need a few dummy "color" and "varietal" fields, unstored calculations, one for each color and varietal, and relationships using them to the Purchases table. And calculations counting the hits. These counts would be Unstored, hence beginning to slow down. If the color and varietal data is still in the Wines table, not looked up, then you're going through the Purchases, which adds another speed hit (I believe; perhaps not). Fortunately there are not many colors (what, red, white, mixed?). Varietals I don't know.

At this point I might switch to a script. Because using the Max() on these unstored calculations is going to be slow. And, at the end you're going to have to filter out all his purchases from the results. Which is filtering a set of found records to remove another set. This could be calculated. I think there is a Custom Function available for this (requires Developer to add it to a file). But it could also be done with a scripted Loop.

If it was scripted you would also need to manage when this script occurred, and when the results should be updated. Lots of fun. If I have time on my hands, I might try it, maybe. It would work.

[dfiller]

I have continuously tried to set the Number or Rows to 8 using a portal and it continues to display every record that matches the criteria. I'm really not sure what I'm doing wrong here.

[Fenton]

Say what :-? A portal has a set number of rows. It will also have a scroll bar, which you can turn off. Is that what you mean? If you are seeing a full screen of records, then I can only guess that you are not using a portal, but a list view of regular records. But it seems odd; a portal is a pretty obvious thing. If you go into Layout mode and click on a portal, its dialog will show. That's where you set the number of rows.

If you can save a clone of your database and post it, we could look. Or, if it's smallish, 500K or so, you can post with data. And tell us which layout you're talking about.