Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Common Data Service entity "relationships" how to define?

A medical office is trying to automate a workflow. For every appointment that they have there should be a corresponding paper form called a "Superbill". 


The "superbill" should have patients demographics (Name , address, age etc) and insurance/financial info like balances and such.


Context - currently most of the data is stored in a hosted terminal based application so the ONLY way I can get data out is to export a report with the required data into a tab delimited text file

The balance of the data (appointment data) is in a CSV.


My approach. -.


1) create an excel workbook with a power query to clean up and structure the data for import into the common data service.

2) Created a set of custom entities as follows:


  1. Patient (12 of the 20 columns)
  2. Providers
  3. Referrers
  4. Appointments
  5. Superbills


3) Import data into the CDS

4) when a new record is created in appointments table a flow would fire and copy the data (with all relationships respected and thusly pulled in) to a SharePoint list which I have already configured


It seems there is only one type of relationship (Lookup) which seems to only pull in the foreign key and not the entire rows data and there is no way to tell it what you need precisely . I’ve attempted to create a powerapp as a trouble shooting method to see what data is actually linked and it seems the issue is the relationships as I can confirm only the foreign key is visible. Is there a way to perform a query to access the other columns in the table? How can the above be accomplished ? do I need a sql or access database in the mix?


Community Support
Community Support

Hi @d5omino,


Yes, currently the Lookup field from Common Data Service could only get the ID of the related record, if you would like to get other column fields, you would need to load the whole entity, then filter it with the ID from the lookup field.

For example:


Further, take a try with the defaultLookup:

Build a relationship between entities

Use field groups


Hope the above helps.




Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thank you for your response i will try this straight away and respond with results. your response and examples are very much appreicated.



Hello @v-micsh-msft


What is the difference between going to entitiy A and creating a relationship to entity B and going to entitiy B and creating a relationship to entity A? And why does powerapps let me create BOTH at the same time if they are all the same type and function? Also why does powerapps let me create more than one relationship at once ... for example from entitiy A to entity B i can have any number of relationships as long as the name is unique... this does not make any sense . Can this be explained?


The suggestion to use field groups is great but unfortunetly and for an unknown reason my custom entities do NOT have any field groups (perhaps because they came from poer query?) and since creating field groups is not currently supported that option is bust.

Hello Agagin @v-micsh-msft


i took another look ar your post and tried to understand your meaning. Unforuntely i was unable to understand your screenshot examples. let me try to expand on my goal perhaps it will help ..


I have a list in sharepoint . the list must contain x fields from records in the common data service in order for the flow to fire that will dynamically create a document called "superbill" in another library with a custom content typed defined with those same fields.


In order to accomplish this i need a flow that fires when a new record is created in the appointments table (means a new appointment was scheduled) that will essentially say something along the lines of


create a record in "superbills" for every record in "appointments"  and includes the data from "Patients" that matches the "name" and "phone number" columns in "appointments" and include all of the linked records from the tables  "providers", "financial", "private" etc..


the above might include some "joins" and "select from" statments in Sql however how to perfrom these complex queires to the common data service specifically thorugh "Flow" eludes me. Can you assit in creating this flow?



Helpful resources

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (4,861)