Enforce my Opportunity Totals to match my order totals
I have a project management app that has a CRM aspect to it as well as order management.
The structure for my following question is Projects < Opportunities < Orders <Invoices.
During the sales cycle there are no related orders or invoices. There is an opportunity with a field in it called value to track the revenue for the opportunity. Won the user hits the Win button it fires off an automation to create tasks to various users and one of them is to the Project Coordinator on the project to enter the order in Quick Base. Another one is for the Project Coordinator to build out the invoicing schedule in Quick Base.
Now what I have noticed is my team is often hitting that win button before updating the opportunity with any final pricing modifications in place so the total of orders does not always match the opportunities. I want to enforce this to prevent that from happening. However, currently I do have it set where each opportunity could more than one order (sometimes there are various phases). Long Story short the total of the invoices must add up to the order. Total of the orders must add up to the opportunity value. I need to keep that all in sync.
So thoughts on best ways to enforce this data? I did place a data validation on the opportunities table that says If([Cumulative Total Sell] != Value, "The opportunity does not match the total of your orders. Please update the opportunity value."). I have a check on this to see if the phase of the opportunity is won so it doesnt run on non won opportunities
So that should catch the opportunities table if they save something on there.
However, how do I enforce the orders and invoices tables? Or I guess holistically how do I ensure those numbers maintain as a match throughout the process as the project develops?
Only other option I can think of is removing the sell value from the order table and have it as a lookup from opportunities but I really did not want to do that.
------------------------------
Ivan Weiss
------------------------------
The structure for my following question is Projects < Opportunities < Orders <Invoices.
During the sales cycle there are no related orders or invoices. There is an opportunity with a field in it called value to track the revenue for the opportunity. Won the user hits the Win button it fires off an automation to create tasks to various users and one of them is to the Project Coordinator on the project to enter the order in Quick Base. Another one is for the Project Coordinator to build out the invoicing schedule in Quick Base.
Now what I have noticed is my team is often hitting that win button before updating the opportunity with any final pricing modifications in place so the total of orders does not always match the opportunities. I want to enforce this to prevent that from happening. However, currently I do have it set where each opportunity could more than one order (sometimes there are various phases). Long Story short the total of the invoices must add up to the order. Total of the orders must add up to the opportunity value. I need to keep that all in sync.
So thoughts on best ways to enforce this data? I did place a data validation on the opportunities table that says If([Cumulative Total Sell] != Value, "The opportunity does not match the total of your orders. Please update the opportunity value."). I have a check on this to see if the phase of the opportunity is won so it doesnt run on non won opportunities
So that should catch the opportunities table if they save something on there.
However, how do I enforce the orders and invoices tables? Or I guess holistically how do I ensure those numbers maintain as a match throughout the process as the project develops?
Only other option I can think of is removing the sell value from the order table and have it as a lookup from opportunities but I really did not want to do that.
------------------------------
Ivan Weiss
------------------------------