Jul 20, 2021 07:31 AM
I run a company that sends qualified leads to hardware distributors.
Someone gets in touch with us via our site, and pass on their information to four distributors (out of several dozen, we pick the four best ones).
Every week, we invoice each distributor for the number of leads we’ve sent them.
So the records for a given week might look something like this
What I want to be able to produce, is a view that shows how many leads each distributor had, and how much they owe.
So from this view above, it should return:
Complete Vending Services | 2 Leads | £100
Full House Coffee | 1 Lead | £50
Esprada | 1 Lead | £50
Underdog Coffee Company | 2 Leads | £0
Caffia Coffee Group | 1 Lead | £0
GMS ESpresso Ltd | 1 Lead | £0
Help?
Jul 20, 2021 01:24 PM
Simplest Solution
The instructions below will show each distributor’s owed fees for the current week, and will reset with each new week as time goes by.
[Leads]
table
IF(Date, DATETIME_FORMAT(Date, "YY-WW"))
IF(Week = DATETIME_FORMAT(TODAY(), "YY-WW"), "yes", "no")
[Distributors]
table, as opposed to 4 like in your current setup. This helps to prevent duplicate linkingTarget field: {Name}
Aggregation formula: 4 - COUNTALL(values)
[Distributors]
table
[Leads]
records linked to the distributorCondition: {This Week?} is "yes"
{Leads this Week} * Fee
Advanced Solution (no scripting)
The instructions below assume you’ve done all of the above instructions, and have a separate [Invoices]
table to keep a log of each week’s owed amounts by distributor. A less complicated version of these instructions could be made using scripting.
[Invoices]
table
IF(Week = DATETIME_FORMAT(TODAY(), "YY-WW"), "yes", "no")
[Distributors]
table[Leads]
table[Leads]
records{Fee} * {Lead Count}
[Distributors]
table
DATETIME_FORMAT(TODAY(), "YY-WW")
[Invoices]
table[Invoice]
record has been created for this week for this distributor, assuming this distributor has been linked to a lead (otherwise there’s nothing to bill for, and no invoice is needed).Target field: {This Week?}
Condition: {This Week?} is "yes"
Aggregation formula: IF(AND(COUNTA(values) > 0, {Leads this Week} > 0), "yes", "no")
[Leads]
table
[Invoices]
tableTarget field: {Invoice Records Created}
Aggregation formula: IF(ARRAYUNIQUE(values) = "yes", "yes", "no")
[Invoice]
records to link to.Target field: {Name}
Aggregation formula: IF(AND(values, {Week}, {Invoice Records Created} = "yes"), {Week} & " " & ARRAYJOIN(values, ", " & {Week} & " "))
Automation 1: Create Invoice Records
Type: When record matches conditions
Table: Distributors
Conditions: {Leads this Week} > 0
Table: Invoices
Fields:
{Name}
: [Trigger record > field value > {Current Week}] (space) [Trigger record > field value > {Name}]{Distributor}
: [Trigger record > field value > {Name}]{Week}
: [Trigger record > field value > {Current Week}]Automation 2: Link Invoices
Type: When record is updated
Table: Leads
Fields: {Format Invoice Names}
Table: Leads
Record Id: [Trigger record > record ID]
Fields:
{Invoices}
: [Trigger record > field value > {Format Invoice Names}]Jul 21, 2021 03:25 AM
This is so helpful, thank you! I think I’m almost there. Just a few questions?
For this bit, {leads this week} doesn’t appear in the formula bar, and I get an error when I try to use it.
It looks like this is referencing itself?
Can’t do this until I figure out above
Jul 21, 2021 09:47 AM
Rollup formulas don’t autocomplete field names like formula fields do. The instructions above are exploiting a “hidden” trick that you can write regular formulas in a rollup, just to minimize the number of fields required for this solution.
In your [Distributors]
table, the {Invoice Records Created}
rollup field is only referencing the {Leads this Week}
field. Its target field for the values
property is the {This Week?}
field from the [Invoice]
table. Also make sure your field names are correct in your formula since rollups won’t autocomplete them for you. Make sure that’s your setup, and let me know if it is and you’re still getting an error.
Jul 27, 2021 01:34 AM
Thank you so much! One thing that’s not quite working properly. The “Leads” colulmn in the “Invoices” sheet doesn’t automatically populate with the leads that need to go into it
Is there a way I can fix this? or have I done something wrong?