In this scenario we have a custom field on the Account form called 'Oldest Contact' which needs to be populated. The business rules states it should be populated with the oldest contact by Age linked to the Company.
The business rule should be set-up so each time the birthday datetime field changes on the contact record the calculation should run & set the field on the Parent Account record.
N52 Formula Manager Solution
The Formula Manager solution works like this,
- A formula of type 'Save To Parent' is created
- It is configured so that each time the birthdate field changes on the contact record it executes
- On execution it performs a fetch-xml query to find all the related contacts sorted by the birthdate field
- As the set of contacts are sorted by birthdate we know the first one in the list is the oldest
- So we set the 'Oldest Contact' field on the parent account with the first contact in the set of contacts via the FindValueFD() function which always accesses the first row of a set of data
N52 Formula Manager Steps
The following set of steps outline how to create this Formula
- Create a new formula of type 'Save To Parent'
- Set the Source Entity to 'Contact'
- Set the Source Property to 'Birthday'
- Set the Relationship field to 'parentcustomerid (account)'
- Set the Target Property to 'Oldest Contact'
- Copy & paste the formula below into the formula
- Click save
- Create a new formula details \ fetch-xml query record by clicking on the 'Plus' icon in the subgrid.
- Give it a name of 'AllContacts'
- Copy & paste the fetch-xml below into the query field
- Click Save
- You are now ready to test
if( ContainsData(FindValueFD('AllContacts', 'contactid', '?') ) , Setlookup( FindValueFD('AllContacts', 'contactid', '?') ), 'NoOp' )
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="contact"> <attribute name="fullname" /> <attribute name="contactid" /> <attribute name="birthdate" /> <order attribute="birthdate" descending="false" /> <filter type="and"> <condition attribute="birthdate" operator="not-null" /> <condition attribute="parentcustomerid" operator="eq" value="@parentcustomerid@" /> </filter> </entity> </fetch>
Wizard - FindValueFD
Please see below the wizard you can use to create the FindValueFD() function call used in this formula.
Note to find the value for Formula Details (Query ) , you will need to go to the Fetch- Xml tab and create the necessary Fetch Xml shown above.