Skip to main content

Notifications

Service | Customer Service, Contact Center, Fie...
Suggested answer

Connecting Invoice table to Work Order and Agreement

(2) ShareShare
ReportReport
Posted on by 366
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.
 
  • Suggested answer
    Tom_Gioielli Profile Picture
    Tom_Gioielli 280 on at
    Connecting Invoice table to Work Order and Agreement
    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,160 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,962 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans