Hey there! I have a common issue with our clients that we onboard into the CRM that I'm hoping to solve with a custom function.
Most of our clients work of a 50% deposit upon contract signing and the final 50% after the delivery of services.
The goal for our clients is to be able to keep track of what deposits they've collected over a period of time (year, quarter, month, and week) along with what's anticipated over a period of time (year, quarter, month, and week).
We thus, have the following fields to help in the Deals Module:
1st Deposit Date: This is the date the first deposit is submitted
Deposit Amount: This is the amount of the first deposit
Final Deposit Date: This is the date the final deposit is expected (or submitted)
Final Deposit Amount: This is the amount of the final deposit
The challenge arises when creating KPIs under Anayltics to keep track of anticipated deposits over a period of time along with total deposits made within a period of time.
I then thought if we add currency fields that are controlled by a function, we may be able to report on this. So we created the following fields:
Anticipated Deposits...
This Year
This Quarter
This Month
This Week
Total Deposits...
This Year
This Quarter
This Month
This Week
The idea is that every day a function (or multiple functions) can run that recalculates this and maps the value to one of each of those 8 fields. I'll then be able to create KPIs in Analytics to sum anticipated deposits and total deposits made over a period of time.
I'm not a pro at deluge yet, so in order to help wrap your heads around this, here's a "plain language" equation I was hoping we could get help with:
Anticipated Deposits This Year/Quarter/Month/Week
If 1st Deposit Date is within this year/quarter/month/week and in the future {
futurefirstDeposit = Deposit Amount;
}
else {
futurefirstDeposit = 0;
}
If Final Deposit Amount is within this year/quarter/month/week and in the future {
futurefinalDeposit = Final Deposit Amount;
}
else {
futureFinalDeposit = 0;
}
anticipatedDeposit = futureFirstDeposit + futureFinalDeposit;
map to Anticipated Deposits This Year/Quarter/Month/Week;
Total Deposits This Year/Quarter/Month/Week
If 1st Deposit Date is within this year/quarter/month/week and in the past {
pastfirstDeposit = Deposit Amount;
}
else {
pastfirstDeposit = 0;
}
If Final Deposit Amount is within this year/quarter/month/week and in the past {
pastfinalDeposit = Final Deposit Amount;
}
else {
pastFinalDeposit = 0;
}
totalDeposit = pastFirstDeposit + pastFinalDeposit;
map to Total Deposits This Year/Quarter/Month/Week;
NOTE: Whereever you see "Year/Quarter/Month/Week", I recognize we'll need 8 functions total. One for each of the time frames of anticipated deposits and total deposits.
Here's a video breaking down everything if that's easier to understand what we're trying to do: https://www.loom.com/share/2ff774c33bb24304ba615800cc4b24bd