Dynamics Community Forum Thread Details

Connecting Invoice table to Work Order and Agreement

This is not a question but rather an answer in case anyone is looking to do something similar.

ISSUE

I do a lot with the data that is tied to an Invoice from a Work Order and an Agreement, therefore to make like easier, since OOB there was no relationship I created one.

I used XRMToolbox to create a new Polymorphic Lookup column named InvoiceSource in the Invoice table.

OUR CONFIG

When an invoice is created from a Work Order the Name is INV-WOxxxxxxxxx, and when created from an Agreement the Name is INV-SLAxxxxxxxxx.

I then had to create two separate cloud flows. Why? ...because the way we do agreements there can be multiple invoices for an agreement so looking up the agreement name wouldn't give us the needed guid for the specific invoice created by the Agreement Invoice Date.

Flow 1: Update Invoice with Work Order

--- Concept: When a new invoice is created, get it's name and use that name (part of it) in a List Rows to find the Work Order with that name, after finding the Work Order use that guid to update the Invoice table.

Since invoices from Work Orders are named INV-WOxxxxxxxxx I took the substring of the name after the first 4 characters to use as a lookup with the GetWorkOrderguid.

I'm sure there's a sleeker way than an Apply To Each to pull the Work Order guid from the previous List Rows action but I didn't want to take more time to figure that out...and it's only returning 1 record so I didn't figure it would be more overhead than an alternative. IF YOU HAVE A SUGGESTION I'M OPEN.

Flow 2: Update Invoice with Agreement

--Concept: The table Agreement Invoice Dates is the only table I found that has a connection between both the Agreement and the Specific Invoice created for that invoice date...in other words a single invoice record. ...and when a record in the Agreement Invoice Dates table has an InvoiceStatus changed to 'Processed' then that creates the invoice. So when that happens I get the agreement guid and the invoice guid and update the InvoiceSource field of the Invoice table with the agreement guid.

The condition is

triggeroutputs()?['body/_msdyn_invoicestatus_label'] is equal to Processed

HELP--I could take out the condition if I could refine the trigger by only triggering if the LABEL of the invoicestatus = 'Processed'. I never figured out how to filter on a optionset label (optionset value is easy).

NOTE: I also made an instant flow to go through the Invoices table and update any invoice with the name containing WO. I did this first to get all the old invoices updated with their Work Orders. Not really necessary since I should never need that information especially once the Invoice is paid.

Categories:

Dynamics 365 Field Service

Quick note on your question of checking an option set label (though I need to point out that checking against a label is not recommended, as they can change and then break flows). This syntax is how you can get the label of an option set anywhere in your flow, including in a check condition.

Take the dynamic value for the field, and then add {@OData.Community.Display.V1.FormattedValue} to the end of it to grab the label. So, in your example, the code would be:

triggeroutputs()?['body/_msdyn_invoicestatus@OData.Community.Display.V1.FormattedValue'] is equal to Processed

I always recommend running the flow and checking the output of your trigger or any get record steps. The labels and other values are all available in that output, and it's a great way to see what can be used in your flow.

If this helped, please consider marking the answer as verified

Quick Links