Edit - Update Previous Column

Hi all,
Here I am again.

Can I know is there any ways to update the column that the user will edit. and the new column.
Eg: Every quotation got “Status”. Might be Open, Won, Lost, Superseded and others.
Let say, current quotation the user want to edit is number GE-2103-1001-Rev.0 and the status open.
And after edit. How we want the previous quotation status will be “Superseded”

hmm, that’s tricky, but I think it’s doable… what follows is just me thinking out aloud, I haven’t actually tested the approach :wink:

  • To start with, I would separate your order number and revision number into two separate columns. So one column would have GE-2103-1001-Rev., and the other would have 0
  • Next, create a multi-relation that joins the order number to itself. This gives you a list of all rows with that order number.
  • From that multi-relation, create a rollup column that selects the maximum revision number
  • Then finally, use an if-then-else column to determine the order status: if revision number equals max revision number then Open, otherwise Superseded

As I said, I haven’t actually tested this, and it doesn’t cater for your other statuses (Won, Lost, etc), but could probably be extended to do so. You’d need to experiment with it a bit.

But from your item 3 , " ** From that multi-relation, create a rollup column that selects the maximum revision number"**
It would display the max from all the column. But it need to be display max from different quotation number right.

No, that’s the point of the relation.
Each row will have a number, but that number should correspond to the maximum version number of all rows that match the order number in that row.

It looks like this right ?
GE-2102-1034-Rev.1 should Open

Quotation No | Revision No. | Max Value | Status(if the else)
GE-2102-1033-Rev. | 0 | 2 | Superseded
GE-2102-1033-Rev. | 1 | 2 | Superseded
GE-2102-1033-Rev. | 2 | 2 | Open
GE-2102-1034-Rev. | 0 | 2 | Superseded
GE-2102-1034-Rev. | 1 | 2 | Superseded

ah, yes I see the problem. There will be a way to overcome that. I don’t have time right now, but will think on it and come back to you a bit later with a solution (if somebody else doesn’t jump in in the meantime).

1 Like

Okay, as promised here is a solution. You will need 2 additional columns:

  • A template column that joins the quotation column to the revision column
  • A second template column that joins the quotation column to the maximum revision number (ie. the rollup column)

Then change the if-then-else column so that it compares the two templates.
If they match - status is open
If they don’t match - status is superseded.

Screen Shot 2021-03-08 at 8.48.22 PM

1 Like

Hi Darren,
Seems good.
But can i know the configuration of “rel-self”?


Am i doing right?
Why mine not come out the result just like you :frowning_face:

It needs to be a multi-relation (tick the “Match multiple” box)

Thank you so much Darren.
Very smart and brilliant though !

1 Like