Data from a column in one table into a column in another (Many to One relationship)

(0) Share

Report

Posted on by THowkins96

Hi all,

I am wondering if the below situation is possible and if so, how I can build it?

Our organisation is building a commentary app for our Accounts Receivable. There are 2 tables, 1 table that pulls through total open invoices by customer number (AR by Customer) and the other table is a commentary table that uses the customer number as primary column to log comments against (Comments).

In "Comments" I have a field that calculates a next review date.

In the views in the model driven app for "AR by Customer" is there any way I can pull through the earliest next review date from the "Comments" table to sit alongside the view by customer so that our team can see which customers need to be reviewed next?

I was thinking there might be an option with Power Automate but not sure how that would work?

Many thanks
Tom

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (8)

Answers (0)

AbdulWahab 517 on at

Like (0)

Report

Hi @THowkins96 ,

Right now you are not able to apply sort in the view based on that calculated column (next review date). I am thinking you should need a new manual column (similar to the next review date) here and copy the value of that calculated column (next review date) into a new manual column (similar to the next review date). To copy value you can use ower Automate.

If you need additional help please tag me in your reply and please like my reply.

If my reply provided you with a solution, please mark it as a solution

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: https://www.linkedin.com/in/abdul-wahab-a5b8b011a/
Youtube Channel: https://www.youtube.com/channel/UCBjgLqLK_2kU-3bwo4McrTw

Was this reply helpful? Yes No
THowkins96 88 on at

Like (0)

Report

Hi @AbdulWahab

I did think that this was going to be the case and Power Automate would come into play. Can you assist with how I would set up this flow please?

What I want to pull through is the Next Review Date of the most recently created row in "Comments" that sits against each invoice. i.e. we could have 20 comments against the invoice as it's one to many, but I only want to pull through the most recent one.

Many thanks
Tom

Was this reply helpful? Yes No
AbdulWahab 517 on at

Like (0)

Report

Hi @THowkins96 ,

How about the rollup column on the Invoice table?

If you need additional help please tag me in your reply and please like my reply.

If my reply provided you with a solution, please mark it as a solution

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: https://www.linkedin.com/in/abdul-wahab-a5b8b011a/
Youtube Channel: https://www.youtube.com/channel/UCBjgLqLK_2kU-3bwo4McrTw

Was this reply helpful? Yes No
AbdulWahab 517 on at

Like (0)

Report

Hi @THowkins96 ,

In case you can not create a rollup column on the invoice table (that references the calculated column of the comments table) due to the product limitation then I would suggest you create a manual column on the comments table and populate that one first. This process enables sorting and rollup column for you on the invoice table.

If you need additional help please tag me in your reply and please like my reply.

If my reply provided you with a solution, please mark it as a solution

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: https://www.linkedin.com/in/abdul-wahab-a5b8b011a/
Youtube Channel: https://www.youtube.com/channel/UCBjgLqLK_2kU-3bwo4McrTw

Was this reply helpful? Yes No
THowkins96 88 on at

Like (0)

Report

Hi @AbdulWahab

I do not think that a rollup will work having had a look. There is no option for a related entity as the relationship does not exist on the next review date but instead on the customer ID number.

The comments table already has the next review date as a calculated column, it's just a case of getting that date into the invoices table. What functions in Power Automate would you suggest?

Thanks
Tom

Was this reply helpful? Yes No
AbdulWahab 517 on at

Like (0)

Report
Hi @THowkins96 ,

There are two tables.
Invoice
Comments
Review Date is the column of the Comments table.
There is a "1 to many relationship" between Invoice and Comments tables.

Am I right?

If you need additional help please tag me in your reply and please like my reply.

If my reply provided you with a solution, please mark it as a solution

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: https://www.linkedin.com/in/abdul-wahab-a5b8b011a/
Youtube Channel: https://www.youtube.com/channel/UCBjgLqLK_2kU-3bwo4McrTw

Was this reply helpful? Yes No
THowkins96 88 on at

Like (0)

Report

Hi @AbdulWahab

Yes that is correct, One (Invoice) to Many (Comments).

Thanks
Tom

Was this reply helpful? Yes No
AbdulWahab 517 on at

Like (0)

Report
Hi @THowkins96 ,

I am trying to explain my 3rd last reply.
Comments table
Create a new column.
Copy the values of the Review date into the newly created column using Power Automate.
You can use the Recurring trigger.
Invoice table
Create a new rollup column
reference the newly created column of comments in it with the max aggregate function
If you need additional help please tag me in your reply and please like my reply.

If my reply provided you with a solution, please mark it as a solution

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: https://www.linkedin.com/in/abdul-wahab-a5b8b011a/
Youtube Channel: https://www.youtube.com/channel/UCBjgLqLK_2kU-3bwo4McrTw

Was this reply helpful? Yes No