I need to create a calculated key field that will link to records in a child table.
As the client designed the solution, the child table records have a field for the date of the sale (also a Month field and a Year field which are calculated from the date of the sale field). There is a key field that links to the vendor's record. The problem is that the relationship links to EVERY vendor's record. The client wants to link only to those records in the current contract period.
Every vendor record has fields for the start and end dates of the current contract. All of them span several years and cover twelve consecutive months, but not every contract year begins in January and ends in December. For example:
- Vendor #1's current contract year may be from January 1, 2016 through December 31, 2016.
- Vendor #2's current contract year may extend from June 1, 2015 through May 30, 2016.
- Vendor #2's upcoming contract year may extend form June 1, 2016 through May 30, 2017.
If all the client wanted to do was display the record in a portal, I'd simply use a filtered portal. But complicated formulas to determine things like dealer incentives depend on only sales from the current contract year. So, I need a relationship that will apply just to the child records that fall within that range. If every contract year corresponded with the calendar year (like Vendor #1 in the example above) it would be easy. I would link on the vendor ids and the current year. But, for Vendor #2, I would need to include records for the last six months of the previous year. And, over time, there could be vendors with different contract start and end dates. Though, the client tells me, all contracts so far have spanned multiple years. I'm looking for one method that covers everything.
I've been thinking along the lines of an unstored calculated field in the vendor table that determines all the month and year combinations from the contract start and end dates and expresses them as a carriage return delimited list. Something like this ...
... and so on, which would be linked to the month and date of the sales table records. But I wonder if I'm making this too complicated. I would be grateful for any other ideas.