Need help creating an expression to add a column value to another table

[Elijah_Magrane]

In reference to my earlier post: @Peter

I am currently constructing a series of actions as part of a workflow. However, I am stuck on an particular expression. The design is that I have two tables, Raw Materials and Production which share a ref. The production table ref column is [RM Inv Id] The expression I am trying to construct would add the quantity from a Production[Quantity Out] to Raw Materials[Quantity Out].

The problem is I cant figure out a way to single out the row for Production[Quantity Out] in the action target table Raw Materials [Quantity Out].

For example SUM(SELECT(Production[Quantity Out],[RM Inv Id]=[_THISROW].[RM Inv Id])) get me close but it (obviously) sums all of the columns in the table and I need the value of individual rows (replacing SUMS with DECIMAL returns 0).

I tried creating a [RecentEntry] column using MAXROW but I can’t get the expression to work.

I would be grateful for any help

[Steve]

Elijah_Magrane:

replacing SUMS with DECIMAL returns 0

What does this mean?

[Elijah_Magrane]

Initially my expression was

DECIMAL(SELECT(Production[Quantity Out],[RM Inv Id]=[_THISROW].[RM Inv Id])) but it returned 0. I switched to SUM() for troubleshooting and I then I realized that the DECIMAL() and SUM() were probably irrelevant to my situation

[Steve]

Elijah_Magrane:

I switched to SUM() for troubleshooting and I then I realized that the DECIMAL() and SUM() were probably irrelevant to my situation

Ahh! This clarifies a lot!

[Steve]

Elijah_Magrane:

For example SUM(SELECT(Production[Quantity Out],[RM Inv Id]=[_THISROW].[RM Inv Id])) get me close but it (obviously) sums all of the columns in the table and I need the value of individual rows

Assuming both the Production and Raw Materials tables each have a column named RM Inv Id, your expression should give you the sum of all Quantity Out column values from rows in Production that share their RM Inv Id value with the current Raw Materials row.

But you don’t want the sum… Suggesting there might be more than one row in Production with the desired RM Inv Id value. Hence, “I need the value of individual rows”. Which individual rows? You reference “RecentEntry” and MAXROW(), suggesting you want the Quantity Out column value from the most recent Production table row with the given RM Inv Id column value.

Time for bed. More tomorrow.

[Elijah_Magrane]

both the Production and Raw Materials tables each have a column named RM Inv Id ,

They do. Raw Materials is the Parent table and Production is the child.

Suggesting there might be more than one row in Production with the desired RM Inv Id value

There are numerous rows within the production table that have the same RM Inv Id

“I need the value of individual rows. Which individual rows?”

Yes. the child table utilizes the Raw Material table as an active inventory source for BOMs. Once a row is entered into the Production Table I need for it to be added to a [Quantity Out] column in the Raw Material Table

You reference “RecentEntry” and MAXROW() , suggesting you want the Quantity Out column value from the most recent Production table row with the given RM Inv Id column value.

I am not sure but I think the only way to achieve the is through a MAXROW(), Specifically, a VC MAXROW(Production, Timestamp). However, I couldn’t successfully incorporate this into the expression. Not sure if it is my Syntax or if I should be trying something else

[Steve]

Not (yet?) sure it’s the right approach to your problem, but to get a reference to the latest row for the RM Inv Id of a particular Raw Materials row:

MAXROW("Production", "Timestamp", ([RM Inv Id] = [_THISROW].[RM Inv Id]))

[Elijah_Magrane]

Steve:

MAXROW(“Production”, “Timestamp”, ([RM Inv Id] = [_THISROW].[RM Inv Id]))

I understand the expression but how would I return that specifics rows [Quantity Out]?

[Steve]

Is the Quantity Out column of the Raw Materials row a roll-up of Quantity Out values from many Production rows, or is there a one-to-one correspondence between them?

[Elijah_Magrane]

It is a roll-up.

Basically, the goal is that each time a row is entered into a production sheet it takes the value from [Quantity Out] and and adds it to the [Quantity Out] column in the Raw Materials table. I have the overall workflow working but the issue is single out the Production[Quantity Out] column per each row added

[Elijah_Magrane]

@Steve

If I were to put MAXROW(“Production”, “Timestamp”, ([RM Inv Id] = [_THISROW].[RM Inv Id])) in a VC would a dereference expression work with to retrieve the Production[Quantity Out] from that specific row?

I ask because I have tried and I can’t get it to work but based on my understanding it should

[Steve]

Elijah_Magrane:

If I were to put MAXROW(“Production”, “Timestamp”, ([RM Inv Id] = [_THISROW].[RM Inv Id])) in a VC would a dereference expression work with to retrieve the Production[Quantity Out] from that specific row?

Yes. If the VC is named Latest, the Quantity Out column value of that row can be accessed as [Latest].[Quantity Out].

[Elijah_Magrane]

I tried. I am experiencing bizarre behavior. Not sure if it is expected…New Bug Encountered: MAXROW() VC not returning a dereference value-auto generating lists in other ta...

[peterdykstra]

@Elijah_Magrane In addition to what Steve is helping with, want to make sure you’re familiar with using the Webhook to copy rows:

• Sample App: Copy Row Workflow
• Help Article: Copying Record Workflow

[Elijah_Magrane]

Thanks that is helpful. I just wish I could solve this problem first. Been working on it way too long