Formula Assistance I think...;)

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

Ok so here is what I am trying to do. I have a table for Campaigns I want to track the conversion rate of our campaigns as it relates to sales. So there are a couple of fields on this table that I would like to pull from the sales table.

  1. Start Date of Campaign (will be manually entered)
  2. End Date of Campaign (will be manually entered)
  3. # of Sales (formula field using the above details and linked to sales records)
  4. Total Amount of Sales. (formula field using the above details and linked to sales records)

So what I would like to do is this. I would like a formula field for these fields below that would get the following information from the sales table.

  1. # of Sales: ( I would like to go over to the sale table and gather the total# of sales that are within the start date of the campaign and the end date of the campaign.)
  2. Total Amount of Sales: ( I would like to go over to the sale table and gather the total of all sales ($sum dollar amount) that are within the start date of the campaign and the end date of the campaign.)

Any super guru help would be great. Thanks guys.




Photo of Joshua McAfee

Joshua McAfee

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Essentially, all you are trying to do is to easily pull a sale report filtered by particular date brackets.

Because there is not going to be a way to unquiet link a sale to a campaign, and also your campaigns could overlap, I suggest that, you make a a sales report which has an <ask the user> question for the sales date before after a date and before another date.

Then you can build a formula URL on he campaign table to run that report and supply the dates.

The formula would be something like

Urlroot() & "db/" & [_DBID of the sales table from the advanced properties]
& "?a=q&qid=99&nv=2"
& "&v0=" & ToText([Start date field of Campaigns]
& "&v1=" & ToText([End date field of Campaigns]


You will need to replace the 99 with the number of the report that you build.

I don't think there is a way to be able to run a list of your campaigns to see the sales results in a table report.  You will need to basically run a query to see the results.