cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
d5omino
Level: Powered On

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?

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Common Data Service entity "relationships" how to define?

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:

59.PNG60.PNG

Further, take a try with the defaultLookup:

Build a relationship between entities

Use field groups

 

Hope the above helps.

 

Regards,

Michael

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.
d5omino
Level: Powered On

Re: Common Data Service entity "relationships" how to define?

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

 

Joey

d5omino
Level: Powered On

Re: Common Data Service entity "relationships" how to define?

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.

d5omino
Level: Powered On

Re: Common Data Service entity "relationships" how to define?

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?

 

Joey

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (3,804)