Do change data workflow trigger for virtual columns

[Pratyay_Rakshit]

Can a change in a virtual column trigger a data change workflow for a real back end column?

[Steve]

Nope, only changes to non-virtual columns.

[Pratyay_Rakshit]

Then can you help me with this?

I want to change a status based on change in a column called [classification] which is virtual. The column and workflow change is related to a table called “Customers”

The formula of the [Classification] column:

ifs(
and([Current Stage]=“Lost”, IN(“Job Sold”, select(Leads[Stage], [Customer]=[_THISROW]))=FALSE), “Inactive Prospect”,
and([Current Stage]=“Lost”, IN(“Job Sold”, select(Leads[Stage], [Customer]=[_THISROW]))), “Inactive Customer”,
and([Current Stage]<>“Lost”, IN(“Job Sold”, select(Leads[Stage], [Customer]=[_THISROW]))=FALSE), “Active Prospect”,
and([Current Stage]<>“Lost”, IN(“Job Sold”, select(Leads[Stage], [Customer]=[_THISROW]))), “Active Customer”
)

The [Current stage] is a dereference.

[Current Stage]=[Key of current stage in leads].[Stage]

[Key of current stage in leads] = MAXROW(“Leads”, “_ROWNUMBER”, ([Customer]=[_THISROW]))

---

The [Key of current stage] selects that row in the ‘leads’ table which is the last record for a customer. The [Current Stage] column calculates the corresponding [Stage] in the leads table.

The logic is:

1. If the current stage is ‘lost’ and one of the stage value in the lead history is ‘sold’, then the classification is “Inactive Customer”.
2. Current Stage <> Lost, and one of the stage values is ‘sold’, then the classification = “Active Customer”
3. Current Stage = “Lost”, and not one of the stage values is ‘Sold’, then classification = “Inactive Prospect”,
4. Current stage <> Lost, and not one of the stage values is ‘Sold’, then classification = “Active prospect”

The stage values change by appropriate actions on pertinent occasions.

I have requirements such that the [Classification] column is turned into a real column which will change with data change of the [stage] column in leads table.

As of now I was trying to trigger the workflow by invoking the change based on the virtual column value, but it would not work, as pointed out by @Steve.

So I input one of the ifs condition directly into the change workflow condition.

and([Current Stage]=“Lost”, IN(“Job Sold”, select(Leads[Stage], [Customer]=[_THISROW]))=FALSE)

Which is intended to turn the real column status to “Inactive Prospect”

But the [Current Stage] as I had defined, is a virtual column as a derference using another virtual column. Which should also not work. And it does not work. So the theory stands.

What I think would work here hypothetically is :

and([MAXROW(“Leads”, “_ROWNUMBER”, ([Customer]=[_THISROW]))].[Stage]="Lost", IN(“Job Sold”, select(Leads[Stage], [Customer]=[_THISROW]))=FALSE)

But I cant happen to make the block [MAXROW(“Leads”, “_ROWNUMBER”, ([Customer]=[_THISROW]))].[Stage]="Lost" work. I want to directly calculate the dereference. But this particular formula is not allowing currently.

PLEASE HELP. I hope this was not too big. But I had to lay out all the pertinent information.

[Steve]

Pratyay_Rakshit:

ifs(
and([Current Stage]=“Lost”, IN(“Job Sold”, select(Leads[Stage], [Customer]=[_THISROW]))=FALSE), “Inactive Prospect”,
and([Current Stage]=“Lost”, IN(“Job Sold”, select(Leads[Stage], [Customer]=[_THISROW]))), “Inactive Customer”,
and([Current Stage]<>“Lost”, IN(“Job Sold”, select(Leads[Stage], [Customer]=[_THISROW]))=FALSE), “Active Prospect”,
and([Current Stage]<>“Lost”, IN(“Job Sold”, select(Leads[Stage], [Customer]=[_THISROW]))), “Active Customer”
)

Reformatted:

