How to handle this case (loop)

[Quentin_Decaune]

Hi,

• I have a list of “Month” with a “beginning amount” property, this property should match the “end amount” of the previous month (“end amount” is virtual, “beginning amount” is not).
• Each month has some element, and on element update, I have to update the current month’s “end amount” (already done automatically as it’s a virtual column).
• By now, you probably guess what’s the problem: each month superior to the current one should update their “beginning amount” to match the previous month’s “end amount”.

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...

[Aurelien]

Hi @Quentin_Decaunes,

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

[Quentin_Decaune]

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.

[Aurelien]

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"

help.appsheet.com

SUM()

Arithmetic sum of list items

[Quentin_Decaune]

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.

[Aurelien]

Can you share your table structure ?
I’m afraid i have not enough information to provide an efficient solution

[Quentin_Decaune]

Let’s be precise then:

• Bank lines has Price(Price) property
• Ignore Budget for now (let’s admit “Month has Many Bank Line” like budget didn’t exist)
• Month as a Name(Date) property
• Month as a Beggining Amount(Price) property
• Month as a End Amount(Price) property (Virtual, [_THISROW].[Beggining Amount] + SUM([_THISROW].[Bank Lines].[Price]))

My needs:
When I create a Bank Line:

• for the given Month, I should display the correct “End Amount”
  • This is OK, as it’s a virtual column
• for all futur Month as the one referenced by the created Bank Line, I should update the correct “Beggining Amount”
  • This is the subject, as each Month[Beggining Amount] should know what’s the value of the previous month. It must be calculated in the right order.

[Aurelien · Accepted 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:

• in the Bank Line Table, you have one more column, that refers to Month table.
• in Month Table, the column [Bank Lines] was initially [Related Bank Lines] and is a List of Ref to Bank Line Table

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

[Quentin_Decaune]

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 😃

[Aurelien]

Quentin_Decaunes:

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.

Yes, and the expression I provide do this

let’s assume:

• monthA starts 0 and make 3
• monthB starts at 3, make 1 ==> end of month 4.
  similarly, monthB will make : 3+1 ==> end of month 4 as well
• month C starts at 4, make 2 ==> enf of months 6
  similarly, monthC will make 3+1+2 ==> end of month 6.

If I add a bank line that adds 5 to month A, then I will get:

• monthA starts 0 and make 3+5 ==> 8
• month B starts at 3+5 = 8 and make 1 ==> 3+5+1 =9
• month C starts at 3+5+1=9, and make 2 ==> 3+5+1+2=11

[Aurelien]

Quentin_Decaunes:

EDIT:
I’m sorry I didn’t realize the usage of the new virtual column.
I’ll try what you said 😃

ok, let us know

[Quentin_Decaune]

It works Thanks.

[Aurelien]

Very nice, I’m glad to read that works