Edit - Update Previous Column

Ask for Help
1

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”

2

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.

3

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.

4

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.

5

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

6

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
7

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
8

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

9

Here…

10

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

image
image1280×720 137 KB

image
image1280×720 131 KB

11

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

12

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

1 Like