Changing a Row based on the equation of another ro...

Prysma_Solution · 07-23-2020 04:17 PM

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!

Steve

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?

Prysma_Solution

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.

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]
	)
)

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

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

Prysma_Solution

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?

Prysma_Solution

Certainly.

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]
)

Prysma_Solution

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

Steve

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

Prysma_Solution

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

Prysma_Solution

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.

Prysma_Solution

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.

Prysma_Solution

Certainly,

JuneCorpuz

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

Prysma_Solution

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

Prysma_Solution

I will give this a try. Thank you

Changing a Row based on the equation of another row