I tried to set the data type to capture data ...

(Prayoon Sonjai) #1

I tried to set the data type to capture data and compare with “Low” and “High” values as below (Data can be displayed with & without decimal in AppSheet).

But I found that the calculation in AppSheet will only work if the data type of “Data” set to either;

  • Number - but I can’t put decimal value. - Decimal - but it will impact other values to display like 7.00 which I don’t want.

Any idea or workaround that I can have the same outcome like in GoogleShee?, Thanks.

(Aleksi Alkio) #2

If you want to keep the value as number or decimal, we don’t have direct way to do that.

(Aleksi Alkio) #3

IFS(Decimal([Data])<[Low],“Low”,Decimal([Data])>[High],“High”)

(Prayoon Sonjai) #4

@Aleksi_Alkio Now it works!! you helped me twice after the “initial value” question !! Big Thanks.

(Aleksi Alkio) #5

You’re welcome

(Prayoon Sonjai) #6

One more time @Aleksi_Alkio the condition above (with App Formula) works for “Update” only because we already have “Low” and “High” data to compare.

But for adding new data, I think the App Formula is faster than Spreadsheet formula that I use to get the “Low” and “High” data (vlookup(RC5&RC3&RC4,‘Other Table’!R2C1:R999999C8,7,0) that cause the calculation is wrong for new data.

Is it possible to put vlookup in the App Formula? because I

tried to add to the “Low” and “High” column but seems not working…

(Prayoon Sonjai) #7

@Aleksi_Alkio Please ignore my previous question above, I found the way to add LOOKUP into the App Formula now everything is solved for both Adding & Updating :slight_smile:

(Prayoon Sonjai) #8

@Aleksi_Alkio any indirect way,put some script on Data?

(Aleksi Alkio) #9

Script won’t help, but you could use TEXT column for this purpose and if you need to calculate with that value, you can use DECIMAL([Column]).

(Prayoon Sonjai) #10

@Aleksi_Alkio I set “Data” to be Text, and “Low” & “High” to be Decimal like below on Spreadsheet formula; ========== IFS(RC(Decimal([Data]))<RC[Low],“Low”,RC(Decimal([Data]))>RC[High],“High”) ========== But I think it still doesn’t work…

(Aleksi Alkio) #11

It doesn’t work in what way?

(Prayoon Sonjai) #12

@Aleksi_Alkio The calculation is still wrong, when I put lower or higher value in Data field, the Compare field still not change…

(Aleksi Alkio) #13

What is the meaning of “RC” in here?

(Aleksi Alkio) #14

You want to use sheet formula instead of an app formula?

(Prayoon Sonjai) #15

@Aleksi_Alkio Actually I don’t mind, because I tried App formula sometime it doesn’t work…

(Jennifer Clem) #16

Following…I’ve been wondering the same thing