Forum Discussion
Systemuser table mutiple relationships
I'm using 2016 update 1 CRM Online with Power BI connected via HTTP REST API to boost speed.
I'm using the 'best practice' protocol of not using expanded columns in the data query but rather using a relationship & look up / related column in another table to boost performance when returning rows.
The problem is that there are mutiple fields in the entity table that could relate to Systemuser table. For example,
_ownerid_value
_createdby_value
_modifiedby_value
all will have lookup values in the system table aginst systemuser ID. I've tried to edit this in relationships but I get a message telling me that there is 'ambiguity' between the tables.
There are occassions where I need to know who has booked something (created by) as well as who has the record assigned to them (owner) and even who had changed it.
I can't work out how to have the look up value (user's name) appear more than once if that makes sense.
can anyone help please?
I believe that one way to solve this would be to download the Systemuser table 3 times. I would name the tables "Owner Users", "Create Users" and "Modify Users" or something similar to tell them apart from one another. Relate each of the columns in the entity table to its corresponding Systemuser table. Then you can use the column from each of the 3 tables in the same visualization.
5 Replies
I believe that one way to solve this would be to download the Systemuser table 3 times. I would name the tables "Owner Users", "Create Users" and "Modify Users" or something similar to tell them apart from one another. Relate each of the columns in the entity table to its corresponding Systemuser table. Then you can use the column from each of the 3 tables in the same visualization.
Thanks Greg_Deckler yes sorry it was a daft question, solution was really obvious I should've worked that out myself!
Thank you.