2 criteria for relationship?

[Orwell]

So here's the story. I have a database containing "Client Activity"; it's related to another database which provides the client information. What I need is a way to produce a report for each month of activity of each client. The problem that I'm running into is that the relationships for my report are pulling all the records for a client (giving me all months, not just the one I need), or all the records for a month (giving me all clients for a month). Is there a way to hide looked-up records from another file besides the source file? I have a feeling I'm missing something really basic here.

[Ender]

Your profile doesn't say what version of FileMaker you're using. Let us know so we can give you the right solution.

[TimDalton]

Looks like Orwell is using version 6 on windows XP in my window. Anyway, I have a similar problem and I'm using 5.5 on Windows 98 (no XP available here or at the client). I'm trying to use a list to populate a pop-up. I have the current relationship that works fine, using a relationship from one file to another. The problem comes when the user wants to see a list showing only a subset of related records that have a "display me in the list" flag set to "Yes". Any ideas?

[Ugo DI LUCA]

Hi,

Say your field in the related file is called cDisplayMeInList

Create a global field, with a value list of "Yes and No"

Then a relationship from this global to the cDisplayMeInList.

Your value list would now need to use this relationship, and becomes, therefore, a related value list.

[Ugo DI LUCA]

Orwell,

In your case, what you need is another relationship based on the concanation of the ClientID AND the Month.

1) In the related file 'Activity', create a calculation cCompoundMonthClientID = Month(DateofActivity) & " "& ClientID.

2) In the Main file, create a global field gMonth and a calculation cUnsoredCompoundgMonthClientID = gMonth & " " &ClientID.

3) For matter of simplicity, use a custom value list with 1,2,3,4,5,6,7,8,9,10,11,12 and attach it to your global field.

If you show your related records from the portal, your list should dynamically update according to the value selected in the Portal.

If you have more than one year, then add the year in both calculations to reflect this also in your filtered relationship.

HTH