Trigger expession invalid

[klarson]

Hi, I'm looking for help.

I am trying to create a flow and can't figure out why my trigger expression is invalid.

I have 2 custom lists. The one is an invoice list, where incoming invoices get added to the sharepoint list as they arrive.  Here are the column headers for that list: 

The second list is a Master PO list, where I want to track the remaining value of my Purchase Orders. Here are the column headers on that list:  

The flow I'm trying to write will sum up the value of all invoices for a specific Purchase Order on the Invoice List and put that amount in the Sum of Approved Invoices on the PO List. I'll paste screen shots of the flow I've put together, and would appreciate any help I can get in getting it up and running!

Regards,

Kirstin

[wskinnermctc]

Why do you need to sum up ALL of the invoices values up update a single Purchase Order each time an Invoice is created or modified?

Wouldn't it be easier to have a flow that triggers when an Invoice status is changed to Approved that will add to the Purchase Order approved sum? So it is individually adding to the Purchase Order approved sum as the invoice is changed to approved.

[klarson]

Sure, I could possibly do that. I'd have to figure out how to write that flow--I'm new at this. But will that solve the invalid trigger expression problem? 

[wskinnermctc]

For the specific "trigger expression problem" I would need more information. Like being able to see the full expression you are using as well as what exactly the error message states.

For a separate flow that triggers only on Approved invoices, you need to tell me more about how an Invoice becomes Approved. Because your current example, if it worked, you expect the Purchase Order sum to be updated anytime an Invoice is created or modified. So I don't understand how you were differentiating an Approved invoice if the flow was going to trigger on all invoices.

[klarson]

I can provide that information.

Originally, I was using a condition "invoice approved for payment? is equal to yes", but it also resulted in an error message. So I changed it to invoice amount has a value just temporarily. But you are right--my intention is that once someone has reviewed the invoice and updates that field, a yes response would trigger the flow.

Here is the full expression I used to create the filter array: 

