Appsheet funktion individual customer price per product

[Michael_at_Graz]

Hello Communitiy.

At the moment, iam working on an combination, of CRM, Stock Management, Order and assigment management tool, based on appsheet.

Its a lot of work to do this, and every upcoming problem, get his own solution, but i cant find one, for this problem:

Our Company, has individual customer prices for individual products.

This mean;

customer one get part one for € 1.

customer two get part one for € 2

customer three has no special price for product one, so he get the selling price € 3

...

in google sheets, i have a sheet with the table assigment, where i create this formula:

=WENN(AN981>0;index('P-VKP'!$1:$1000;VERGLEICH(AM981;'P-VKP'!$B:$B;0);VERGLEICH(X981;'P-VKP'!$4:$4;0));)

this formula is searching the price per customer in the pricing table (P-VKP). in the pricing table is the first row rightwards B:B, the customer ID, and in the first split downwards 1:1000, the product ID. X981 is a copy (=AM981) of the falue from AM981. 

i have no idea, how to get this into a working appsheet, cause i have to apply the option, that first, the app checkes the customer price, and if there is no price found, it use the normal selling price, from the products table, whos the customer selling price is written in the same row, next to the product ID.

It would be greate, if someone has an idea how to solf this problem.

Many thanks in advance for your answer.

nice greets from Austria

[TeeSee1]

I am not sure how your app is exactly structured but assuming..

products: product_id, standard_price

customer prices: id, cust_id, product_id, price

order header: ord_id, cust_id

order detail: ord_det_id, ord_id, product_id, the price

then you can pull the correct price, let's say in your sales order detail row, with the following expression.

IF(
 ISNOTBLANK(
  SELECT(
   customer prices[price],
   AND(
    [product_id] = [_THISROW].[product_id],
    [cust_id] = [_THISROW].[order header].[cust_id]
   )
  )
 ),
 INDEX(
  SELECT(
   customer prices[price],
   AND(
    [product_id] = [_THISROW].[product_id],
    [cust_id] = [_THISROW].[ord_id].[cust_id]
   )
  ),
  1
 )
,
 LOOKUP([_THISROW].[product_id], "products", "product_id", "standard_price")
)

 This expression may be able to be optimized if you customer specific prices are always lower that the standard price like here.

MIN(
  SELECT(
   customer prices[price],
   AND(
    [product_id] = [_THISROW].[product_id],
    [cust_id] = [_THISROW].[order header].[cust_id]
   )
  )
 )
 +
 LIST(LOOKUP([_THISROW].[product_id], "products", "product_id", "standard_price"))
)

Hope this will help you in some way.

[Michael_at_Graz]

@TeeSee1 

Hello TeeSee1 and many thanks for your help.

Yesterday, iam searching a lot for informations about that and i found one solution that should also work:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Lookup-using-multiple-criteria/m-p/362538#M1319...

ANY(
SELECT(
Molding Manpower[Total People],
AND(
([Production Date] = [_ThisRow].[Date]),
([Shift] = [_ThisRow].[Shift]),
([Mixer Number] = [_ThisRow].[Mixer Number])),
FALSE
)
)

The background is, that i think, i have to create a new price sheet, every row based on an timestamp, and the formula, should search the newest entry, cause at the moment, the prices are changing weekly and the programm should work for the next years.

So my intention is, that i create a new price sheet like this:

Timestamp (Date of entry), Customer ID, Product ID, price

the formula should check if there is a price for the assigment customer in the price sheet, if yes, he have to choose the newest, and if its not, he choose the standart price from the table product.

if i got this working, i can make a simple new entry for a new customer price, and the history of the older prices are staying in the table. with my first intension, i loose the complete history.

Our prices are sometimes higher and lower than the standart price. so i dont know if the second one will work.

I will try your solution and came back to you 🙂

Many thanks 

[Michael_at_Graz]

Hello Community, Hello @TeeSee1 

After thinking a lot of this Problem, i can not write the code by my own.

I have restructured the Pricefile at the following:

Pricefile Columns:

Row_ID, Date Entry, Customer Number, Product ID, Customer Price.

Product table, Columns:

Row_ID, Product ID, Description, Price.

Order Table Columns:

Row-ID, Invoice ID, Customer ID, Date Order, Product, Price, 

In the Order Table, i would like, that if i choose a product by Product ID in the ref from Product ID from the table Product (that actually work) appsheet put the correct price in the price column below, by searching first the price file, if there is a entry for this customer and for the product i select, and put back the newest entry for this product, customer and the latest Date, smaller than the order Date. If Appsheet found no Entry, it give back the price from the Product table for this product, without checking the customer and Entry Date.

Like this:

IFNOTBLANK [Product] from "Ordertable" ,

search [Customer ID] "Pricefile" = [Customer ID] "Ordertable"

[Date Entry] < [Order Date]

[Product ID] "Pricefile" = [Product] "Ordertable"

give back [Price] "Pricefile"

but if no entry found in "Pricefile", search [Product ID] "Producttable" = [Product ID] "Ordertable"

and give back [Price] "Producttable"

I hope i have explained it enough to understand and i hope anyyone can help me, with writing this code, cause at my own, i dont get this done.

Many many thanks in advance for any help i get.

Nice greets from austria

[TeeSee1 · Accepted solution]

Try this and see if it works (check and make sure you have all the col names correct..)

IF(
 ISNOTBLANK(
  SELECT(
   Pricefile[Customer Price],
   AND(
    [Product ID] = [_THISROW].[Product],
    [Customer ID] = [_THISROW].[Customer ID],
    [Date Entry] <= [_THISROW].[Date Order]
   )
  ) /// Edited: was missing
 ),
 INDEX(
  SELECT(
   Pricefile[Customer Price],
   AND(
    [Product ID] = [_THISROW].[Product],
    [Customer ID] = [_THISROW].[Customer ID],
    [Date Entry] <= [_THISROW].[Date Order]
   )
  ),
  1
 )
,
 LOOKUP([_THISROW].[Product], "Product", "Product ID", "Price")
)

[Michael_at_Graz]

@TeeSee1 

Many thanks for your help.

after adding the lossing ")" it should work. (marked below)

I will complete the hole prozess within this week and after this i can confirm.

IF(
ISNOTBLANK(
SELECT(
Preisdatei [VK],
AND([Artikelnummer]=[_THISROW].[Artikelnummer],
[Kundennummer]=[_THISROW].[Kundennummer],
[Datum Preis]<=[_THISROW].[Auftrags Datum]
)*I ADD THIS ONE
)
),
INDEX(
SELECT(
Preisdatei[VK],
AND([Artikelnummer]=[_THISROW].[Artikelnummer],
[Kundennummer]=[_THISROW].[Kundennummer],
[Datum Preis]<=[_THISROW].[Auftrags Datum]
)
),
1
)
,
LOOKUP([_THISROW].[Artikelnummer],"Artikel","Artikelnummer","VK")
)