I’m running a program for rebates related to project work on homes. The issue I’m running into is how to track the rebates over multiple projects. It’s not as simple as just keeping a running total unfortunately, there are several factors that determine what the available rebate amount is and how it is applied. 

I have it all figured out for single projects with everything calculating correctly, so I know exactly what the rebate would be if the full rebate limit is available. However, when I have more than 1 project and a rebate is being spread out between multiple projects is where I’m having issues.

I’ll try my best to describe everything below. Unfortunately, due to the sheer complexity of the program and base, plus lots of personal data, I can’t create a copy of the base to share for someone to look into directly, but let me know if there is anything I can provide that would help.

Here are the details:

• Rebates are by address
• There are individual rebate limits, plus a total rebate limit, but for purposes here I’m going to focus on just one rebate as I’ll be able to apply the same solution to each one
  • The rebate I’m going to focus on is for electrical work and the limit is $2500
• Factors to determine the rebate amount
  • Each home is in one of two buckets; Low & High
    • Low is the lesser of 100% of the wiring cost or $2500
    • High is the lesser of 50% of the wiring cost or $2500
  • The total wiring project cost
  • Other program rebates
  • Any rebate funds already used (this is where I’m running into the most issues, more on this below)

Example:

Project 1 for Address 1

• Low Bucket
• Project cost: $1500
• Other Program Rebates: $0
• No rebate funds used previously
• Rebate for this project would be the full $1500

Project 2 for Address 1

• Low bucket
• Project cost: $2000
• Other Program Rebates: $250
• Previous rebate funds: $1500, leaving $1000 available
• The rebate should be $1000 (the combined total of the primary rebate and the other rebate do not exceed the project cost. If they did, the primary rebate would need to be reduced to not exceed the total cost)

For project 2, the fields and calculations I’ve had show this project has the full $2500 available for the rebate and if I did not catch that we could accidently approve a project with a primary rebate of $1750, plus the other rebate of $250 totaling $2000. However, that would be incorrect and could be easy to miss when projects are months or even years apart. 

What’s I’ve attempted

• My first step was to create a new table for the addresses. This allows me to link by address instead of trying to find previous projects and link them, this way it’s automatically linked when the record is created
• I built a Rollup in the address table to calculate the rebates for all records based on certain conditions (Approved status, Selected Estimate checkbox)
• Here’s where I’m stuck. I need to only have any previous projects that meet the Rollup conditions apply to the new project.
  • I keep getting a circular reference error in my formulas and I’ve used the AI to try fixing it, but so far I’ve not been able to figure out the right prompts

Here’s the latest prompt I tried with the AI, but it could not figure it out. 

Create a field that determines what the rebate is for each electrical wiring record. You can use the SOW & Invoices table as well as the Project Addresses table to create fields as needed.

Step 1, determine what the total electrical wiring rebates from previously reserved projects are by summing up Electrical Wiring IRA Rebates for records with the same linked Project Addresses, that have the Selected SOW checked, where the Selected SOW modified date is before the current records date or if no modified date, then ignore this check, that have a status of Approved or Finalizing Rebate Invoice or Completed,  and a Project Status of SOW EA Approved, 3 Day Wait, Installation Approved, Installation Scheduled,  Install in Progress, Change Order, Inspection Needed, Inspection Scheduled, Inspection Rescheduled, Final Document Review, Project Review, State Approval, or Project Complete.  

Step 2, determine what the available rebate is by subtracting the amount in the first step from 2500. If this amount is greater than 0, reduce the max rebate of 2500 by this amount. The available rebate should be no less than 0 and no more than 2500

If it helps, here is a workflow of how this should be calculated.