Thanks for the reply @KVachon. I actually did what I think you’re recommending, in reverse (i.e., I brought the marketing campaign dates into the product table because the goal is to see ALL product deliveries on the calendar, not just ones that have been allocated a marketing campaign yet).
I even got the view that I wanted quite nicely (the green are planned go live dates of campaigns, the blue bubble an expected delivery of an item that same day):
But, the challenge became that some products map to multiple campaigns and when I do a lookup the products with multiple campaigns aren’t added to the calendar because there are now comma separated start and end dates.
Here’s a screenshot of what I mean:
The Chill and Sour map beautifully to the calendar. The 65 Roses record doesn’t map at all.
Any thoughts? It feels so close…
Ahh gotcha,
So this may be a little backward, but if you are linking the two, you can use the marketing table as your “source.” Then group by your product id. Then what you’ll see are the campaigns that are tied to that product ID.
Take a look at this screenshot. That may be what you’re looking for. If you need the opposite you can do that, but it’s easier to keep the table with the different dates as the “source.”
Marketing Table:
Product Table:
@kvachon Thanks for working with me on some suggestions. I tried the rollup approach and then realized I could just use the original lookup fields and only select records that have occurred during the past month or so as that’s really the time frame we’re realistically looking at in this view right now. Not exactly foolproof if we have promotions that hit a product more than once in a given month but gets me mostly there. I think the best solution is to actually split the lookup column up by the comma and putting those different date fields onto the calendar view but when I do that I get a result that looks like a date but apparently isn’t a date and then cannot be used in the calendar view.
