Solved: Copy data From above Row

Amit_Jain1 · 03-25-2019 05:44 AM

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

Steve

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

View solution in original post

LeventK

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

Amit_Jain1

Initial Value as well as app Formula

LeventK

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.

Amit_Jain1

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

Steve

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.

LeventK

@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_Jain1

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

Steve

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

Amit_Jain1

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

LeventK

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

Amit_Jain1

Will let you know after Trying

Rosemary

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

Steve

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:

Rosemary

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

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.

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
This will load into AppSheet because the formula is identical on every row of the Excel table.
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

Steve

I wouldn’t advise it.

Rosemary

Hi Steve,
I had a crack at using the Lookup it because I had some time. It doesn’t work. It treats my strategy as a circular reference because PRIOR CALS column gets its value from CALORIE TARGET and CALORIE TARGET gets its value from PRIOR CALS.

I was aware of that risk and so I tried to use _THISROW to get it to differentiate the data calls by both row and column (equivalent of a ‘cell’ in Excel) . I thought this would make the formulas logically independent from each other but Appsheet was having none of that!.

PRIOR CAL TARGET Formula
LOOKUP([_THISROW].[PRIOR ROW ADJ],“DATA”,"_RowNumber"," “CALTARG”)

CALTARG Formula
if(data[_thisrow].[ New Cal Targ ]>0,[ New Cal Targ],data[_thisrow ].[PRIOR CAL TARGET])

So now I will re-try your strategy with those refinements you sent.
cheers
Rosemary

Steve

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?

Rosemary

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

Rosemary

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

Rosemary

Hi Steve,
Here’s the MAXROW function returning DATE not ROWNUMBER.
You can see in the formula evaluation it inserts ‘DATE’ even though I haven’t mentioned date in the formula and you can see below that [_RowNumber] is Type Number.

Cheers
R

Rosemary

Hi Steve,
I think even if MAXROW had returned a number it would have been the same number on every row and that wouldn’t have worked for my objective anyway. For one thing it would change all my previous targets to the new one, which I don’t want and besides, I think I would still hit up against the circular reference issue.

So I’m gonna try the old START DATE/END DATE approach.

Make another table called NEW_CAL_TARGETS to store Start and End Dates against each new Calorie Target.
Set up the Data Entry field on the app to point to this table and require the user to enter Start and End Dates when entering a new target.
Then write a formula on the CALORIE TARGET column in tbl.DATA that selects the target from tbl.NEW_CAL_TARGETS, based on the DATE showing on the ROW in tbl.DATA.

Used to do this a lot in the '90ies with Oracle Databases that were very Code Table dependent.
Got out of the habit when I moved to SQL so sort of forgot about this approach. When I started with AppSheet a few weeks back I thought it was integrated with Excel and that Excel approaches applied in AppSheet but I’ve gotten over that now!

If this approach works I will post it here. If it doesn’t, I’ll open a new question as it will be about completely different issues.

Thanks for your interest in my little conundrum
Cheers
Rosemary

Steve

Missing comma between " and (:

Rosemary

Thanks Steve,
Adding that comma did fix the issue of getting the previous day’s row number (allbeit displayed as a date) onto each row.

But the underlying issue of not being able to use the previous day’s CALORIE TARGET as today’s CALORIE TARGET remains. AppSheet just says my formula is part of a cycle (circular reference) because I can’t write the syntax to get it to write the value from the CALORIE TARGET column but on the PREVIOUS ROW into the CALORIE TARGET column on TODAY’S ROW.

In excel I would just reference the cell above in the SAME COLUMN unless there is a value in the adjacent cell, like this:

Is there really no way in AppSheet to do that?

thanks
Rosemary

Steve

Going back to my original reply:

Steve:

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], ([_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]

Rosemary

Thanks Steve,
Yes sorry I tried to be clear in my previous response.
The problem with [PRIOR DAY].[CALORIE TARGET] Is that I need to put that formula in the CALORIE TARGET column and AppSheet regards that as a circular reference. I would be delighted if this could work. I would be delighted if this could work but I have tried and tried and tried and I just get the same result: “Formula appears to be part of cycle”. I will post some more screen shots when I get home later today.

Steve

Don’t use a virtual column; use a regular column and put the expression in the Initial value.

Rosemary

Hi Steve, thanks for the tip about putting the formula in the Initial Value area. That seems to have done the trick!

It took quite a bit of messing about. I had to have data in the excel file for all past dates and then test with future dates only. And I had to change my UX to collect the new calorie target at the same time that the days’ data is entered. It doesn’t work if you edit the target a few days back. I guess that’s because it is an Initial Value formula and not an App Formula. But now that I have understood that it is working properly.

Thank you so very much. I was nearly going to abandon this platform for one that more directly works with Excel, but I stuck at it because I really like the front end and the UX design.

Thanks again. Much appreciated.
Rosemary