Filtered list using calculations from both sides of a table relationship
Posted on 2007-07-30
In my wine database each wine record is connected to its related varietal, country, pricepoint, classification, and supplier tables. Each varietal, supplier etc, has a unique code. Each wine has a code that concatenates the codes from each of its five related table.
Each contact in my database can have a selection of prefered wines, or types of wines. The preferences are tracked as line items related to that contactid.
Each line item can have 1 to five elements (varietal, country etc) concatenated from its related tables like the wine records.
The preferences are related to the wines through each respective concatenated code.
The problem is this, If 1 preference is italian red wines under 20, the portal should show only wines that match all 3 criteria, even though the preference code will only partially match the wine code, which also includes a supplier and varietal component.
The second issue is that the the related wine list should show italian reds under 20, NOT all italian wines, all red wines, and all wines under 20.
This list can have multiple preferences, which means, that a second preference could include all Shiraz wines over $100. I know I would need to come up with some calculation on 1 or both sides of the relationship to filter the wine list properly, but I am not sure what it should be.