Calculated field according to conditions

[kasa1982]

Hi everybody,

I would like to know if there is a possibility for a field to be calculated automatically according to some conditions. For instance.

1. I have the sallers table and the field [CALCULATED] yes/no type.
2. I have the order items table which has the field [UNIT_PRICE]. This field is currently receiving a formula that comes from the spreadsheet which fetches the unit price value of the product from the [PRICES] table.

What I need?

1. Automatically calculate the price of products when the CALCULATED field of the sallers table is equal to NO. When YES, allow the seller to enter a value in this field.

Look my table order items to see the formula I've done.

The complete formula is:

VLOOKUP(RC[-6],'PRICES'!R1C1:R10C31,MATCH(CONCATENATE(RC[-5]," ",RC[-2]," ",RC[-4]),'PRICES'!R1:R1,0),0)

It is possible?

[dbaum]

Any given column can be populated via only one the following mechanisms:

• user entry (in this case, an initial value expression can be defined that populates the column unless a user overrides it; and, if the column is never shown, that value can’t be overridden by users)
• App formula
• Spreadsheet formula

it sounds like you need something like the following:

• Show/hide the user-entry column based on the Calculated column’s value
• Hide the spreadsheet-formula column
• creat a new column for its an app formula that, based on the Calculated column’s value, takes its value either from the user-entry column or from the spreadsheet-formula column; use this column for display and subsequent calculations 

[kasa1982]

Sorry @dbaum I didn't undertand you. Could you exemplify?

[dbaum]

Here's a general pattern you can adapt to the specifics of your app.

• [UNIT_PRICE_user]: new column
  • Show_if property: [Order_ID].[CALCULATED]
• [UNIT_PRICE_spreadsheet]: rename existing [UNIT_PRICE] column
  • Show_if property: FALSE
• [UNIT_PRICE]: new column
  • App_formula property: IF([Order_ID].[CALCULATED], [UNIT_PRICE_user], [UNIT_PRICE_spreadsheet])

Another approach could be to stick with a single [UNIT_PRICE] column, but not use a spreadsheet formula:

• Initial_value property: IFS(NOT([Order_ID].[CALCULATED]), {replicate the spreadsheet formula here, referencing price data in tables within your app}).
• Editable_if property: [Order_ID].[CALCULATED]

[kasa1982]

I will try and tell you if it´s runnig

[kasa1982]

Dear Mr. @dbaum , 

I'will need a little bit of you. I am assuming your final suggestion, but when I tried to adapt the formula I saw this error:

Vendedores = Is the same of Sellers table;
[REDE] = is the equivalent of the [CALCULATED] field, as informed previously. The field [REDE] is of the yes/no type.

Preços = prices table

hpedidos = order header table

dPedidos = order items

My Sellers table:

My hPedidos table

My dPedidos table

How can I fix this?

 

[kasa1982]

I've tested this formula instead of:

IFS(NOT(IN(USEREMAIL(), SELECT(Vendedores[EMAIL],([REDE] = TRUE), TRUE))),VLOOKUP(RC[-6],'Preços'!R1C1:R10C31,MATCH(CONCATENATE(RC[-5]," ",RC[-2]," ",RC[-4]),'Preços'!R1:R1,0),0))

However, the error persist once the table PRICE has many coluns as follows:

Price table:

 

[dbaum]

Your verbatim formula from the spreadsheet won't work as an expression in AppSheet. You need to adapt your spreadsheet formula that uses spreadsheet functions into an AppSheet expression that uses AppSheet functions.

[kasa1982]

Mr @dbaum Good Evening,

Any ideia to how use it in this spreadsheet formula?

It is an array, but I have no ideia of how to convert it.

[dbaum]

No. I don't know enough about the data structure of your spreadsheet or you app to parse your spreadsheet formula or draft an expression for an App formula.

In case you haven't already found it, here's a great guide to using many AppSheet functions that are likely relevant to your need.