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)))
)
)
How is the data organized? What are the table and how are they related?
It might be smart to use this somewhere here:
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.
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?
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?
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.
Did you by chance miss my first reply just now, since I replied twice in a row (my bad!) ?
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.
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 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.
Thatโs great, thanks so much Marc! Sorry for the confusion, my adapted example may not have been the best. Appreciate it.
User | Count |
---|---|
40 | |
34 | |
28 | |
23 | |
17 |