@equals(trigger(Outputs()?['body/Purchase Order/String'], item()?['Purchase Order/String'])

I think that is saying create a list of all the Purchase Orders in the nvoicing list that have a matching Purchase Order in the Purchase Order list.  I have tried replacing the word  "string" with "value," but it doesn't help. Both lists, for the Purchase Order columns, are defined as text.

Here is the error message I get:

I think that addresses all of your comments. Thanks for trying to help me!

[wskinnermctc · Accepted solution]

Ok I made an example flow and SharePoint lists. But I ask that you go look at this post about SharePoint Column InternalName and use that information to name your columns. This will help greatly with using references and expressions.

I think you should delete your list columns and recreate them to look more like my example. You can always change the display name after creating.

I made an example Purchase Order SharePoint list that has 2 purchase Orders:

Purchase Order Initial List View

Here are the column types of the Purchase Order List:

Purchase Order List Settings

Then I made an Invoice List with 3 Example Invoices:

Invoice Tracker with 3 submitted Invoices

Here are the column types of the Invoice SharePoint list:

Invoice List Settings

The Invoice List has a Choice type column "InvoiceReviewStatus" that will be used to identify that status of a created invoice/item.

Invoice Review Status Choice Column

So then when an Invoice is Reviewed, it can have the status changed to "Approved" in the choice column. See below I changed the status of the first invoice.1st Invoice Approved

Any creation of an item or modification will trigger the flow, but it uses a Condition to check the column value of "InvoiceReviewStatus" to determine if the flow should continue or stop running.

Flow Overview All Steps

In the condition it is checking the InvoiceReviewStatus Value which is different than the InvoiceReviewStatus. Choice columns will have multiple dynamic content options. One of them is the Value which will just be the word, the other will be a different output showing the whole choice box that we don't want.

SharePoint Choice Column VALUE

The second part of the flow is where there is a Get Items action that is getting all of the rows from the PurchaseOrders SharePoint list. Directly in the Get Items there is a Filter Query field that is being used to only get items with match Purchase Order Numbers.

The Filter Query is in the format of PurchaseOrderNumber equals the InvoicePurchaseOrderNumber.

Since the purchase order number are literally in a number value, the Filter Query format is:

ColumnName eq NumberValue

If the purchase order number was a text/string or trying to filter by another column the Filter Query format is:

ColumnName eq 'TextValue' 

If it is a text it needs ' single quotes around the value.

Flow Overview - Zoom Get Items and Update Items

 The Update Item step will add the amount of the new invoice to the Sum of Approved Invoices. This is basically taking the value from Get Items and adding it to the trigger Invoice Amount. 

add(items('Apply_to_each')?['SumofApprovedInvoices'],triggerOutputs()?['body/InvoiceAmount'])

The expression is put in the Expression fx box to be added into the field:

Add Expression

The remaining purchase order amount uses the previous add expression, but it also includes a subtract expression. The is getting the PurchaseOrderTotalCost and subtracting the SumofApprovedInvoices to get the remaining.

sub(items('Apply_to_each')?['PurchaseOrderTotalCost'],add(items('Apply_to_each')?['SumofApprovedInvoices'],triggerOutputs()?['body/InvoiceAmount']))

Here is the expression in the fx field:

Subtract Expression

Here is the reference for how the expression (formulas) are created:

add() expression to sum values

subtract sub() expression  to subtract values

So here is the example of changing an invoice to submitted and then the result of the Purchase Order list:

Invoice List with First Invoice ApprovedPurchase Order List After First Invoice was Approved

You can see the $1,550 InvoiceAmount was added to the Purchase Order List in the SumofApprovedInvoices and it was also subtracted from the RemainingPurchaseOrder column.

Here is the example after the remaining 2 invoices were changed to Approved:

Invoice List Remaining Invoices ApprovedPurchase Order List after All Invoices Approved

This is a very basic example. You can add features and functions to this all day, but I recommend getting the basic flow working and then building things on top of that.

Give this a try and let me know if it works for you. Please create new column names because it will make all of this much easier. See this post for why column names help.

[klarson]

I have re-written the order of this flow, and think I'm a lot closer to getting it to work, but just need help now on the last step.

So, the new flow says when the Invoice list is modified and the Invoice Submitted value = yes, create a filter array for the Purchase Order value and compose it as the first array then update the PO List by matching the output of the array (which should be the purchase order number) and update the Sum of Approved Invoices field with the newly approved invoice amount.  I think what I'm missing is that amount, but I'm stuck on how to get it into this flow.  Here is a screenshot. The last function in the Sum of Approved Invoices reads "add(triggerOutputs()?['body/Amount'], outputs('Compose')?["Sum of Approved Invoices'])

[wskinnermctc]

The example I provided is what you should use. It is the least amount of steps to accomplish what you want.

[klarson]

• Thank you, I will work on you instructions today!

[klarson]

Thank you so much for your very helpful reply. I really appreciate that you took the time to provide me with lots of learning resources, and explained each step.  It took me a little time to re-create the two lists, but once I did that, I followed your instructions and created the flow.  It ran successfully, but resulted in one glitch. In the PO Tracker Sum Invoiced field, it recorded double the amount that I approved in the invoice list.  Any idea why?  

Again, thank you so much for the excellent instruction. I'm so appreciative for your clear, easy to follow tutorial!

[klarson]

Here are some screenshots of the results:

[klarson]

This works perfectly.  Ignore my question about the doubling of the total invoiced amount--the flow ran twice. I've added several additional test invoices, and it is performing exactly as I was hoping. You are amazing!!!

[wskinnermctc]

Glad you got it working 👍

You can go change the column display name and it won't be a run together words, but the Internal Name of the column will always stay the same.

For example if rename your column "ApprovalDate" to "Date Invoice Approved", the InternalName will always be "ApprovalDate" to use in your flow, but what is displayed on the list will be "Date Invoice Approved".

One more thing:

Can go up to my previous reply that has all of the instructions and screenshots and mark that Accept as Solution so this will be resolved?  It will make it easier for others to find the solution, and I get those sweet fake internet points!