Customer Engagement & Dynamics CRM Forum

 View Only
  • 1.  Lookups and calculations......

    Posted Apr 15, 2022 08:32 AM
    I have a client that wants me to add a couple fields to their Contacts page that will indicate if this contact is in a Disadvantaged Community (DAC) and if the contact is considered Hard To Reach (HTR).

    They sent me a list of the Zip Codes that they consider DAC.   I have created a new entity to hold that information.   Then they sent me a set of IF THEN used for the final determination.   These IF THEN calculations reference 5 different fields.   Three of the fields have been created on the Contacts page, the other two are on the newly created Entity with the Zip Codes.

    Here is what they sent me for the logic to determine the DAC and HTR Statuses (I did not create these, this is what they sent me)

    DAC/HTR Gen Proc Logic 

    HTR Logic: 

    App.Program_Code = "HTR" IF: 

    1. Premise Zip Code = "Disadvantaged Community Zip Codes" AND 
    1. App.Primary_Language = "YES" OR 
    1. app.Number_Employees = "1-10" OR 
    1. app.Rent_Or_Own = "Rented or Leased" 

    OR 

    1. Premise Zip Code <> "Disadvantaged Community Zip Codes" AND = "Metro LA Zip Codes" AND 
    1. App.Primary_Language = "YES" AND 
    1. app.Number_Employees = "1-10" AND 
    1. app.Rent_Or_Own = "Rented or Leased" 

    OR 

    1. Premise Zip Code <> "Disadvantaged Community Zip Codes" AND <> "Metro LA Zip Codes"  

    AND  

    1. App.Primary_Language = "YES" OR 
    1. app.Number_Employees = "1-10" OR 
    1. app.Rent_Or_Own = "Rented or Leased" 

    DAC Logic: 

    App.Program_Code = "DAC" IF  

    1. Premise Zip Code = "Disadvantaged Community Zip Codes" AND 
    1. App.Primary_Language = "No" AND 
    1. app.Number_Employees = "10+" AND 
    1. app.Rent_Or_Own = "Owned" 

    Non-DAC/HTR Logic: 

    App.Program_Code = "Non-DAC/HTR" IF: 

    1. Premise Zip Code <> "Disadvantaged Community Zip Codes" AND <> "Metro LA Zip Codes"  

    AND 

    1. App.Primary_Language = "No" AND 
    1. app.Number_Employees = "10+" AND 
    1. app.Rent_Or_Own = "Owned" 

    OR 

    1. Premise Zip Code <> "Disadvantaged Community Zip Codes" AND = "Metro LA Zip Codes" AND 
    1. App.Primary_Language = "No" OR 
    1. app.Number_Employees = "10+" OR 
    1. app.Rent_Or_Own = "Owned" 

    I worked with Dynamics NAV (on-prem) for a decade at a previous employer and I could have come up with a solution creating a couple of custom views on the SQL server and adding a couple of fields to the form, but I now I am working with Dynamics CRM in the cloud and I just feel like I don't have any of the tools I used previously.

    Questions: 

    (1)   Can I create a simple lookup on the Contact Page that will look at the Contacts Zip Code, and look that up on the new Entity with the Zip Codes and bring back the simple "Yes" or "No" from that Entity as to whether that Zip Code is considered (DAC)?

    (2)  How do I create an IF THEN statement that will reference multiple fields to determine how the contact should be flagged?

    I have done lookups that can pull a sub-grid onto the Contact page, but I need to do actual calculations, not just display data.    Need some guidance on this one.

    Thank you so much in advance!

    Steven R. Hoag
    Microsoft Dynamics CRM Business Analyst

    ------------------------------
    Steven Hoag
    Dynamics CRM Business Analyst
    ICF
    Fairfax VA
    ------------------------------


  • 2.  RE: Lookups and calculations......

    SILVER CONTRIBUTOR
    Posted Apr 18, 2022 05:15 AM
    Hi

    The first consideration is do you want the DAC and HTR fields to update in real time if the App.Primary_Language, app.Number_Employees etc fields change or if you are happy for them to update in the background after the user has saved the record. 

    If you want real time then I would suggest using JavaScript on the client to query the ZIP code table and the other fields and update the DAC/HTR using the logic described.

    if you are happy for it to be done asynchronously on Save of the record you have more options. You could use a Microsoft Flow or Logic App to run on the Save and peform the logic and update the record in background. You could also use a custom plugin.

    HTH

    Jon


    ------------------------------
    Jonathan Farmer
    Business Systems Manager
    SWS Broadband
    ------------------------------



  • 3.  RE: Lookups and calculations......

    SILVER CONTRIBUTOR
    Posted Apr 18, 2022 12:19 PM
    Hello!  
    There are a couple of options to make this happen.  You can either create lookups on your form and use a calculated field to create the and/or/if/then statements OR you can use a background workflow (aka process) that will allow you to make the same process happen.  I would personally try the workflow first since they can be more flexible and you don't have to add all the lookups to the form, as long as you have one field mapping the relationship between the two tables.  
    If you want more details, I can send you more details on building a workflow though Power Automate and what that would entail. 
    Now that you are on the cloud, my recommendation is to not overcomplicate things for the sake of easier maintenance.  Most of what you want to do is available without using SQL or JavaScript these days.
    Good Luck!

    ------------------------------
    Vanessa Lopez
    Black Knight Financial Services
    Vancouver, WA
    ------------------------------



  • 4.  RE: Lookups and calculations......

    Posted Apr 20, 2022 01:49 PM

    Hello everyone,

     

    Thank you for the suggestions and advice.    I knew I could depend on this group to steer me onto the correct path.

     

    I really appreciate the responses.

     

    Sincerely,

     

    Steven R. Hoag | Microsoft Dynamics CRM Business Analyst

    Mobile: 1-262-753-3247 | steven.hoag@icf.com | icf.com

    ICF | Remote Office, Whitewater, WI, USA

     

     






If you've found this thread useful, dive deeper into User Group community content by role