Setting value according to SUM

AppSheet AppSheet Q&A
, ,
1

I have an orders app (previously moaned about in other threads) that I want a value to auto assign for delivery runs when it reaches a threshold of 20 objects.

I have an action button that sets a numerical value in a column for the delivery stop +1 for every click (formula is MAX(DATASETDelivery Stop])+1).

Each row could have up to 4 objects (which is another complication, but let’s address the principle first!)

So, when an order is assigned a delivery stop i’d like the delivery run column to claculate what delivery run it should be in.

How do i put a formula together that does the following:

  • If the row option is equal to ‘for delivery’

AND

  • The row value for delivery stop column is greater than 1

AND

  • the sum of the number of objects column when the above two conditions is greater than 20

then set the delivery run column value to 2.

I can’t quite wrap my head around it…

2

Reading between the lines…you are trying to segregate the “Delivery Stops” into “Delivery Runs” with NOT MORE than 20 items per “Delivery Run”? Is that right? I assume the “Delivery Stops” always are included sequentially?

For instance, with 4 max # of items per Order, [Delivery Run] = 1 will ALWAYS have stops of 1, 2, 3, 4 and 5 AND possibly others if the item count is less than 4 on these Orders. [Delivery Run] = 2 will have the next set of sequentially number stops where # of items is not greater than 20.

If the above is correct…I think you are on the right track BUT you need additional information, e.g. the item total so far, to help make the decision.

I would suggest adding a column to track the [Delivery Run] running total of items - maybe [Delivery Running Total]. Each time you add a stop, you either:

  1. Add it to the current [Delivery Run] if running total does NOT exceed the max Items = 20.

  2. Start a new [Delivery Run] sequence an reset the “Delivery Running Total”

These mean you will need to utilize the MAX(DATASET[Delivery Stop]) value in these other expressions like:

Setting [Delivery Run]

IF( ANY(SELECT(DATASET[Delivery Running Total], [Delivery Stop] = MAX(DATASET[Delivery Stop]))) + [_THISROW].[Number of Items] > 20,
    MAX(DATASET[Delivery Run]) + 1,
    MAX(DATASET[Delivery Run])
)

Setting [Delivery Running Total]

IF( ANY(SELECT(DATASET[Delivery Running Total], [Delivery Stop] = MAX(DATASET[Delivery Stop]))) + [_THISROW].[Number of Items] > 20,
    [_THISROW].[Number of Items],
    ANY(SELECT(DATASET[Delivery Running Total], [Delivery Stop] = MAX(DATASET[Delivery Stop]))) + [_THISROW].[Number of Items]
)

I hope this helps!

1 Like
3

Yes! This looks to be the correct logic i’m looking to deploy.

I am, however, struggling with the [Delivery Running Total] column. In effect I need the following:

SELECT

SUM([Number of Items])

WHERE

[Delivery Stop] <> 0

But i’m absolutely flumoxxed by the AppSheet syntax I need here. Using FILTER refers to a key (rownumber…) using SELECT I can’t get things to work together.

Any advice on this one?

I’m an idiot who can’t syntax. That’s the [Delivery Running Total] sorted.

4

Once i’d sorted the [Delivery Running Total] column i wrote an IF statement to cover the eventualities:

IF([Delivery Running Total]<=20
,“(01) Run 1”
,if(and([Delivery Running Total]>20
,[Delivery Running Total]<=40)
,“(02) Run 2”
,if([Delivery Running Total]>40
,“(03) Run 3”
,“-”)))

This works now.

1 Like