For starters, I’m relatively new to Airtable and this is my first time asking for assistance. Please feel free to explain things to me like a 5 year old. I’m pulling data from multiple sources, Sheets being the main one. I’m looking for help with Airtable’s scripting automation for a field titled Contribution Amount. For context, this is for a freelance project for a company that offers cleaning service subscriptions.
There’s three main tables- blocks, Subscribers, and Active Subscriptions (junction block of the first two). Each block has a cleaning Budget (Budget field) that needs to be achieved, subscribers on a block pledge an amount (Pledge Amount field) they are willing and able to pay, pledges are pooled (Total Pledge Rollup field) and the funding threshold (Threshold Milestone field) that the block has achieved is noted. When a block surpasses a funding threshold (Numeric Value of % Threshold field is used here so a percent is not) that value is tallied in the Pledge/Threshold Difference field then dispersed back to the subscribers. This will be the Contribution Amount in which I need assistance. Please ignore the Max Donation, Current Donation and Savings fields.
Basic guideline is equitable distribution. Contribution Amounts are calculated to be as fair and even as possible without exceeding anyone's individual pledge. Smallest pledges should be tallied first- if the total cost to fund the block (Numeric Value of % Threshold) cannot be covered by equal contributions, smaller pledges are applied first, and the remaining cost is distributed among neighbors with higher pledges. Screen shot 5 for example, has a $23 value for the Pledge/Threshold Difference ($73-$50 = $23). The two largest pledges of $20 have the Pledge/Threshold value applied first- $10 each. This value then matches the next largest Pledge Amount, and the remaining $3 is then applied to the largest three pledges. Please note that the Contribution Amount sum adds up to the Numeric Value of % Threshold- in this case, $50.
As noted, there’s a checkpoint system: the entire service is considered fully funded when the pooled contributions meet or exceed a certain checkpoint value (Numeric Value of % Threshold, e.g., $200). Regular recalculation: whenever a new neighbor joins or leaves, contributions are recalculated to ensure fairness. Any savings from new subscribers are used to reduce the rates for other neighbors, saving everyone money each month.
I’ve attached the Airtable screenshots (Screenshot 1, Screenshot 2) and three examples of what Pledge Amount and Contribution Amount scenarios look like (Screenshot 3, 4 and 5) via Sheets. Even if unable to provide code for the script, a walk through of how to better explain this logic to ChatGPT for assistance would be a step in the right direction. Let me know if further explanation is needed. Your help is greatly appreciated!
