Rollup decimal field - How to filter related records based on a lookup column
As Mark suggested, the rollup itself is not instantly updating, but if this is not necessary, I would suggest this approach which uses standard functionality:
- add a new colum to your Table 2 "cost of specific product type"
- calculate this column with a synchronous workflow: 0 when the product has not the specific product type, otherwise copy the cost from the standard cost column
- use for the rollup field the new column "cost of specific product type"
This is a simple solution that should solve your problem.
The other more complex options are:
- implement a synchronous plugins to calculate the rollup field. There are several plugins neccessary which must run on create, change of cost or product type and also on delete of a Table 2 row. When the record of table 2 can be relinked to another row of table 1 both rows of table 1 must be refreshed. The problem of a plugin is the 2 minute limit, when there are many records in table 2 linked to one record in table 1, this limit can be reached.
- implement cloud flows doing the calculation. You have to recognize the same cases as with the plugin. The issue here is, that cloud flows are running asynchronously and the users have to wait and reload to see the calculated value. Here is no 2 minute limit.
When the calculated number is only necessary for reporting, Power BI should be a good way to handle it.
