"Execute an action on a set of rows" - In the...

references
(Grishma) #1

“Execute an action on a set of rows” - In the referenced action, I want to select the changed-row’s column.

Here is the scenario: Update Stock.Quantity for each changed Row in InvoiceLines table.

So, i have defined a workflow in InvoiceLines tables which is to be triggered when any changes happens in InvoiceLines.

The workflow will change the data using an action on InvoiceLines table of type “Execute an action on a set of rows”. This action has:

Event (Changed) Table: InvoiceLines

Target (Referenced) Table: Stock

Rows on which to apply the action: SELECT(Stock[DesignNumber], [DesignNumber] = [_THISROW].[DesignNumber])

And, there is a Referenced Action:

Name: UpdateStock

Here is where I have trouble of updating. The update is: Stock.Quantity = Stock.Quantity -

InvoiceLines.Quantity

I cannot access referenced row’s quantity (InvoiceLines.Quantity) within UpdateStock action.

How to achieve this?

(Grishma) #2

@Suvrutt_Gurjar

Hi Sruvrutt, thanks for the quick response!

The expression

SUM([RELATED InvoiceLines][Quanty Column Name in InvoiceLines])

deals with all the invoice lines (and not just the ones currently being updated that have triggered the workflow).

This may have implications in long run when some stock’s design-numbers are reused.

Is it not possible to access the changed InvoiceLines in the referenced action on Stock table? Like how it is possible to access them in the “Execute an action on a set of rows” using _THISROW?

(Suvrutt Gurjar) #3

If you are referring to using more qualified expressions in referenced action , I believe , yes, that is possible.

I for example got the same result with

SUM(SELECT(InvoiceLines[Quantity Column Name in InvoiceLines Table],[Reference Column ID in InvoiceLines Table]=[_THISROW].[Stock Table Table ID]))

in place of

SUM([RELATED InvoiceLines][Quanty Column Name in InvoiceLines])

(Grishma) #4

@Grishma

Thanks Suvrutt. I’ll try and get back to you.

Appreciate your help.

(Suvrutt Gurjar) #5

Based on my understanding of requirement description, you may wish to try following approach with an expression like below in referenced action.

The expression can be set on a column called say [Consumed Quantity] in stock table

SUM([RELATED InvoiceLines][Quanty Column Name in InvoiceLines])

The final or remaining stock may be computed in Stock table ( or even referenced action) as

[Final Stock]=[Initial Stock]-[Consumed Quantity]