IFS(
  AND(
    ([Current Stage] = “Lost”),
    IN(
      “Job Sold”,
      SELECT(
        Leads[Stage],
        ([Customer] = [_THISROW])
      )
    )
    = FALSE
  ),
    “Inactive Prospect”,
  AND(
    ([Current Stage] = “Lost”),
    IN(
      “Job Sold”,
      SELECT(
        Leads[Stage],
        ([Customer] = [_THISROW])
      )
    )
  ),
    “Inactive Customer”,
  AND(
    ([Current Stage] <> “Lost”),
    IN(
      “Job Sold”,
      SELECT(
        Leads[Stage],
        ([Customer] = [_THISROW])
      )
    )
    =FALSE
  ),
    “Active Prospect”,
  AND(
    ([Current Stage] <> “Lost”),
    IN(
      “Job Sold”,
      SELECT(
        Leads[Stage],
        ([Customer] = [_THISROW])
      )
    )
  ),
    “Active Customer”
)

Simplified:

IF(
  ([Current Stage] = “Lost”),
  IF(
    IN(
      “Job Sold”,
      SELECT(
        Leads[Stage],
        ([Customer] = [_THISROW])
      )
    ),
    “Inactive Customer”,
    “Inactive Prospect”
  ),
  IF(
    IN(
      “Job Sold”,
      SELECT(
        Leads[Stage],
        ([Customer] = [_THISROW])
      )
    ),
    “Active Customer”,
    “Active Prospect”
  )
)

Or even:

IF(
  IN(
    “Job Sold”,
    SELECT(
      Leads[Stage],
      ([Customer] = [_THISROW])
    )
  ),
  IF(
    ([Current Stage] = “Lost”),
    “Inactive Customer”,
    “Inactive Prospect”
  ),
  IF(
    ([Current Stage] = “Lost”),
    “Active Prospect”,
    “Active Customer”
  )
)

[Pratyay_Rakshit]

Okay, but would this solve what I’m ultimately trying to achieve?

[Steve]

Nope, but I need to understand what that expression was doing, and thought I’d share my observations.

[Steve]

So when a row’s stage column value changes, the Classification column values that are based that particular row’s stage column value need to be updated?

[Pratyay_Rakshit]

Classification column and stage column are from different tables - customers and leads respectively.

The lead has a ref to the customers table, since leads are entered against the customers.

When the Leads table’s [stage] value changes, that should update the [classification] column in the customers table. Though [classification] is a virtual column calculated from the above mentioned formula. It gets updated real time.

But what I’m specifically requested is to have the changes incorporated in a real backend column, say [Status], for the customers table. One thing that I should have mentioned that the [Status] column is of initial value “Active Prospect”

[Steve]

So the user or some other in-app process updates a Leads row with a new Stage column value. The Leads row references a single Customers table row via Customer. That referenced Customers row needs to be updated so its Classification column value is recalculated to reflect the new Stage column value in the Leads row. Yes?

[Pratyay_Rakshit]

Yes.

But its based on two criteria.

1. The value of the stage column in the latest lead for the customer.
2. Whether or not the stage “Job sold” occurs at least once in the lead history for the customer.

[Steve · Accepted solution]

1. I think you can/should do away with the virtual column, and make Classification a real column.

2. Create an action for the Customers table of type Data: set the values of some columns in this row that recomputes the row’s Classification column value.

3. Create an action for the Leads table of type Data: execute an action on a set of rows with a referenced table of Customers, a referenced rows of LIST([Customer]), a referenced action of the Customers table action in (2) above, and a condition expression that checks those two criteria you mention.

4. Attach that Leads table action from (3) to the Form Saved event action in whatever Leads table form users use to change the Stage column.

5. And/or attach the action from (3) to any other actions that modify the Leads table.

6. Alternatively, don’t do (4) and/or (5), but instead attach the action from (3) to a workflow that monitors for changes in Leads.

[Pratyay_Rakshit]

The two criteria that I mentioned can output 2 x 2 = 4 different conditions of validation.

What you mentioned in (2), about recomputing the Classification column, it should be able to recompute to one of four values. (Active Prospect, Inactive Prospect, Actibe Customer, Inactive customer).

So do I need 4 different actions? But then, how would I attach 4 actions to a workflow?

Grouped action would not work in this case I think, since all actions are based on different values of the same condition operator

[Steve]

Pratyay_Rakshit:

So do I need 4 different actions?

Not at all. One action that computes the value. The expression I reformatted and simplified above.

[Pratyay_Rakshit]

Thanks man. You’re a saviour