I am struggling with how to create a bonus calculator for some members of my team. Here is my schema and what I am trying to accomplish:
- App1: Team Elite - This is our ERP/Project Management / Project Billing App
- App2: eHR = Our HR app containing compensation etc.
For security reasons I keep these as two separate apps.
- Team Elite Table Structure used in this scenario
- Projects < Opportunities < Orders < Invoices
- Total Invoice is summarized all the way up to Project Level
- Team Members is a table for all off our employees
- eHR Table Structure
- Team Members (from Team Elite) < PM Bonus (eHR app)
Now what I am trying to do is when I create a record in eHR on the bonus table for one of my Project Managers have it populate all of the projects assigned to them (there is a Project Manager field on the Projects Table related to Team Members). For each of those projects I want the ability to add some fields or reference fields. Here is the data I need:
- Total Job Value (can pull this data from Projects Table in Team Elite)
- Invoiced Total (can pull this data from Projects Table in Team Elite)
- Invoiced Last Two Quarters (can pull this data from Projects Table in Team Elite)
- Job Status (can pull this data from Projects Table in Team Elite)
- Estimated Profit (can pull this data from Projects Table in Team Elite)
- Actual Profit (I want to manually add this data)
What I am trying to do is have Quick Base show me a report by Project Manager of all of the projects they are engaged in with the above fields as a column. I would then create a formula numeric field to calculate the bonus to them (it is profit based). The challenge I am having is the Projects table in quick base is my main table, its big.
I dont think it is a cross app reference structure wise, and making it a sync table could work but it means I am pulling a lot of data across into a copy in another app.
What is the right structure to get this data right? I would love for it to be real time but I guess that is not essential.
My only other thought was to set a pipeline that would run quarterly and populate records in the eHR app but that sounds clunky to me.