Hi, I want to capture a number in a column, ...

[Ct_Chung]

Hi,

I want to capture a number in a column, is there a way to limit or control this number with a range that is dynamic depending on a few conditions example from data of other table?

CT

[Ct_Chung]

@Aleksi_Alkio Hi Aleksi, I have the following formula in the Valid_If field of the column

=[Qty] <= IFS([D_truck] = “WWS 1”, SUM(SELECT(Products[T1_F], [P.ID] = [_THISROW].[P.Id]))…

Basically it checks the condition if the quantity entered is within the allowed range with reference to a certain product ID.

This entry is to be made in a 2nd table that contains the detail of the order.

D_truck is value from a V_lookup formula implemented in Google Sheet.

Products[T1_F] is a Virtual column where calculation is performed from another two Virtual columns on the same table Products.

What I found out is that with the above implementation, the condition or checking will not work unless, I first save the entry with a zero value and modify it a second time then it works. What is causing it not to work when the row is first created? Some value not available for the evaluation yet? Thanks.

[Aleksi_Alkio]

Easiest solution is writing a suitable validation formula into that column’s Valid_If field.

[Aleksi_Alkio]

Sorry… I was trying to mean “Why”… not "Where.

[Ct_Chung]

@Aleksi_Alkio ah ok, I want to prevent wrong data to be entered. For example, entering a stock value greater than that in the warehouse. So the condition needs to take into consideration of data from other table

[Aleksi_Alkio]

Would it be possible to write just… [Qty] <= SUM(SELECT(Products[T1_F],[P.ID] = [_THISROW].[P.Id]))

[Ct_Chung]

@Aleksi_Alkio [T1_F] is the column that is specifically related to the “WWS 1”, so my intention is to have IFS that check [D_truck] and if condition WWS1 then sum [T1_F], WWS 2 then sum [T2_F] etc… so as per my current column structure, the above won’t satisfy my requirement…

[Aleksi_Alkio]

Write a formula (virtual column) with just the SUM and see what is the result in the form. Then you can have an idea what is causing this behavior.

[Ct_Chung]

@Aleksi_Alkio Hi Aleksi, I place the virtual column in the 2nd table. I use a IFS statement to capture two conditions WWS 1 and WWS 2, giving them different value so that I can identify them separately. The result return in the virtual columns are correct. So the IFS formula has no issue.

I think the issue is with, when all the required data are available for the formula to work. The formula needs [P.Id] which happens to be the data required to be selected before the entry for [Qty]. Just by selecting the P.Id from an ENUM list without Sync to the Google sheet backend, the cell for P.Id in the Google sheet is still blank or unknown. Without this info available, it is not possible for the SUM(SELECT…) to work.

Where as if I just save an arbitrary value first and then go back to edit [Qty]. Now all the data in the ROW are already filled, there are no unknown and this is when the formula will be able to evaluate the [Qty] and point out what I key in are invalid. Obviously, this 2 steps method is not ideal in actual deployment, as I need to capture the error at the first instance.

[Aleksi_Alkio]

It’s always a little bit difficult to understand the whole picture without seeing the appstructure. Sorry about that.

[Ct_Chung]

@Aleksi_Alkio I understand, by the way, you have been amazing with your support.

I just thought of another issue that I need some help from you.

I have some text entry in a table basically for ref purpose. Eg

Name

|

Title A

|

Manager B

|

Admin

There are a few area in the App that I make comparison with the Name (A, or B). e.g. [Name] = “A”

Is there a way to make reference to the Cell that contains A or B using some expression? This is so that if in future A or B is changed, I don’t have to search high and low in the App to replace them. Thanks again.

[Aleksi_Alkio]

Well… you could use ref field for that purpose but then the value in your sheet would not be A or B. So… it depends again what kind of App/Sheet structure you have.

[Ct_Chung]

@Aleksi_Alkio Let me further explain. The content of table is use for reference.

So, for those entry that making use of ref as entry is not an issue. It is just that under certain condition I need to specifically compare with the content directly, similar to a filter and I use that in App expression in various section of the App.

So I have app expression that I check a value against “A”, if I decided to change A to A1, I have to go to all the app expression to update from “A” to “A1”. Is there a syntax that I can use which is equivalent to Sheetname!R1C1 to point to the content?

[Aleksi_Alkio]

The reason is probably the sheet formula (vlookup) because the calculation will happen when you save the record. Any chance to use LOOKUP expression as an app formula?

[Aleksi_Alkio]

If the key column is different than Name column, you can evaluate the key column only. For example… If you key column has an unique value like AgFr5dRtw you can evaluate it like [KeyColumn]=“AgFr5dRtw” and then it doesn’t matter what the real value in column Name is. Could this work for you?

[Ct_Chung]

@Aleksi_Alkio Let me explain further. During the initial development of the App I am using fake name or may be using variable text string that needs to be changed later. Those string are placed in a table.

I have these string hard coded in various part of the App. I am looking for a way to make reference to this table entries at specific column and row. So that I just need to change the string in the table and no longer need to modify all the hard coded string, Not sure my explanation is clearer now.

[Aleksi_Alkio]

Ref field would be suitable for that purpose. Of course you can use LOOKUP for that purpose as well.

[Ct_Chung]

@Aleksi_Alkio Hi Aleksi, I remember the reason why I use vlookup which is a Google sheet formula is because the LOOKUP does not work similarly to the Google sheet formula. Does it has any known limitation?

The other thing that I thought of is that since the evaluation on this entry also depends on other parameters entered (P.ID) on the 2nd table besides the [Qty]. Thus, without saving these parameters, they

are not physically save or present on the Google sheet. Thus, the evaluation will ultimately fail. Does this make sense?

Is there some write up that I can read about when Appsheet update the data to the worksheet? I am currently facing some challenges on not knowing when and how the data will be updated, thus, there are information not being update as I wish. One example is stock taken out from the order sheets, but the main stock which is in another table still showing the old info as some intermediate (ref) table/worksheets are not updated. And a lot of time, operational wise, it make no sense to go to those table and effect a save action.

Thanks.

[Aleksi_Alkio]

First… would you please copy/paste the VLOOKUP formula so we can understand what kind it is, thanks.

[Ct_Chung]

@Aleksi_Alkio this is what the formula is recognized in Appsheet

VLOOKUP(RC[-6],T.Ledger!R2C1:R400C7,2,FALSE)

Very standard VLOOKUP function to match the condition and return the content from specific column. The column that I am fetching the data has either an ENUM or REF classification. I remember that for some reason, no value is returned however VLOOKUP works

Thanks.

[Aleksi_Alkio]

Would you please show us the VLOOKUP from the sheet, not what it is in the app, thanks

[Ct_Chung]

@Aleksi_Alkio sure,

=VLOOKUP(A2,T.Ledger!$A$2:$G$400,2,FALSE)

[Aleksi_Alkio]

Where do you need that IFS statement?

[Ct_Chung]

@Aleksi_Alkio in the Valid_IF column definition of table Order details (2nd table) after the T.Ledger table. T.Ledger table is the first entry and Order details is the second entry where more details of the order are entered…