How do I add data to a new row and to a previous row?

[eric_niedergang]

Hi everybody,

I am looking for the best way to do this :
I want to fill in a form with datas from the sale of a newspaper
One field represents the newspapers I bring to a vendor
Another field represents the unsold newspapers from the previous issue that I take back
So the number of unsold newspapers should fill the previous row

How to do this as implicitly as possible?

Thanks

[TeeSee1]

1) Enter the unsold qty to a sales row

2) When you save this row, invoke an action (note both tables are the same)

You can choose which fields to copy by the LINKTOFORM function parameters.

by setting this action in this form view's

Note: This repeats endlessly until you press cancel.

Hope this is what you are looking for.

[graham_howe]

Assume the following:

table: newspapers, with key field: [issue_id] and a field called [previous_unsold] which is populated on each record with the unsold newspapers from the previous issue. You should then be able to add a virtual column with the following formula:

lookup(
  minrow(
    "newspapers",
    "_ROWNUMBER",
    ([_THISROW].[_ROWNUMBER] < [_ROWNUMBER])
  ),
  "newspapers",
  "issue_id",
  "previous_unsold"
)

[eric_niedergang]

Hi Graham,

I was looking something like that.

But I don't want to use a virtual column,  I have a "Issue Sold" column. I was thinking to autocompute the value with an formula ?

Thanks

[graham_howe · Accepted solution]

I think you'll need a virtual column as a normal column with a formula will only be populated when the record (previous one) is updated. A VC is always calculated any time it is displayed. Another option would be to have the column value calculated in the spreadsheet, then it will update after each sync.

[eric_niedergang]

Finally, i do the calculation in the spreadsheet with a QUERY formula

👍