Help with creating invoice reports

Wyatt_Cavalier · ‎Jul 20, 2021

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:

Help?

Kamille_Parks · ‎Jul 20, 2021

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

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 [Distributors] 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)

[Distributors] table

(optional) a count field for all [Leads] 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 [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

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 [Leads] table
A count field, pointing at the linked [Leads] records
A formula field multiplying the number of leads by the distributor’s fee
{Fee} * {Lead Count}

[Distributors] table

A formula field to calculate the current week
DATETIME_FORMAT(TODAY(), "YY-WW")
A link to the [Invoices] table
A rollup field determining if an [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

A link to the [Invoices] 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}: [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

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}]

Wyatt_Cavalier · ‎Jul 21, 2021

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

Kamille_Parks · ‎Jul 21, 2021

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.

Wyatt_Cavalier · ‎Jul 27, 2021

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?