Limit Total Sum For Unique Items (Steve I'm Lookin...

Jordan_Davis1 · 06-11-2021 07:46 AM

Let’s say I have a fruit store and there’s a deal where if a customer buys enough of any one fruit, the total price cannot exceed $150. For example, a customer could buy 50 strawberries for a total of $50 but if they buy 200 strawberries, the price is capped at $150. However, their purchase could include a variety of different fruits so the cap only kicks in if the sum for any unique fruit exceeds $150.

I created an expression that works but once there are a lot of items in a list, the processing time is crazy and often the app throws up an error/window freezes. Is there a better way of doing this?

IFS(
SUM(SELECT(Fruit[Price],
AND(
[Category Id] = [_THISROW].[Category Id],
[Fruit ID] = [_THISROW].[Fruit ID])))>150.00,
DECIMAL(
150.00/
COUNT(SELECT(Fruit[Price],
AND(
[Category Id] = [_THISROW].[Category Id],
[Fruit ID] = [_THISROW].[Fruit ID],
[In Stock] = True)))
)
)

Marc_Dillon

How is the data organized? What are the table and how are they related?

It might be smart to use this somewhere here:

Jordan_Davis1

Bear in mind that this example is adapted for clarity, I’m not actually a fruit vendor but let’s say I have a parent table of ‘Customer’ parent table and a child table of their ‘fruit baskets’. Fruits can be added and removed before they check out. The difficulty here is that there isn’t a unique fruits table (yeah I know), instead, the ‘fruit baskets’ table contains many rows of the same fruit. I hope this makes sense.

Marc_Dillon

Further explain the data structure of the fruit basket table. Like, can a user enter a record of just 1 fruit, then a quantity, and a sub-total price is calculated there? Can a user create multiple separate fruit basket records with the same fruit, within a single Customer? Or is there perhaps a child Table underneath fruit basket as well?

So, why don’t you make one?

Marc_Dillon

Your expression reformatted for clarity

IFS(
  SUM(SELECT(
    Fruit[Price],
    AND(
      [Category Id] = [_THISROW].[Category Id],
      [Fruit ID] = [_THISROW].[Fruit ID]
    )
  ))>150.00
  ,

  DECIMAL( 
    150.00 /
    COUNT(SELECT(
      Fruit[Price],
      AND(
        [Category Id] = [_THISROW].[Category Id],
        [Fruit ID] = [_THISROW].[Fruit ID],
        [In Stock] = True
      )
    ))
  )
)

Of the Table that you’ve so far explained, in what context is this expression running? In the Customer Table?

I don’t understand why you are dividing 150 by the COUNT of the records. How does that solve your stated requirement?

Jordan_Davis1

The expression is running in the child table (‘fruit basket’). It’d make more sense to run in the Customer table but I couldn’t figure out how say “SELECT all fruits in basket and if the sum of any unique fruit is over $150 then cap at $150 and then add the total cost of the remaining fruits”.

The reason for the division is to provide an itemized cost. In other words, if there are 200 strawberries for a total of $150, each strawberry costs $1.33 whereas if you buy 1000, each strawberry costs $0.15.

Marc_Dillon

Did you by chance miss my first reply just now, since I replied twice in a row (my bad!) ?

Jordan_Davis1

Oh didn’t see that. The reason I don’t want to add an additional table is simply because of sync time, as soon as I add anything, the sync time jumps to 30 seconds plus which is too much in my case.

Perhaps my example isn’t the best, the customers can only remove or modify existing records in the child table, they can’t add records. It’s really just about whether there’s a simpler way of creating a limit on a price based on a unique id within a list of ids. That may be a better way of putting it.

If this were all in SQL i’d just add a view/temporary table of the unique items but, as always, the workarounds on appsheet are to ensure that the app sync time doesn’t go off the rails.

Marc_Dillon

That response mostly just confused me more…

I can’t imagine how you expect this to work here then.

Going in a different direction, I’ll explain how I would set this up to meet what I think your requirements are, within your described app functionality.

Have 2 Tables:

Purchase Order (“fruit basket”)
PO Line Item (“fruit” ?)

Purchase Order can be thought of just as a “folder” for the individual Line Items, but will also hold our total price, as well as reference to a Customer if need be.

Each Line Item will reference a single fruit, with a valid_if that you cannot select the same fruit twice within the same PO. You’ll also enter a quantity, and have a sub-total calculated out. IN this calculation here is where you’ll use the $150 max, it could just be:

MIN( $150 , [qty] * [fruit].[price] )

Your PO total price will be:

SUM( [Related Line Items][subtotal] )

Or, I wasn’t sure if the $150 cap was supposed to be applied over the entire PO, or not (that would seem like an amazing deal if so, so I’d doubt it, but just in case…). If so, add a Yes/No column in your Line Item Table, titled something like “Deal Reached?”, with expression:

[qty] * [fruit].[price] > 150

Then change the PO’s total calculation expression to:

IF(
  IN( TRUE , [Related Line Items][Deal Reached? ) ,
  150 ,
  SUM( [Related Line Items][subtotal] )
)

Note that I’m working with just total prices, as I assumed from the first post, but you just explained further that you’re wanting price per item. Not sure why that wasn’t mentioned from the beginning, or at what level exactly you’re looking for that, but hopefully you can use the above to figure out how to meet the needs of your situation, and change as needed.

Jordan_Davis1

That’s great, thanks so much Marc! Sorry for the confusion, my adapted example may not have been the best. Appreciate it.

Limit Total Sum For Unique Items (Steve I'm Looking At You)