Jump to content
  • 0

Accessing previous row field data in a formula


mrdps

Question

6 answers to this question

Recommended Posts

  • 0

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!

 

Link to comment
Share on other sites

  • 0
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

Link to comment
Share on other sites

  • 0

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

 

ask caspio.JPG

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:
image.png.25b8e9dd538374a040705de27bf5d5f3.png

And the result will be:
image.png.02167c7e89bcf615155b8ec39df65755.png

 

I hope this helps :) 

Link to comment
Share on other sites

  • 0

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. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...