Solved: Error in nested conditionals - Page 2

RogerCreasy · 08-08-2024 02:40 PM

Hi All,

I am writing a quoting app. I am stuck on the bit that looks up prices for items on the quote. We custom manufacture products, each product has multiple items. We have different prices based on the product and the customer. I have the following:

IFS(
IN(LOOKUP([_THISROW].[PriceID], "Job Basics", "PriceID", "FG INV ID"), {"product1", "product2"}),
SWITCH(LOOKUP(LOOKUP([_THISROW].[PriceID], "Job Basics", "PriceID", "Customer Parent"), "Customer Groups", "ID", "Customer Name"),
"customer1", LOOKUP("item1","ContractPrices", "ID", "customer1"),
"customer2", LOOKUP("item1","ContractPrices", "ID", "customer2"),
.... through customer28,
LOOKUP("item1","ContractPrices", "ID","Generic")
),
IN(LOOKUP([_THISROW].[PriceID], "Job Basics", "PriceID", "FG INV ID"), {"product3", "product4"}),
SWITCH(LOOKUP(LOOKUP([_THISROW].[PriceID], "Job Basics", "PriceID", "Customer Parent"), "Customer Groups", "ID", "Customer Name"),
"customer1", LOOKUP("item1","ContractPrices", "ID", "customer1"),
"customer2", LOOKUP("item1","ContractPrices", "ID", "customer2"),
.... through customer28,
LOOKUP("item1","ContractPrices", "ID","Generic")
),
.... through product300

The above code is repeated for each field in a form (item1 above is the field)

I am getting an "unknown Error", and am waiting for support to figure that out.

Is there any way to avoid repeating so much code? In PHP or GOlang I would use functions and parameters. But, I can't find a way to do that in AppSheet

Can anyone suggest a different approach?

Steve

The problem appears to be the structure of your ContractPrices table. It should have only four columns: customer, product, item, and price. To get the price of a single (customer, product, item) tuple:

ANY(
  SELECT(
    ContractPrices[Price],
    AND(
      ([_THISROW].[Customer Parent] = [Customer]),
      ([_THISROW].[FG INV ID] = [Product]),
      ([_THISROW].[Item] = [Item])
    )
  )
)

View solution in original post