Unanswered: Querying/filtering across related forms etc
I've been experimenting with send email templates.
In this scenario I have the following:
1. A form with details about items that are for sale. These are always one-off items.
2. A form with contact details of buyers of these items.
3. I have a relationship set-up so that on the items form I can select the name of buyer from the contacts form and on the contact details form a list of the items a particular buyer has bought is shown. This really should be a one (buyer) to many (items) relationship though BD only seems to distinguish between "one 2 one" or "many 2 many".
Anyhow, to get to my query. I have set up an email template so that I can generate an email to each buyer thanking them for their recent purchase blah blah and listing what they bought. Now, if they have bought more than one item, all the items that they have ever bought will be listed. The question is, is there anyway of limiting this, so I could just include items bought in the last week. I have a sold date on the item form and can show that alongside the items on the contacts form but there appears to be no way of filtering that data prior to sending the email. Also, the only field from the items form that shows in the e-mail is the Title field, but what if I want to show other fields in the e-mail from that form, how do I specify those?
I suppose this can be widened to a more general question about querying data on fields that spread across related forms. Suppose I wanted to query a list of people who had bought items between a date range, how would I do that? The list of people are on one form, and the items with the sold date on another. This should be basic stuff in a relational database, but I can't figure this out in BD.