Relationship Mapping between same Look-Up Fields between Entities not working

(0) Share

Report

Posted on by Community Member

An Entity [Account] has a field called [Project] in a 1:N relationship to an Entity called [Work]. Both Entities therefore are mapped by the same field and datatype

[Account].[Project] (Lookup) ---> 1:N ----> [Work]].[Project] (Lookup)

[Project] appears on the form of the Account Entity. And [Project] appears on the form of the Work Entity. As look-up datatypes, these are presented as GUID in the org_wfcrm database. And using T-SQL, they can be joined on the [Project] column on the org_Account and org_Work tables

In CRM: The relationship "Navigaton Pane Item for Primary Entity" under the newly created "Account to Work Association" has a Custom label called "Projects" related to the Account Entity.

Under Mapping the Source_Name and Target Name refers to their respective Entities. I've double-checked this.

Published.

But when Selecting the drop-down for the Account and then browsing to the "Projects" as such :

And then going under "Common" I get to the "Work Association Associated View" and then see nothing

No data is returned. Though in the database I can easily make the selection off of the same fields and related tables.

No idea what I"m doing wrong. Any help would be appreciated. Thanks/.

*This post is locked for comments

I have the same question (0)

All responses (24)

Answers (1)

Saroj Das 3,355 on at

Like (0)

Report

Hi,

My question is silly, but did you add the Project to the accounts?

Thanks,

Saroj

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, as stated above [Project] appears on the form of the Account Entity. And [Project] appears on the form of the Work Entity. Its also the field used as the mapping between Account and Work Entities, which holds the Project field in the form

Was this reply helpful? Yes No
TheMarkChristie 10,328 Moderator on at

Like (0)

Report

Hi

Can you confirm the requirement?

is it to see Projects against Accounts or Work against the Account (with projects as the join)?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

It is to see Projects against Work. Projects is the shared mapped field between Accounts and Work.

Projects is the mapped field between Accounts and Work entities. So , for example, the following Accounts > Account Number is displayed. When one goes to the right hand side of the Account Number there is a down triangle as so: \/ (Highlighed in Yellow)....Clicking on it. goes to Projects under Common.

Project takes me to the Projects Association Associated View. But there is no data. But in the Database, the relationship does exist. Such as

SELECT *
FROM [orgcrm_MSCRM].[dbo].[org_WorkBase]
WHERE org_ProjectId in ( select org_ProjectId from [org_MSCRM].[dbo].[org_AccountBase] )
and org_ProjectId = <GUID>

I get Project data based on the GUID.

In CRM the Project ID is a Look-up Type on the Accounts form

In CRM the Project ID is a Look-up Type on the Work form

Both Project ID fields are mapped . It shows as 1:N relationship under the Account entity . Source and target are mapped to each other on the projectID field. But no data is rendered in CRM.

Any suggestions ?

Was this reply helpful? Yes No
TheMarkChristie 10,328 Moderator on at

Like (0)

Report

Hi

So if I get this correctly

1) An Account can have Many Work Records

2) when you are on the account you want to be able to see all work records for that account

3) on the work records you need to see a lookup to the account

4) I think the problem might be the relationships - when creating new lookups you may have created other relationships.

5) What I am confused about is the Project ID - is this just a reference for the Work Record?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

1) Yes - An Account can have Many Work Records. Each Work Record has a field called "ProjectID". An Account has all the fields called "Project ID". So when the user clicks "Projects, the mapping goes from the Account:Project(1), Account:Project(2), etc to the Work entity which lists Project(1), Project(2), etc.

2) Yes - on the account I want to be able to see all work records. So, for example. Account --> Work.Project(1), Work.Project(2), etc.

3) No - When the user is on the Work Entity, all they need to do is look at the Projects associated with the Work Entity.

4) Maybe - Work.Project is also mapped to another Entity called Team.Project. Would reusing one mapping cause the other not to display ?

5) Project ID is the field which is the mapping between Account entity and Work entity. So under the Account entity in Mappings the Source Name is org_Project and the Target name is org_Project. Both Project fields appear for Account and for Work (one appears on a form; the other appears on a view). So, to answer your question, Project ID references both in a Account ---> Work ( 1:N ) Referential relationship.

Hope that helps. Thanks for your support. I'm still trying to work on this, but there is very limited resources online

Was this reply helpful? Yes No
TheMarkChristie 10,328 Moderator on at

Like (0)

Report

Hi

Ok so I have you now, What you need to do is create a lookup on the Work Item to look up at the Account (you can have this as a hidden field or not on the form) then on creation of a work item have a workflow that copies the account down to it from the project, you can then add a subgrid to the account that will show all work items. I can see how you have got to where you are from at the moment using SQL logic but CRM does work a little different.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

So, create a N:1 Mapping from Work Item to Account by mapping the Project field from Work Entity to Account Entity ?

Was this reply helpful? Yes No
Suggested answer

TheMarkChristie 10,328 Moderator on at

Like (0)

Report

1:N From Account to Work Item - Then on Create of a new work item Have the workflow update the Work Entity with the Account

If this has worked remember and mark them as verified

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks, Mark, but the mapping relationship I created I expected to see the data, not a method of creating data in a Form and have it updated in the the related entity.

There is already data in the the ProjectID field that is related in a 1:N from Accounts to Work. Currently, I have to either view Accounts or view the Work entity independently to view the ProjectID. I used the 1:N mapping from Accounts to Work to drill down to the ProjectID as shown in the screenshot above.

So, by what you're telling me, mapping does not accomplish this. Mapping only establishes a relationship similar to a way one creates a record from a template. i.e. the "data" from the template is provided with data from a master form, and then the user completes the form on the related associated view/form ?

I was trying to relate to Entities with the same ProjectID so that when the user is on either form, they could view the ProjectID that Account and Work entities share.

Hope that makes sense. Once again, grateful for your help. And if the purpose of Mapping is like this, then this will be a great resource going forward on another project I'm working on....

Was this reply helpful? Yes No