My master data is in a SharePoint list. In Excel I can set formulas that calculate fields to tell me if the stored date (in this case Date of Last Inspection) is within a certain parameter (last 365 days). And if it is not, the Excel formula can calculate that fact and turn the 'Compliance Status' text to a 'Red' and/or 'Non-Compliance', indicating a deficiency. Is there such a formula or rules/query capability in InfoPath/SharePoint.
Synopsis: Can InfoPath/SharePoint query and calculate existing data in fields to determine compliance of an action; and where there is no compliance, automatically change the formatting of the existing data 'Compliance Status' to Non-Compliant'; indicating the lack of compliance - i.e. change the auto populated field to the proper choice (Compliant/Non-compliant, based on the existing date in the Compliance Status field and the system current Date.
Example: I entered an inspection report last June (2014). I go into the form today and prepare to enter a new report of this year's annual inspection. When I entered the data last year it was 'Compliant'. Now, I more than 365 days have passed since the last inspection. When the form is opened, the auto populated text in the "Compliance Status' field, based on today's data and the date of last inspection, should be 'Non-compliant'; even though the text originally read 'Compliant' when the data was originally entered.
Can this be done, through a rule/query/or calculated value field? Or, can it not be done at all?
Thank you,