Copy data From above Row

What will be formula to IF column X Contains Value Yes then copy Data from above Row

@Amit_Jain
What kinda operation are you asking this? For initial value? For a conditional rule? Can you please give details?

Initial Value as well as app Formula

Which data do you want to copy from above row? and the word “above” is a bit open statement actually. Above from where? 1 row above? 2 rows above? from the row matching a criteria? Do you want to get a single value from a column? Please detail your query.

From Above 1 Row
Actually I Want to Submit a form Where may Be some Value of Form Are same As Previous Submission, so To Avoid Time I have Added Column X if Column X is Yes then Copy Data From Above 1 Row
Fields are Date and Text

@Amit_Jain
So as the form data will not be saved yet, 1 row above will be the last actual row in your table:

=IFS(
[ColumnX]="Yes",ANY(SELECT(TableName[Date],[_RowNumber]=MAX(SELECT(TableName[_RowNumber],TRUE))))
)

You can apply the same expression to the Text and these shall be Initial Value expressions

@Amit_Jain
Another approach can be setting a MAXROW expression in a virtual column and then you can de-ref this column in your form i.e.

[VirtualColumnName].[Date] and/or [VirtualColumnName].[Text]

Will let you know after Trying

Hi

Your Formula work Perfectly at the Time of Submission
But when edit submissions to Make Changes (after Some New Entries) then the Date Changes to Latest Submission Date

What I want is to Select from Above ROW Not MAX Row Number

Any Idea how to Achieve This Result

Here Is your Given Formula for Initial Value

IFS(
[ColumnX] =“Yes”,ANY(SELECT(TableName[Date],[_RowNumber]=MAX(SELECT(TableName [_RowNumber],TRUE)))))

Replace TRUE in the inner SELECT() with an expression to limit the list of row numbers to only those below this row’s (([_ROWNUMBER] < [_THISROW].[_ROWNUMBER])):

IFS(
  ([ColumnX] = "Yes"),
  ANY(
    SELECT(
      TableName[Date],
      [_ROWNUMBER]
      = MAX(
        SELECT(
          TableName[_ROWNUMBER],
          ([_ROWNUMBER] < [_THISROW].[_ROWNUMBER])
        )
      )
    )
  )
)

or, equivalently:

IFS(
  ([ColumnX] = "Yes"),
  LOOKUP(
    MAX(
      SELECT(
        TableName[_ROWNUMBER],
        ([_ROWNUMBER] < [_THISROW].[_ROWNUMBER])
      )
    ),
    "TableName",
    "_ROWNUMBER",
    "Date"
  )
)
1 Like

Given that you want multiple values from the preceding row, the most efficient approach (by a long shot) is to create a normal (non-virtual) column with the app formula:

MAXROW("MyTable", "_ROWNUMBER", ([_ROWNUMBER] < [_THISROW].[_ROWNUMBER]))

to get a reference to the row, then dereference this to get individual column values as needed. @LeventK also suggested something like this.

Hi
I used This Formula Suggested by You
IFS(
([ColumnX] = “Yes”),
ANY(
SELECT(
TableName[Date],
[_ROWNUMBER]
= MAX(
SELECT(
TableName[_ROWNUMBER],
([_ROWNUMBER] < [_THISROW].[_ROWNUMBER])
)
)
)
)
)
And this Work Perfectly no issue at all
But when I edit the Entry of ColumnX Having value “No” then date Column Get Blank it reset to Blank
(I want to have Present Value instead of Blank)

Edit
I have Entered IF Formula in Initial Value Reset

Thanks to Both of you

2 Likes

Hi I have a similar challenge. I want to pick up the value from the same column but in the row immediately above (unless the value in another column on the same row is greater than zero) AND I want it to return as a NUMBER so that I can then do some percent calculations with it. I tried the formula suggested in post 11 of this thread but like a few other solutions I came up with based on it doesn’t return a number.

