cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
d5omino
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?

 

4 REPLIES 4
v-micsh-msft
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:

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.

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

 

Joey

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?

 

Joey

Helpful resources

Announcements
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.

New Process Advisor Capabilities carousel.png

Read the blog for the latest news

Read the latest about new experiences and capabilities in the Power Automate product blog.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

AI Builder AMA June 7th carousel (up on May 25th, take down June 8th) (1).png

'Ask Microsoft Anything' about AI Builder!

The AI Builder team invite you to ask questions and provide helpful answers at our next AMA.

Users online (1,629)