Changing a Row based on the equation of another row

[anonymous]

Hello all,

I am new to writing long form expressions and linking data together within Appsheet and I was wondering if any of you could point me in the right direction.

In the example screenshot, I am trying to have Column A be changed to what is listed for Column D. Column A is a REF to another sheet. Column B is also a REF to the same sheet as Column A. Column C is the data being input by me and Column D is a subtraction expression that is (Column B - Column C). I would like for it to function as (Column B - Column C = Column D, which changes the value of Column B when saved.) Is there a way to make something like that work? In my limited experience, I have not had any luck. Any help would be appreciated.

Thanks!

[JuneCorpuz]

Based on my understanding, you are in Sheet B, and you want the value of Column B on Sheet A to have the value of Column D after saving?

[anonymous]

That is correct.

[JuneCorpuz]

I’ve done this before but setting the value to a specific word but not by using a formula. You can try creating something like this.

1. On Sheet A, create an action Data: set the values of some columns in this row

Column B =

ANY(
	SELECT(
		Sheet B[Column D],
		Column A = [_THISROW].[Column A]
	)
)

1. On Sheet B, create an action Data: execute an action on a set of rows.

Referenced Table: Sheet A
Reference Rows:

> SELECT(> Sheet A[key],> Column A = [_THISROW].[Column A]> )> >

Referenced Action: name of the action you made on Sheet A

1. Go to UX, find the name of your form for Sheet B, go to Behavior section, and change the Event Action to the name of the action you made on Sheet B

[anonymous]

I will give this a try. Thank you

[anonymous]

I finally had a chance to try this out. I have followed it as you have it written. I am showing no errors, however, when I click save and then go check the value of Column B on Sheet A, it is still unchanged. Below is how I have written out.

SHEET A
ANY(
SELECT(
terrell_zeigler_parts_usage_batch[Part Number #1 Total],
Part Number #1 = [_THISROW].[Part Number #1]
)
)

SHEET B
SELECT(
terrell_zeigler[Part Number],
Part Number #1 = [_THISROW].[Part Number #1]
)

[JuneCorpuz]

Can you please share a screenshot of your actions?

[Steve]

Try these:

ANY(
SELECT(
terrell_zeigler_parts_usage_batch[Part Number #1 Total],
[Part Number #1] = [_THISROW].[Part Number #1]
)
)

and:

SELECT(
terrell_zeigler[Part Number],
[Part Number #1] = [_THISROW].[Part Number #1]
)

[anonymous]

Good morning Steve,

I have it set up as you have posted, but now I am getting the following prompt
“Action Failed” “Quantity is required”
I have deselected the required for all of the columns in this equation and it still comes up

[anonymous]

Certainly.

[Steve]

Your expression is likely finding no matching rows, or the matching row has no value.

[JuneCorpuz]

Prysma_Solutions:

I have deselected the required for all of the columns in this equation and it still comes up

Are you running the test action #1 or #2?

[anonymous]

June_Corpuz:

Prysma_Solutions:> > > I have deselected the required for all of the columns in this equation and it still comes up> > Are you running the test action #1 or #2?

Either or. If I hit Action #1 or #2 that prompt shows up.

[anonymous]

Steve:

Your expression is likely finding no matching rows, or the matching row has no value.

I was able to solve this issue and get the actions to do what I was looking for the most part.

ANY(
SELECT(
terrell_zeigler_parts_usage_batch[Part Number #1 Total],
[Part Number #1] = [_THISROW].[Part Number]
)
)

SELECT(
terrell_zeigler[Part Number],
[Part Number #1] = [_THISROW].[Part Number #1]
)

Thank you very much for your help with this. Now to figure out why it is now deleting the quantities of other referenced rows

[anonymous]

Hey Steve,

as i had responded a while back, I have been able to have this application remove the quantity based on these expressions

ANY(
SELECT(
terrell_zeigler_parts_usage_batch[Part Number #1 Total],
[Part Number #1] = [_THISROW].[Part Number]
)
)

SELECT(
terrell_zeigler[Part Number],
[Part Number #1] = [_THISROW].[Part Number #1]
)

The problem I have now is, it removes the “Quantity” from every row. So now I am left with the correct action taken place in the row I am working in, but all other quantities are also erased and have no value. I have attached before and after pictures to better show what I mean. I have tried to fix it, since we have last spoke, but haven’t come up with anything that has worked. Any ideas?

[Steve]

Please summarize your current problem. I’m not inclined to go back and re-read the entire thread to come back up to speed.

[anonymous]

I was trying to create actions to change data in one column based on another. Here was the original way I had phrased it
“I am trying to have Column B be changed to what is listed for Column D. Column A is a REF to another sheet. Column B is also a REF to the same sheet as Column A. Column C is the data being input by me and Column D is a subtraction expression that is (Column B - Column C). I would like for it to function as (Column B - Column C = Column D, which changes the value of Column B when saved.)”
I have been able to get it to work this way, but the problem I am having is when the app now changes Column B in the row I’m working on, it changes Column B for all rows in the sheet with it.

[Steve]

Please post screenshots of all the actions involved in changing column B as part of this process.

[anonymous]

Certainly,