Situation:
My rows represent days.
I am recording Diet informatoin each Day.
The user has a Calorie Target which is the same every day until their Dietition changes it.

I have a Data Entry column for NEW Calorie Target. It is blank most of the time but when there’s a change to the target the user enters it via the app.

I have a virtual column called CALORIE TARGET. I want Calorie Target column to pick up yesterday’s target as stored in CALORIE TARGET column on the row immediately above today’s row - except when the NEW Calorie Target column has a value on today’s row, in which case I want Calorie Target to pick up the value from New Calorie Target. This is dead easy in Excel but whenever I update the data in AppSheet, it fries the Excel formulas.

So far I have found Select and Lookup Formulas but they return as data type “List”. When I set hte column type to Number it says that isn’t accepted. T

he formula for the Calorie Target column needs to return as a NUMBER so that I can then divide the ACTUAL Calories as entered by the user each day by the Target to get a percent of target achieved. e.g. user target is 1000 calories. User eats 1200 calories result is 120%. User eats 900 calories result is 90%

Help would be appreciated.
thanks
R

Add a (preferably non-virtual) column named Prior Day with an App formula expression to identify the immediately-prior day’s row:

MAXROW("Days", ([_ROWNUMBER] < [_THISROW].[_ROWNUMBER]))

With that column in place and populated with a value, any column value from the immediately-prior day can be gotten with a simple dereference expression. For instance, to get the prior day’s Calorie Target column value:

[Prior Day].[Calorie Target]
IF(
  ISBLANK([New Calorie Target]),
  [Prior Day].[Calorie Target],
  [New Calorie Target]
)

See also:

2 Likes

Hi Steve, thanks for your advice. I read the articles but I can’t line up the info with your advice. So I tried just plugging in what you wrote but it kept saying that the MAXROWS functionI had to have two parameters.

I got past that issue by writing the formula as:
MAXROW(“DATA”,"_ROWNUMBER"([_RowNumber]<[_THISROW].[_ROWNUMBER]))

But this returned a DATE not a RowNumber and the date that it returned was the date of the last row in the table not the row above the last row.

Nevertheless, I tried plugging in your formula [Prior Day].[Calorie Target] on my Calorie Target column but that returned the error that it does not contain a reference. I tried putting in the table name as DATA[Prior Day].[Calorie Target] and as
“DATA”[Prior Day].[Calorie Target]. Neither of those worked either.

So while I am sure your solution is valid, I just don’t understand what I am doing wrong in my interpretation of it. I would post some screen shots but I don’t seem to be able to do that.

any further advice?
thanks
Rosemary

Hi Steve, Just wondering if another solution would work:. I can add a column called PRIOR DAY to my Excel Spreadsheet that is behind the DATA table in my AppSheet.

  1. Write a formula in excel that is = Value from Date Column - 1. This will generate a column that has the date of the previous day on each row. So there would be two date fields on each row: Today’s Date and Yesterday’s

  2. This will load into AppSheet because the formula is identical on every row of the Excel table.

  3. Do AppSheet’s equivalent of an Excel VLOOKUP to Lookup the Prior Day’s date in the DATE column and return the value in the Caloire Target Column of the row that matches.

Before I spend a bunch of time teaching my self how to do LOOKUP in App Sheet, can you advise if this approach would work? I know it would work in Excel.

thanks
Rosemary

Whoops! That was my mistake. Your fix is appropriate.

It should have returned the key column value of the previous row. Is the key column of the DATA table of type Date?

That certainly should not have happened!

Please post a screenshot of the Expression Assistant showing the entire MAXROW() expresion you used.

What happens when you try?

I wouldn’t advise it.

Hi Steve
Thanks for persevering with me.
When posting a screen shot I take the shot, save the file to my Dropbox and then I use the upload button on the community message and navigate to my saved image and insert it. But instead of the image a filename is displayed.

.

R

1 Like

OK now I see that it is converted to an image when I click reply. I didn’t know it would do that. :frowning: :).
R

1 Like