Accessing previous row field data in a formula

[mrdps]

Hi

Is it possible to access data from a field in previous row inside a formula?

something like  [@field:variable1 <of previous row> ]

thanks

[Meekeee]

Hi @mrdps - Do you have a sample expected result? What type of DataPage are you using?

[futurist]

Hi,

I have come up with a formula but first you would have to have an autonumber field in your table.

 

SELECT TOP 1 Your_Field FROM Table WHERE AutoNumber_Field< target.[@field:AutoNumber_Field] ORDER BY AutoNumber_Field DESC

You can use this on your Calculated field by the way.

Hopefully this works for you!

 

[mrdps]

37 minutes ago, Meekeee said:

Hi @mrdps - Do you have a sample expected result? What type of DataPage are you using?

Hi, 

It should be something like this in excel

Thanks

 

[mrdps]

36 minutes ago, futurist said:

Hi,

I have come up with a formula but first you would have to have an autonumber field in your table.

 

SELECT TOP 1 Your_Field FROM Table WHERE AutoNumber_Field< target.[@field:AutoNumber_Field] ORDER BY AutoNumber_Field DESC

You can use this on your Calculated field by the way.

Hopefully this works for you!

 

Thank you so much

I was struggling for a while to try run your query until I realized I am on free account and cannot run queries

But still, thanks so much, at least now I know what I need to get what I want

[Aether]

Hi @mrdps,

You have 2 option for your workflow, it's either calculate the formula upon submission or create a trigger action that will generate the same output.

36 minutes ago, mrdps said:

Hi, 

It should be something like this in excel

Thanks

 

Option 1: Formula on Submission Form

This formula will select the previous value of the record. (Make sure that the "Field1" is a Autonumber Data Type OR at least have a field that will act as Autonumber): 

SELECT TOP 1 Field2 FROM [TABLE NAME] ORDER BY Field1 DESC

Then this will be the overall formula that will be placed on your Field3:

[@Field:Field1] * ISNULL((SELECT TOP 1 Field2 FROM [TABLE NAME] ORDER BY Field1 DESC),0)

=====

Option 2: Trigger Action

Use this trigger action:

And the result will be:

 

I hope this helps  

[NiceDuck]

This is not possible directly on the formula field (table level) since formula field can only refer to the values of its own record. If this is on the datapage level via calculated field, then it is possible via select statement.

If you want the values on table level, you will have to use a task or triggered action instead.