web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Advanced Find Assistance
Microsoft Dynamics CRM (Archived)

Advanced Find Assistance

(0) ShareShare
ReportReport
Posted on by Community Member

Hello,

       I'm currently in the design process of a database that I'm building for my office and I'm having a verydifficult time trying to build an Advanced Find to meet my needs.  I'm sure that this Advanced Find isn't anything out of the norm, but I have no database or IT background so I'm at a loss.

        I work in a Labor Relations office and we do not service the public; instead, we handle referrals for potential discipline for our employees.  Our work process starts when a referral is sent to us and our Secretary will be logging in the referral information.  What I'm hoping to accomplish is that if she enters the unique Employee ID Number into the system, it will automatically pull in information from multiple related Entities.

         I've attempted to normalize the data for our database into multiple Entities because it was explained to me to be a best practice.  By doing this, I have the following Entities with their Fields within the parentheses:

Referral Information (Referral Received Date, Subject Matter, Requested By, Employee ID Number)
Employee Name (First Name, Last Name, Employee ID Number)
Employee Address (Street Address, City, State, Zip Code, Employee ID Number)
Employee Work Information (Office Location, Unit, Title, Union, Employee ID Number)

         

       The Employee ID Number is hopefully the key that links all of these Entities together.  I'm trying to create an Advanced Find / Query that can recognize the unique Employee ID Number being entered into the Referral Information Entity, and then pull all of those related fields to auto-populate that data.

         Can anybody assist me in setting something like this up without using any external software (I don't have access to Visual Studio).  Is it possible to do this with the built-in Advanced Find feature?

Thank you in advance!

-Brandon

*This post is locked for comments

I have the same question (0)
  • Verified answer
    ThomasN Profile Picture
    ThomasN 3,190 on at

    Hi Brandon, Thank you for reaching out.

    I am not sure what you need advanced find for, but I guess you are trying to create a view to show these details. I will try and help provide a solution but your data structure in CRM will need corrected for advanced find to work the way you want. I would recommend combining Employee into one record it will make things easier.

    1. Normalizing databases are good for traditional data structures. Dynamics CRM is a little more complicated than this and true normalization is not necessary. Please make sure you are using the CRM Customizations interface to create Entities and fields in the database. NEVER edit the database directly. So if you are using the interface then design should not be focused on the database but the business needs.
      1. Business Need: Referral Information form needs above attributes you listed
      2. Solution Using your Data Model Above
        1. Create Lookup to Employee Name, Employee Address, and Employee Work Information
        2. For each lookup
          1. Open the lookup field
          2. On left navigation select mappings.
          3. Use this to map the fields from the Employee to the matching Referral Information fields you just created
    2. Solution if you combine employee into one record
      1. On Referral Information entity create a field for each attribute listed above (referralReceivedDate, SubjectMatter, FirstName, StreetAddress, OfficeLocation, etc..).
      2. Create a Lookup field for Employee
          1. Open the lookup field for Employee
          2. On left navigation select mappings.
          Use this to map the fields from the Employee to the matching Referral Information fields you just created

    That will auto populate the other fields from the related record onto the Referral Info record. If you do not want to duplicate that data on the referral information record but just display in a view, that is going to be limited with the way you have it set up.

    • Using Your Data Model
    • Look For Referral Information > You can only display columns from records directly related to referral information.
    • Look for Employees > Now you can have a list of employees and their one to one relationships. I would assume you are not going to have One employee with one referral. So you have to have a list of Referrals that contains employee details. To do that each employee needs a One to Many relationship with Referral. Then in the view you can add columns from the employee.

    Sorry if this is confusing. It is a tough subject to get your arms around. Please ask questions if you have them, and I apologize if I completely missed your intent. I am eager to help. Have a wonderful day!

    -Tom

  • Community Member Profile Picture
    Community Member on at

    Thank you very much for this thorough response.  If this structure is complicating the process, I'd be much happier to consolidate the information.  I think I will give that a shot and see if the office agrees that it would be acceptable.

    I have a brief follow-up question, if you have a chance.  If I set up the Lookup Field that pulls in the information from the Employee Entity I want to make sure that the data would be a one-time pull.  What I mean by that is that if our Secretary creates a Referral for Employee 1, that employee's information would only get pulled at the time the Referral was being created.

    I believe this has to do with the cascading rules, is that correct?  If I set up the Type of Behavior to Referential and Assign, Share and Unshare to "Cascade None", would that ensure that information would not change if the employee's information did?  

  • Verified answer
    ThomasN Profile Picture
    ThomasN 3,190 on at

    Yes the data is populated that one time on creation. The Referential behavior handles the actual relationship between the entities. Cascade None would not make any changes on delete, reassign, share, etc. Of course always test this to make sure it does not update after creation.

    There are some caveats to this. This mapping is only when New Referrals would be created from the Employee record. That may not be how your process works.

    The alternative would be to still create the matching fields in Referral Information then create a workflow process that kicks off on creation of new Referral Information record. That interface is fairly simple and there are plenty of guides how to do that. In that you can map each field to populate with values from Employee record. Not sure why I didn't think of this one first.

    Field Mapping Info: www.microsoft.com/.../map-entity-fields.aspx

    Hope these help. Have a great day!

    -Tom

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Field Mapping and Cascading Behaviour are not really linked, other than the fact they both have to do with relationships.

    Your model sounds over-complicated - I think you need one entity for the Employee with all their information, and one for the referral, with a lookup to the Employee.

    You can configure the Employee Quick Find View > Find Columns so that the employee name, email and ID number are all search columns. Then in the lookup field on the referral you could type any of these and hit "tab" and it will resolve to the correct record.

    You have been advised not to de-normalise the data, which is good (in general), and you have tried to build a model to achieve this. But then you talk about copying the employee data to the referral records, which is exactly what denormalisation is! So don't copy the employee data anywhere at all (tip: this also makes it easier to secure so only the right people can see it).

    Instead, create a Quick View form for Employee, with the fields you need to see on the referral. Then on the Referral form, insert the Quick View form you just created, linked to the Employee lookup field, so when you choose an employee it displays the relevant details (you can see an out-of-the-box example of this on Contact, showing Account details, and on Account, showing Primary Contact details).

  • Community Member Profile Picture
    Community Member on at

    Thank you for this great response.  

    I agree that the model I'd started with is over-complicated and I've made efforts to simplify and centralize the data.  Currently, I'm planning on keeping all the employee information together in one Entity.  I've also consolidated my Entities for Referrals and Notices of Discipline into one Entity and I'm currently working on setting some fields to hidden until they are needed (if they are needed).

    I really appreciate you taking the time to assist me with this.  I like the Quick View solution to linking relevant data much more than what I'd tried to run with initially!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans