Viewing related data in a custom table (MS Forms data) and Account table

(0) Share

Report

Posted on by rixmcx59

158

Hello all, I am trying to sort through the best approach to view related data with the following.

I created a dataverse table to store form data, unique ID is Response ID
I have a flow that loads form data to the table
The flow also creates a new customer and adds Response ID to the account table.

What next? I am not sure how to set up this up. I want to add a tab so the user can see the MS forms results (45 questions) related to the account. Is merging the data in Power Query the way to go or creating relationships? It's one:one relationship, one survey for each account.

Thanks for any help,

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

BCLS776 8,994 Moderator on at

Like (0)

Report

If your MS Forms response will only ever be a 1:1 relationship with Account, you could add a column to the Account entity that contains the ResponseID of the form. Then, whenever you need to access the responses for that Account record, you can do a LookUp() to the Response table using that ResponseID. No Power Query needed here, just revise your flow to find and associate the response with the correct Account too.

Does that make sense?

Bryan

Was this reply helpful? Yes No
rixmcx59 158 on at

Like (0)

Report

Yes, it will always be 1:1. I have already added the responseID to the Account table. Are you saying Add a Lookup column from account to survey or have a flow look up the responseID value and populate a powerapps form?

Thanks for the reply

Was this reply helpful? Yes No
BCLS776 8,994 Moderator on at

Like (0)

Report

In my mind, I don't think a Lookup column is the best answer for 1:1 relationships. Dataverse treats it as a 1:M relationship anyway and then you have to remember you're seeking a record, not a table of records. The responseID column in the Account table handles it adequately. A LookUp() will return the matching record in whichever direction you query.

For your flow that populates the response table, that seems like a good point at which to store the responseID in the Account table. Is there something in the form you can use to identify the correct Account record to update? Perhaps the Account's name or something else relatively unique? That can be the basis for a List Rows action to find the correct Account record.

Bryan

Was this reply helpful? Yes No
rixmcx59 158 on at

Like (0)

Report

Thanks for the help, I ended up just adding the survey columns from the MS Form to the accounts table, so now when a form is submitted, a new account is created (by the PA flow). I added a new tab to the main form to view the survey results. this may not be the right way, but it accomplished exactly what I was looking for.

Was this reply helpful? Yes No