Error in nested conditionals

[RogerCreasy]

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]

Wow.

[Steve]

Here's your expression reformatted for clarity:

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"
    )
),

[Steve]

I'd think you should just have a price list table with columns for customer, product, and item. Then you could replace your entire expression with a single (or possibly two) SELECT() statement(s).

[RogerCreasy]

@Steve Thanks for looking!
Isn't the AppSheet DB Table limited to 100 columns? We have several hundred items

[Steve · Accepted solution]

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])
    )
  )
)

[RogerCreasy]

OK. That is much cleaner. 
So you propose a row for each item, and item is not unigue, right?

I was hoping to have privileged  users update the prices directly... this way I would need an app for managing prices.
Am I understanding your thoughts?

[Steve]

@RogerCreasy wrote:

So you propose a row for each item, and item is not unigue, right?

---

ID	Customer	Product	Item	Price
1	Customer1	Product1.1	Item1.1.1	$Price1.1.1
2	Customer1	Product1.1	Item1.1.2	$Price1.1.2
3	Customer2	Product2.1	Item2.1.1	$Price2.1.1
4	Customer3	Product3.1	Item3.1.1	$Price3.1.1

---

@RogerCreasy wrote:

I was hoping to have privileged  users update the prices directly... this way I would need an app for managing prices.

---

You could share this one spreadsheet with your privileged user(s).

[RogerCreasy]

OK. Downside is that item is one to many on customers and products. So, each item would have to be on a row for each customer and each product. This would be thousands of rows, even dozens of rows for each item.... I would have to create an app to manage prices. Data entry folks would be lost.

[RogerCreasy]

Can the select column be variable? I know the below doesn't work. But, any way to emulate that?

SELECT(
    ContractPrices[$Price],