Read data from one table and insert into another table in model driven app on form save ( kind of background process )

[VenkatC]

I am working on a case management process. Where in basic cases information will be loaded on a monthly basis into the app.

and each case will go through different life cycles with different reviewers and each life cycle will have individual questions to answer and once all the questions are answered and validated that case will be assigned to second review and close it.

Project -> cases ( one project - many cases )
 Case -- > life cycles ( one case - Many life cycles )

Life Cycle - > Questions ( one Life Cycle -- many questions )

For creating a life Cycle to a case , I created a Life Cycle and Reviewer association table , so when a user opens a case , I am giving the user a form to fill in the information related to lifecycle and reviewer.

once the lifecycle & reviewer are identified I want to pick the question related to the life cycle and build a new association table , Case Info , life cycle , reviewer and questions

how can I implement this on form save -- 
I want to identify the Questions related to the life cycle selected and then I want to insert into a new table with ( Case Id , lifeCycleId, reviewer info and questions )

[LinnZawWin]

I am not so clear on how the Questions are going to be identified (I thought the questions must be already related to the life cycle), but maybe you can achieve this by Power Automate cloud flow. If you have a flag or a specific status to start this process, the cloud flow can be triggered on the value change of that particular column and created the data in the new table by looping through the Question records.

[rampprakash]

Hi @VenkatC 

I think your question is quite tuf to understand,

Can you please explain a bit more

Based on my understanding we can use plugins or Microsoft flow to achieve the same.

[VenkatC]

Here is what I am trying to accomplish.

I have three tables and few columns

1) Case ( Case Id, LifeCycle )

2) Questions ( QuetionId, LifeCycle , QuestionInfo)

3) Question and Answers ( Case Id, Question , Answer )

Initially user will load Cases information ( excel file) to my Power app application by import functionality, these cases will have case Id and life Cycle assigned in excel

Once the Case was loaded to application and user is ready to review the case, I want to show questions to answer related to the case, these questions need to picked based on the life cycle assigned to the case.

Questions table will have relation with 'Life Cycle'

I want to identify the questions and store those questions into a different table 

How can I achieve this ?

[LinnZawWin · Accepted solution]

@VenkatC 

Power Automate cloud flow would be a good fit for your scenario.

• The cloud flow can be triggered When a row is added to the Case table.
• Based on the LifeCycle, List rows from the Questions table with the filter "xyz_LifeCycle eq <<LifeCycle value from the trigger>?
• Then, Apply to each row
  • Add a new row to Question and Answers table with Case Id from the trigger and the Question from the List rows step above

[VenkatC]

Thank you LinnZawWin. I started working on the resolution you shared. But one thing is troubling me to achieve the final step to add to Answer table, 
 In answer table I created a relationship between Answers and Questions, that created a look up column for question 
When I am trying to add question to the lookup table, I am passing GUID to the lookup column in Answers table 
But I am getting some error ""URL was not parsed due to an ODataUnrecognizedPathException. Resource not found for the segment provided in the URL""

[LinnZawWin · Accepted solution]

@VenkatC 

To set the lookup, you need to set in the following format.

«EntitySetName»(«RecordGUID»)

e.g. xyz_answer(«AnswerRecordGUID»)

Please refer to my blog for more details.

https://linnzawwin.blogspot.com/2019/11/power-automate-how-to-set-lookup-field.html

[LinnZawWin]

@VenkatC 

If my answer solved your problem, can you please mark it as a solution to close this thread? That will be useful for others in the community who come across this thread to identify the solution easily.