Common Data Service entity "relationships" how to define?

[d5omino]

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?

[v-micsh-msft]

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.

Regards,

Michael

[d5omino]

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]

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]

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