Action to place values into 2 rows from 4 columns

[jonbowles84]

I havent had any luck with this so have left it in the "too hard basket" until now! 
Could you please help me with what im doing wrong here... I have a sheet like this:

And in this table there are also 2 VC's with references to another table that should be changing fairly often ("Credits" and "Delivered"). I then have an action as follows:

And a bot with an "all changes" event and this expression:

OR(
[_THISROW_BEFORE].[Credits]<>[_THISROW_AFTER].[Credits],
[_THISROW_BEFORE].[Delivered]<>[_THISROW_AFTER].[Delivered]
)

Which runs the action, however it does nothing... Any help?? 

[TeeSee1]

From what you described the sequence of changes seem as follow.

1. A table other than Chart changes
2. Changes the VCs - Credits&Dlevered - in Chart 
3. A bot detects the change in Chart and fires the action "Update Chart"

A question. The bot only fires the action when the values in Leads or Delivered are changed (= your OR expression).

In the sequence above, I did not see any changed in those cols but changed in the VCs.

Are we missing something here?

[jonbowles84]

Sorry, Im not sure what your question is. So my virtual columns in "Chart" are changing. The problem in how to update in "Chart" table:

1. Column "Leads" row 1 (first one below top, im not sure if thats ccorrect??) with "Saleable" from the "Client" table
2. Column "Leads" row 2 with "Remains" from the "Client" table
3. Column "This Week" row 1 with "Valid ThisWeek" from the "Client" table
4. Column "This Week" row 2 with "Remaining ThisWeek" from the "Client" table

[TeeSee1]

My apologies. I was completely confused.

You are updating the VC values which are in your bot's firing condition. That seems all good.

Then. the issue seems to be with your action.

Since action works on one row at a time, having two assignments to the same col seems illogical. At best the last assignment, I suppose, will overwrite your first assignment.

So I suppose you need to create two actions, one that applies to the "Saleable" row, the other to the "Remains" with one assignment to each of Leads and This Week columns and control them to act on the correct row by specifying the condition in behavior (i.e. [status]="Saleable").

Do I make more sense now?

P.S. At least the bot is kicked off, right?

[jonbowles84]

It does make sense, but how do I specify which row the data should overwrite?

[TeeSee1]

Like I said, two actions

One action to apply to the Saleable row. So you just have in your action behavior if condition - [status] = "Saleable" and the other action [status]="Remains".

Your assignment expressions are what you want for each of the rows. (Two assignment in each action.)

You need to fire both actions from the bot. This "only if this condition is true" filter should take care of the rest (apply the right action to the right row)

[jonbowles84]

Im so sorry im just not getting this 🤯, could you give me an example expression to udpate:

1. "lead" column where "status" column = "saleable" with [saleable] from the "client" table 
2. "This Week" column where "status" column = "saleable" with [Saleable ThisWeek] from the "client" table 

Sorry for being so difficult!!

[TeeSee1]

Action to apply to Saleable

condition expression:  [status] = "Saleable"

Assignment 1:  to your [Leads]  <- "Saleable" from the "Client" table

Assignment 2: to your [This Week] <- "Remains" from the "Client" table

Action to apply to Saleable

condition expression:  [status] = "Remains"

Assignment 1:  to your [Leads] <- "Valid ThisWeek" from the "Client" table

Assignment 2: to your [This Week] <- "Remaining ThisWeek" from the "Client" table

I am assuming that your assignments expressions are correct in the first place.

If you share Client table definition, it might be more helpful.

[jonbowles84]

Its still not working... so all of the 4 values are decimal type and the formulas in the actions are like this:

is "sum(Client[Remains])" the correct expression to give the value of the decimal?

[TeeSee1]

If you want to retrieve data from another table you have to use an expression of some variant to SELECT.

Could you show me your Client table definition?

[jonbowles84]

Actually the action works as it populates the values if I use the icon from appearance, so it must be the bots event trigger?
This is the event in the bot:

Does it look ok?

Whole Bot:

[TeeSee1]

OK, so the SUM expressions are OK.

Then try removing the bot's condition. It is optional. See if the bot works for any changes in Chart.

[jonbowles84]

It doesn't work with or without the expression...

[TeeSee1]

Anything in the bots Monitor?

[jonbowles84]

It shows 4 succesful actions, but they are from me runing them manually, not through the bot (I just tested to make sure)

[TeeSee1]

Hmmm,

This lists Bot trigger conditions (also cases where bots are not triggered).

I cannot tell for sure that changes VC triggered by data in another table actually triggers a bot. (I need to experiment which will take some time..)

Meanwhile, someone maybe able to shine some light on this.

[jonbowles84]

Ill try adding an actual column with the same formula in my Google sheet and see if that works...

[TeeSee1]

My experiment failed, meaning the VC change based on another table did not trigger my bot..

So need another trick.

Wait for your results..

[jonbowles84]

same result, I tried adding actual columns with formulas in google sheets, so 2 actual and 2 VC's, also tried without VCs, and with or without any expression...😑

[TeeSee1]

I was afraid of that. Cols do not automatically update.

One thing you could try is

1) add a col(could be anything) to your Chart.

2) create an action to update this col on Chart

3) create an action on Client to trigger the action in 2) - Data: execute an action on set of rows will let you do that.

4) trigger the action in 3) when you save changes (I assume you are adding or editing rows via a Form) in Client - use Behavior>>Event actions in the Form.

Does this make sense to you?

Unless someone has a better idea, this is the only thing my limited brain can come up with..

[TeeSee1]

Hi @jonbowles84 

I believe again I mis-understood you .

My final crack at this

You have tree tables as follows

• Chart
• Client
• Something else( I called it CREDITS_DELIVERED)

Here is what you want to happen

1. You change something in CREDITS_DELIVERED
2. This changes the VCs in Chart <- I am not sure if you really need this or just to trigger your bot. In order for 3) to occur, this is irrelevant.
3. The two rows in Chart get updated based on the numbers in Client

If above is what you need, you really do not need a bot.

I have created a demo here. You are welcome to take a look. Generic 3 in here

What I basically did.

1. Create two actions to update the two rows in Chart - You already have these
2. Create a Grouped: execute a sequence of actions to run both of 1)
3. Create an action in CREDITS_DELIVERD to run 2) - Data: execute an action on a set of rows
4. In the form of CREDITS_DELEVERD, set 3. in Behavior>>EVENT ACTIONS

Hope this will finally solve your case.

[jonbowles84]

Thankyou, so Ive done the grouped action and the action in CREDITS_DELIEVERED or "Data" in my case. I have also added the action to my Data_Form, Im a bit confused by it 😁 How does it trigger? It doesnt seem to be working..?

Ok, I know how it triggers, upon saving the data_form, it doesnt seem to work, ive tried adding it straight to data to with "Row_Selected" as the event, but neither seem to work...😥

[jonbowles84]

You mean this?

[jonbowles84]

Sorry, I think its just clicked after re-reading your reply: action behavior if condition - [status] = "Saleable"

[Steve]

Virtual columns cannot trigger bots; only changes to normal (non-virtual) columns can trigger bots.