Hi,
I tried a simple “update a set of rows”, but it seems to happen simultaneously, therefore results are wrong. I tried to create a bot, with a set of conditions which calls a second bot that does the same and repeats. But ended up with an error “Cycle detected in processes”.
I looked up some “tips and tricks” about loops, but they all seem overpowered for my needs.
Does anyone have an idea on how to handle this case? Thanks 😃
Related feature requests: Add a Simple FOR LOOP condition to allow for Repeating a Sequence of Actions like Multiple Rows in a...
Solved! Go to Solution.
Thank you for sharing.
First, I think you can simplify your expression:
[_THISROW].[Beggining Amount] + SUM([_THISROW].[Bank Lines].[Price]
into:
[Beggining Amount] + SUM([Bank Lines].[Price]
I think some information is missing, but given the informations you provide, let’s assume:
If so, your expression End Amount
should be:
[Beginning Amount] + SUM([Bank Lines][Price])
(without the dot)
My suggestion:
You should turn the [Beginning Amount] from a real column into a Virtual one.
This way, that allows to split calculations and make it in real-time, without any bot involved.
Then, use these expressions:
Month Amount
(new virtual column)
SUM([Bank Lines][Price]
Beginning Amount
(virtual column)
SUM(
SELECT(
Month[Month Amount],
[Name]< [_THISROW].[Name]
)
)
End Amount
(virtual column)
[Beginning Amount]+[Month Amount]
or, alternatively:
SUM(
SELECT(
Month[Month Amount],
[Name]<= [_THISROW].[Name]
)
)
(with the <= sign)
Let us know if that works for you.
EDIT : minor changes
I might miss something, but why don’t you use a real column aside your virtual column, and update the real one depending on the virtual one with your bot ?
This way, you won’t have any sync/loop issue I think
The problem would remain the same as I need to update all subsequent months with their previous month synchronously.
Let’s simplify scheme for simplicity:
- Month A
- Amount A
- Ref elements
- Month B
- Amount B
- Ref elements
- Month C
- Amount C
- Ref elements
Here’s the use case I need:
- Let’s say I start without elements, every Amounts are 0 (zero).
- I add 1 element to Month A
- Amount A becomes 1
- Amount B becomes 1
- Amount C becomes 1
- I add 1 element to Month B
- Amount A stays 1
- Amount B becomes 2
- Amount C becomes 2
- I add 1 element to Month A (again)
- Amount A becomes 2
- Amount B becomes 3
- Amount C becomes 3
With the actual behaviors and bots, I can’t loop synchronously, therefore I get this:
- Let’s say I start without elements, every Amounts are 0 (zero).
- I add 1 element to Month A
- Amount A becomes 1
- Amount B becomes 0
- Amount C becomes 0
- I add 1 element to Month B
- Amount A stays 1
- Amount B becomes 2
- Amount C becomes 0
- I add 1 element to Month A (again)
- Amount A becomes 2
- Amount B becomes 2
- Amount C becomes 2
Calculations are done with the current elements count but the previous month’s state (before they are updated by behaviors/bots themselves), therefore I’m getting an “out-of-sync” calculation.
Did you try a SUM/SELECT expression for your beginning expression ?
Something like:
SUM(
SELECT(
yourTable[Amount],
[Month]< [_THISROW].[Month]
)
)
Please see:
in the section " Sum Values from Select Rows"
Yes I tried, (and my request looks like this btw)
The problem is not “how to calculate”, I know for a fact that requests are good.
The problem is the process of how to do those requests one after another.
For now, they seem to be done asynchronously which creates wrong results.
Can you share your table structure ?
I’m afraid i have not enough information to provide an efficient solution
Let’s be precise then:
[_THISROW].[Beggining Amount] + SUM([_THISROW].[Bank Lines].[Price])
)My needs:
When I create a Bank Line:
Month[Beggining Amount]
should know what’s the value of the previous month. It must be calculated in the right order.Thank you for sharing.
First, I think you can simplify your expression:
[_THISROW].[Beggining Amount] + SUM([_THISROW].[Bank Lines].[Price]
into:
[Beggining Amount] + SUM([Bank Lines].[Price]
I think some information is missing, but given the informations you provide, let’s assume:
If so, your expression End Amount
should be:
[Beginning Amount] + SUM([Bank Lines][Price])
(without the dot)
My suggestion:
You should turn the [Beginning Amount] from a real column into a Virtual one.
This way, that allows to split calculations and make it in real-time, without any bot involved.
Then, use these expressions:
Month Amount
(new virtual column)
SUM([Bank Lines][Price]
Beginning Amount
(virtual column)
SUM(
SELECT(
Month[Month Amount],
[Name]< [_THISROW].[Name]
)
)
End Amount
(virtual column)
[Beginning Amount]+[Month Amount]
or, alternatively:
SUM(
SELECT(
Month[Month Amount],
[Name]<= [_THISROW].[Name]
)
)
(with the <= sign)
Let us know if that works for you.
EDIT : minor changes
First, I think you can simplify your expression: …
Yes, it’s just that I wrote it from memory instead of copy-paste from the app, sorry for the typos.
My suggestion: …
It seems I wasn’t clear enough. An important detail: Beginning amount
is not only SUM(...)
but PreviousMonth[EndAmount] + SUM(...)
A month starts with what remains of the previous month.
EDIT:
I’m sorry I didn’t realize the usage of the new virtual column.
I’ll try what you said 😃
Yes, and the expression I provide do this
let’s assume:
If I add a bank line that adds 5 to month A, then I will get:
ok, let us know
It works Thanks.
Very nice, I’m glad to read that works
User | Count |
---|---|
43 | |
27 | |
24 | |
23 | |
13 |