Bill to Contract comparisons

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered
I am looking to create an app that will compare bills to the service contracts. I have a table for the Contracts and one for the bills with a relationship that shows that the contracts can have multiple bills. I need to create some sort of Automation or report that will compare the bills to the contracts and only return the bills that are greater than the contracts. What would be the best way to do this?
Photo of James Swett

James Swett

  • 70 Points

Posted 11 months ago

  • 0
  • 1
Photo of Everett Patterson

Everett Patterson

  • 1,058 Points 1k badge 2x thumb
There are many ways to do this, but here is one using simple native features.

On the relationship "Contracts have many Bills", create a lookup field [Contract Price] so we can carry that value down from the parent table to the child table

On the Bills table, create a new formula field [Price Variance] that calculates [Contract Price] - [Bill Price]

On the Bills table, create a new report and use the [Price Variance] as a filter (is greater than 0) and this should provide you with all Bills that are greater than the contract price. Setup report subscriptions with the new report for automating the reports via email.