Views:

Scenario Overview


In this example we are using the 'Urgent Breeder Selling Program' advice on the CattleFacts.com.au website. This sample was built based on their documentation  and revolves around showing how complex business requirements can be implemented in a Multi-Sheet Decision Table.    

They break down cattle stock under the following headings:

Wet or Dry

Is the cow currently producing milk?
Pregnant

Is the cow currently pregnant?

The advice is based on the following:

  • Sell cows which will generate the least profit over the next 12 months.
  • Sell cows which present the greatest drought risk during the next 12 months.



Reasoning:

Dry Empty Cows

A dry empty cow will generate no income until she next weans a calf. Depending on seasonal conditions she may take several months to conceive followed by a 9 month gestation and possibly 8 months before the calf is weaned. All up around 18 months unless you 'early' wean. The most profitable thing is to convert her to money. The money is better in the bank reducing debt or spent on an income producing activity.
Dry Pregnant Cows

The time of the year the calf is born is important in deciding whether to keep or sell these cows. Calves born outside the 'Preferred' period represent less income in terms of calf growth, drought risk and cow re-conception. Each region can identify a preferred time for cows to calve, even if cows cannot be managed to calve in this period.
Wet Cows

Wet cows have the complication of having a calf at foot. They are usually lower in condition than dry cows and therefore less valuable and less saleable. Even so, they should be evaluated on pregnancy status in a similar manner to the dry cows.

A non-pregnant cow which has a weaning age calf at foot could be a sale candidate in 2-3 months once she is fattened. Crop fattening or feedlotting are options to consider. The urgency for cash and the economics of fattening are 'cards to be played' with these cows.

Identifying cows which have just had a calf weaned from them can be difficult. If they have the opportunity, they usually 'show up' at the yards with full udders after the muster. A pregnancy test of this mob could be rewarding in terms of sale options.


Based on this logic they produce a sales rating to be applied to the Cow determining in what order the cows should be sold. 



To implement this in Dynamics CRM we have re-purposed the Contact entity as a Cow.



The following fields have been added to the Cow entity:

  • Expected Muster Date - When you are planning to sell your cows 
  • Lactation Status - Is the cow currently producing milk?
  • Pregnancy Status - Is the cow pregnant?
  • Date of Insemination - When did the cow get pregnant?
  • Expected Calf Birthing Date - When the cow is expected to give birth to the calf she is carrying
  • Estimated Calving Period - Will the cow birth her calf at an acceptable time of the year?
  • Action - This is the suggested action for this cow
  • Selling Order - Suggested sales rating, 1 being sell now, 7 being keep if possible

The estimated calving period is based off the below table:


 

N52 BPA Solution

We use 2 formulas to implement this logic:

Formula 1 is  used to calculate the expected birth date of the calf using the value in the Date of Insemination field.

  • A formula of type Save - Perform Action is created on the Cow entity
  • The Source Property is set to Pregnancy Status
  • The Event is set to Create & Update



Formula 2 is a Multi-Sheet Decision Table that updates the Estimated Calving Period for a pregnant cow and assigns a suggested Action and Sales Rating.

  • A formula of type Save - Perform Action is created on the Cow entity
  • The Source Property is set to Expected Calf Birthing Date and Expected Muster Date
  • The Event is set to Create & Update

This Decision Table Sheet assigns Estimated Calving Periods based on the month the cow is expected to give birth to her calf in all regions of Queensland, Australia. The 'Calving Periods' decision sheet executes first and then the 'Evaluate Sales' decision sheet.
 




The second Decision Table Sheet in this Decision Table is used to evaluate the various conditions and assign the most appropriate Action and Selling Order to the cow


 

Calculations Table


The Calculations Table has 3 calculations to execute as part of this Decision Table:

  • GetMonthOfBirth - Returns the month the cow is expected to give birth to her calf - returns a 0 if not pregnant
  • CalfWillBirthSoon - Checks if the pregnant cow will birth within the next 2 months
  • WeanableCalf - Checks if the cow has any calves currently less than 2 months old


    Calculations

    GetMonthOfBirth
    
    If(ContainsData([contact.new_expectedbirthingdate]), GetMonth([contact.new_expectedbirthingdate]), '0')
    
    CalfWillBirthSoon
    
    If(ContainsData([contact.new_expectedbirthingdate]), if(datediff([contact.new_expectedmusterdate],[contact.new_expectedbirthingdate], 'd') < 60, true, false), false)
    
    WeanableCalf
    
    SmartFlow(
    
        SetVar('WeanableCalf', false),
          
        ForEachRecord(
      
            FindRecords('contact', 'parentcontactid', [contact.contactid], 'birthdate'),
      
        Iftrue(DateDiff(CurrentRecord('birthdate'), [contact.new_expectedmusterdate], 'd') < 60, SetVar('WeanableCalf', true))
            
      ),
      
      SmartFlowReturn(GetVar('WeanableCalf'))
    )