Populate products from Quote sub-grid to Invoice sub-grid

(0) Share

Report

Posted on by Sunny21

Good Day, Thank you for your interest and hope this message finds you well.

We have a business requirement where we want the fields on Invoice to automatically populate based on the Lookup of a Quote. Currently we have a working flow that accomplishes with most of the fields that we want. However, their are field values on the child entities (Quote Product and Invoice Product) that are not easily accessible.

The Quote entity has a Subgrid (table: Quote Product) which details the product name, price details, etc.. The Quote Product table is the child table in the relationship so it actually creates a separate record for each Product that was selected in the Quote sub-grid. I want to be able to re-populate them back into the Quote entity, the issue I am running into is that the Child table creates a separate record for each Product selected in the Sub-Grid and it's challenging to the update any single record on the Quote table with multiple child records.

The main objective is when Quote is looked up on Invoice; the related Products (Quote Product) are updated on (Invoice Products) Subgrid.

A no-code, low code solution would be appreciated.

Best Regards,

Sunny

I have the same question (0)

All responses (13)

Answers (3)

Suggested answer

Community Member on at

Like (1)

Report

Hi Sunny21,

If you want to A no-code and low code solution, you can try to use power automate to create a flow.

List rows:

Use the List rows action to retrieve multiple rows at once from Microsoft Dataverse with a structured query.

Use lists of rows in flows - Power Automate | Microsoft Docs

Was this reply helpful? Yes No
Verified answer

Bipin D365 28,983 Moderator on at

Like (1)

Report

Hi,

I think this is OOB behaviour. When you create Order from Quote, Quote products will be created under Order. And when you create Invoice from Order, Same products will be created under Invoice as well.

If you still want to populate product from quote subgrid to Invoice sub grid then you can make use of Power automate which will trigger on Invoice create and check if Quote lookup is not null.

Then use List rows to fetch quote products based on Quote ID and lookup through each record using Apply each and create record under Invoice product.

Get Row By ID is added because I did not find OOB Quote lookup on Invoive so I added this action to get Quote ID.

I have mapped limited fields while creating Invoice Product but you can add whatever fields you want.

Please mark my answer verified if this is helpful!

Regards,

Bipin Kumar

Follow my Blog: xrmdynamicscrm.wordpress.com/

Was this reply helpful? Yes No
Sunny21 90 on at

Like (0)

Report

Hey Bipin, Thank you for your response.

I gave the flow a test this morning and unfortunately I got an error on the 2nd step of Get Row by ID. Not sure why you suggested to Get Row Id from 'Orders' table tbh because the point was to bypass Creating Order from Quote. Instead I want to be able to Create an Invoice from or/ based on a Quote instead. Not in the OOB functionality sense (that would work too), but when a Quote is lookup on the Invoice entity, the Invoice Product sub-grid is populated based on Quote Products.

I tested the flow by both activating a Quote and creating an Order from it and then running the flow. And then I tested by simply Creating a New Invoice and looking up a Quote which had multiple products on it, but the sub-grid on Invoice Product did not populate.

Also, can you please clarify whether the Field Mapping you did was between (Quote <---> Invoice) or (Quote Product <---> Invoice Product)?

Anyway I appreciate your willingness to help. Much appreciated as I have been stuck on this for a while.

Best,

Sunny

Was this reply helpful? Yes No
Verified answer

Bipin D365 28,983 Moderator on at

Like (0)

Report

Hi,

In your case Get Row by ID is not required. You can directly get Quote ID from Invoice Entity.

I added because my Invoice Entity has Order lookup and Order Entity has Quote lookup so to get Quote ID I added Get Row By ID. So you can skip that.

Yes I have mapped the fields from Quote product to Invoice Product.

So delete ger rowb by Id and Show me your List Row action screenshot.

Please mark my answer verified if this is helpful!

Regards,

Bipin Kumar

Follow my Blog: xrmdynamicscrm.wordpress.com/

Was this reply helpful? Yes No
Sunny21 90 on at

Like (0)

Report
Okay so this is what I got. Maybe I have a type or I am missing a relationship field. Is the Quote Product supposed to have any relationship to the Invoice entity? Currently, on the Invoice entity I only have a lookup to the Quote Table.

OUTPUT:

Source":"20420110314692223115122101881302161980204115239246153185536124498477412319847140238157,138211209483118215107188897080244118251204202182902442271712622897102158657919411174","Public":"OPTIONS,GET,HEAD,POST","Date":"Fri, 18 Feb 2022 15:12:14 GMT","Content-Length":"125","Allow":"OPTIONS,GET,HEAD,POST","Content-Type":"application/json; odata.metadata=full","Expires":"-1"},"body":{"error":{"code":"0x0","message":"Syntax error at position 54 in '_quoteid_value_eq 32a6c725-165c-ec11-8f8f-002248282812'."}}}

Was this reply helpful? Yes No
Suggested answer

Bipin D365 28,983 Moderator on at

Like (0)

Report

Hi,

Filter row syntax is incorrect.

It should be _quoteid_value eq

you have added extra _ before eq operator.

Please mark my answer verified if this is helpful!

Regards,

Bipin Kumar

Follow my Blog: xrmdynamicscrm.wordpress.com/

Was this reply helpful? Yes No
Sunny21 90 on at

Like (0)

Report

Yeah I saw that after I posted it lol. On the Last step I got the 'Resource Not Found' error. Any ideas what may have caused this?

Was this reply helpful? Yes No
Suggested answer

Bipin D365 28,983 Moderator on at

Like (0)

Report

Hi,

Invoice column mapping should be as below -

/invoices(select invoice from dynamic content)

Please mark my answer verified if this is helpful!

Regards,

Bipin Kumar

Follow my Blog: xrmdynamicscrm.wordpress.com/

Was this reply helpful? Yes No
Suggested answer

Sunny21 90 on at

Like (0)

Report

Thank You So Much Bipin. Much appreciated. I got it working with one last modification; another forward slash after invoices. Thank you again and God Bless!

/invoices/(select invoice from dynamic content)

Was this reply helpful? Yes No
Sunny21 90 on at

Like (0)

Report

Hey Bipin I thought maybe you could also help me with one last error. I looked it up when troubleshooting and I have not found a viable solution yet. The flow ran successfully in the Flow test environment but when I created a new invoice and selected the Quote from the lookup; nothing populated. I got error; "Expression expected at position 18 in '_quoteid_value eq '" for the List rows action step. Have you seen this error before?

Best Regards,

Sunny

Was this reply helpful? Yes No