I have two tables customers and payments.

In customer’s table I can see the total amount of payments of a customer.

What I want to do is to add a date field to the relationship to filter the data shown.

I.e.

If date field is empty in customer’s table, all payments and their totals are shown but if I place 2011, only those payments are shown and only the totals for that year.

I know it must be simple, but I have to admit it I am completely lost with relationships.

I kind of self answer.

I don’t know if it’s too rudimentary but simply after a few hundreds trial and error, generate and test, lol, I could have solved what I was trying to accomplish.

FilteringByDate.zip

Almost. You have the concept almost correct. To filter a relationship, you use a global field on the left-hand side of the relationship. However, the portal and the fields within the portal, must be based on the table occurrence that represents the right-hand side of the filtered relationship. So, in your example, you need to change the portal and the fields with it to the Payments||Year table occurrence. Then, changing the gYear in Customers will filter the portal records.

This can also be done directly with a filtered portal.

What you are struggling with is the more complex requirement you've stated in your first post. Filter or Show All. To do that you define another layer of logic.

Thanks for your help bcooney.

I had what you tell me in my sample file, but I changed it because there's two differente relationships based on date, so I'd need two different portals to show records (I don't know if it can be done in other way). By now I only need the total amount filtered, so that's why I used "general relationship" portal.

What I'm working on now is, what if I want to filter some customers to offer them a VIP offer?

I have implemented two more tables: VIPOffers and VIPOffeLines, and two more fields to customers table: MinimumAmout and VIP.

What I would like to do is when I do a VIPOffer, select only those customers that are "offerable" depending on the MinimumAmount criteria.

How could I use Date from VIPOffer to filter what customers are "VIP" that exactly day or date?

FilteringByDate2.zip