Skip to main content

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?

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.


eLeads] table



  • A date field

  • A formula field to calculate the week the lead should be invoiced for, based on the date field.

    IF(Date, DATETIME_FORMAT(Date, "YY-WW"))

  • A formula field to determine if this lead is from “this week”

    IF(Week = DATETIME_FORMAT(TODAY(), "YY-WW"), "yes", "no")

  • One link to the table, as opposed to 4 like in your current setup. This helps to prevent duplicate linking

  • (optional) A rollup field to let you know how many more distributors could/should be linked.

    Target field: {Name}

    Aggregation formula: 4 - COUNTALL(values)


table



  • (optional) a count field for all records linked to the distributor

  • A count field to calculate the number of leads from this week

    Condition: {This Week?} is "yes"

  • A Formula field to calculate the fees owed this week

    {Leads this Week} * Fee


Advanced Solution (no scripting)

The instructions below assume you’ve done all of the above instructions, and have a separate eInvoices] 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



  • A field for the week

  • A formula field to determine if this invoice is for “this week”

    IF(Week = DATETIME_FORMAT(TODAY(), "YY-WW"), "yes", "no")

  • A link to the Distributors] table

  • A lookup of the distributor’s fee

  • A link to the nLeads] table

  • A count field, pointing at the linked hLeads] records

  • A formula field multiplying the number of leads by the distributor’s fee

    {Fee} * {Lead Count}


lDistributors] table



  • A formula field to calculate the current week

    DATETIME_FORMAT(TODAY(), "YY-WW")

  • A link to the lInvoices] table

  • A rollup field determining if an iInvoice] 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



  • A link to the AInvoices] table

  • A rollup that determines if the necessary invoice records for all distributors have been created yet.

    Target field: {Invoice Records Created}

    Aggregation formula: IF(ARRAYUNIQUE(values) = "yes", "yes", "no")

  • A rollup field to calculate the names of the 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



  • Trigger:

    Type: When record matches conditions

    Table: Distributors

    Conditions: {Leads this Week} > 0

  • Step 1: Create record

    Table: Invoices

    Fields:


    • {Name}: cTrigger record > field value > {Current Week}] (space) rTrigger record > field value > {Name}]


    • {Distributor}: iTrigger record > field value > {Name}]


    • {Week}: cTrigger record > field value > {Current Week}]




Automation 2: Link Invoices



  • Trigger:

    Type: When record is updated

    Table: Leads

    Fields: {Format Invoice Names}

  • Step 1: Update record

    Table: Leads

    Record Id: /Trigger record > record ID]

    Fields:


    • {Invoices}: >Trigger record > field value > {Format Invoice Names}]



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.


eLeads] table



  • A date field

  • A formula field to calculate the week the lead should be invoiced for, based on the date field.

    IF(Date, DATETIME_FORMAT(Date, "YY-WW"))

  • A formula field to determine if this lead is from “this week”

    IF(Week = DATETIME_FORMAT(TODAY(), "YY-WW"), "yes", "no")

  • One link to the table, as opposed to 4 like in your current setup. This helps to prevent duplicate linking

  • (optional) A rollup field to let you know how many more distributors could/should be linked.

    Target field: {Name}

    Aggregation formula: 4 - COUNTALL(values)


table



  • (optional) a count field for all records linked to the distributor

  • A count field to calculate the number of leads from this week

    Condition: {This Week?} is "yes"

  • A Formula field to calculate the fees owed this week

    {Leads this Week} * Fee


Advanced Solution (no scripting)

The instructions below assume you’ve done all of the above instructions, and have a separate eInvoices] 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



  • A field for the week

  • A formula field to determine if this invoice is for “this week”

    IF(Week = DATETIME_FORMAT(TODAY(), "YY-WW"), "yes", "no")

  • A link to the Distributors] table

  • A lookup of the distributor’s fee

  • A link to the nLeads] table

  • A count field, pointing at the linked hLeads] records

  • A formula field multiplying the number of leads by the distributor’s fee

    {Fee} * {Lead Count}


lDistributors] table



  • A formula field to calculate the current week

    DATETIME_FORMAT(TODAY(), "YY-WW")

  • A link to the lInvoices] table

  • A rollup field determining if an iInvoice] 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



  • A link to the AInvoices] table

  • A rollup that determines if the necessary invoice records for all distributors have been created yet.

    Target field: {Invoice Records Created}

    Aggregation formula: IF(ARRAYUNIQUE(values) = "yes", "yes", "no")

  • A rollup field to calculate the names of the 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



  • Trigger:

    Type: When record matches conditions

    Table: Distributors

    Conditions: {Leads this Week} > 0

  • Step 1: Create record

    Table: Invoices

    Fields:


    • {Name}: cTrigger record > field value > {Current Week}] (space) rTrigger record > field value > {Name}]


    • {Distributor}: iTrigger record > field value > {Name}]


    • {Week}: cTrigger record > field value > {Current Week}]




Automation 2: Link Invoices



  • Trigger:

    Type: When record is updated

    Table: Leads

    Fields: {Format Invoice Names}

  • Step 1: Update record

    Table: Leads

    Record Id: /Trigger record > record ID]

    Fields:


    • {Invoices}: >Trigger record > field value > {Format Invoice Names}]




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


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




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 rDistributors] 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.


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?

Reply


Terms & Conditions