Viewing dates from two sources on one view

[Justin_Eppley]

I have one table with a list of products and their delivery date. I have another table with a list of marketing campaigns and a “Go Live Date” and a “End Date”. I simply want to have this information together in one view so it’s easy to tell what products are getting marketing love and which are not. But, I can’t seem to find a way.

The point is to on any given week to see what marketing activities are occurring and what products are arriving in store.

Any suggestions?

Thanks in advance!

[KVachon]

Hey there Justin,

Is there a way to link your marketing campaigns to the product table? If you do that, you can setup a Lookup field to pull in the delivery date. That way, your marketing table will have your Go Live, End Date, and Delivery Date in one place.

[Justin_Eppley]

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…

[KVachon]

Oh that’s perfect!

If you already made the association, you can go to the marketing view and group by the Product ID lookup field. This will show every product that’s part of that campaign with the information that’s associated to the campaign.

You can, of course, do the opposite if that’s better for you to view.

Let me know if you can get that built.

Kyle

[Justin_Eppley]

Hey Kyle @KVachon –

I think I might have made the second part of my reply to you too long. The opposite is definitely the desired view but I have the problem outlined in my earlier replay (i.e., product Ids with multiple campaigns and thus multiple go live and and end dates that don’t appear to map to the calendar view at all).

Any ideas?

Thanks,
Justin

[KVachon]

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:

[Justin_Eppley]

@KVachon Thanks for the suggestion. Not exactly what I’m looking for… the screenshot I shared earlier in the thread with the pretty color-coded calendar is what I’m trying to achieve (i.e., on the calendar view). Or even a timeline view or Gantt. Anything visual. The audience that is using it is allergic to a table… shocking, but, true.

[KVachon]

Well that’s unfortunate :joy:

So if you have something already working with just the multiple dates being an issue, could you potentially use a rollup function to pull the minimum/maximum date using a condition (i.e. go live date greater than today)?

Check if that would work for you.

Otherwise, the other option would be to use a third table and combine all the data into a single table using unique campaign names / products.

[Justin_Eppley]

@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.

[KVachon]

That date is in ISO8601. You should be able to use the DATEFORMAT formula function to make it a date.

Glad you found a somewhat acceptable solution.

You’ll probably think of a way to solve it randomly down the line as it’s normal.