Value for Virtual Column showing up in Test but not when viewing the Table Data

[andresjgc]

Hi,

I have a set of virtual columns that perform a certain set of operations. For the virtual column that I'm interested in, I am able to test the formula and get the expected value through the Test button but when I use this value somewhere else or simply view it in the Data Table, the value that I'm expecting is not showing up even though the value was showing up in the test

The image above is the value that I get when using the test button which is expected (second row)

In the image below, the last column in the second row should display 0, 2 but instead is showing nothing

App has been saved and values are matching the data type as well

[SkrOYC]

Are you using UserSettings?

[andresjgc]

No. I'm not sure if for some reason there's like a timeout prevent the value to be calculated in the data table view which is not happening in the testing but still the calculation is not that heavy

[Joseph_Seddik]

Can you add more rows including one with the same column values and check the behavior; this would help in troubleshooting and locating the issue.

[andresjgc]

When added new rows, the value was then calculated (still second row but now we can see the value has been populated for it and the new added rows)

[andresjgc]

I have now deleted all the elements and was able to recreate the issue, the 2nd row won't get the values to display

UPDATE: When a new row is added, the value for the row gets displayed

[Joseph_Seddik]

I’d be inclined to say it is a display bug. To test this theory, add a new EnumList normal column in your sheet with an app formula that just copies the value of your virtual column like this: 

[name of virtual column]

Then do a sync and see if the value is written in the sheet.

[andresjgc]

That will still show the column as with no value

[Joseph_Seddik]

Will show? Did you try it already?

[andresjgc]

The new column is copy_column which is a EnumList with formula value of the column I'm copying (last column in second photo)

[Joseph_Seddik]

Thank you. Why is it empty also for the first row? Would you please post screenshots for your table column configurations and formulas used? Thanks. 

[andresjgc]

Not sure why the first column did't get written

copy_column

consecutive_with_day_in_between_list

[Joseph_Seddik]

Please make sure the base type of your copy column is the same as “start/end mp for calculation”. This column seems to have the type Number, so the “copy column” should have same Number base type, also remove the “initial” value which is not a number. 

[andresjgc]

All set, changed the DataType to match that of start/end mp which is Decimal. Also removed the "initial" but still nothing is written into that copy_column

[Joseph_Seddik]

Thanks. A sheet row won’t be updated unless there is a change in one column of of the row; this will trigger the app formulas in that row to calculate for all columns. 

Please make a change in one column of each row, through the app, not in the sheet, save and sync, then see whether the sheet gets updated. 

[andresjgc]

That seemed to update the copy_column but not the OG one

UPDATE: And the value is not available either to be read from other virtual columns

[Joseph_Seddik]

So as I guessed, this is just a non-impacting display bug in View Data, and you can file a bug report about it if you wish.

However, as long as your app functionality is concerned, your virtual column does have a value and you can use it in further calculations as you need. 

[andresjgc]

Well, the virtual column does not have the value  available to read if called within the formula of another virtual column, which is more than a display bug in View Data. The value will simply be blank if used on other virtual columns. If instead pf using a EnumList and replace it with a virtual column of type List and copy the value of the OG column, it won't show either

[andresjgc]

But thank you for your time and helping me out with this. It was and still is confusing on why the value is not available

[Joseph_Seddik]

Welcome.

---

@andresjgc wrote:

the virtual column does not have the value  available to read if called within the formula of another virtual column

---

Did you test this? I don't think so. Should the VC not have a value, the "copy column" wouldn't have any value. Let's take it further, go to another table, create a Ref column "refID" to this table, also a virtual column and have it's formula as: 

[refID].[OG] + LIST(88.99)

[andresjgc]

Image below is with a new virtual column that simply reads the value from the original column and adds LIST(89.99) (see column two how there's no value besides 89.99)

Now let me try your suggestion creating a second table with the reference

[andresjgc]

Here's a photo of the table to the reference of the table mentioned above. The second row references the second row of above while the first and third row references the first row from above

[Joseph_Seddik]

Thank you. One thing occurred to my mind, perhaps I should've thought about it earlier, can you see what values are displayed in an App View? 

Can you please open a table view of your table, and detail view of your row and see what is the behaviour? Thanks. 

[andresjgc]

From trable view

And detail view

Same behavior 

[Joseph_Seddik]

Thank you. Well perhaps others here can add other ideas or you might want to contact support. 

[andresjgc]

Appreciate your time and effort. Will try to open a ticket and contact support for it. Thanks

[SkrOYC]

You need it to be a list? Could you change that to Text for example and even make a better separator?

I'm not completely sure (and I don't know how it could change things) but List column type is for inline views afaik

[andresjgc]

Yes, Ideally it should be a List. Converting to text and then converting back to a list could be an option but requires more effort. I'll try that when I have a chance and let you know the outcome. If that doesn't work, I'll contact AppSheet support and see what the problem is about. For now, I can get over the problem when new rows are added to the list

[SkrOYC]

Have you tried using EnumList instead of List?

Again, I though that List is just for inline views

[andresjgc]

So the purpose of this column is just to hold some data which will then be used by another virtual column to perform so action based on this value

[SkrOYC]

I get it, but try changing the column type to EnumList instead of List. As far as I know (which is not that much) List expects a list of keys, at least that's the only thing I use it for (to create inline views on forms/detail views)

BTW: Try not to over use VC, if you need one value just to be used on another column, try just making the end VC with the formula inside of it, unless you need to see that intermediate data on the UX

[andresjgc]

Thanks for your suggestion. That disn't work either. Yoou can see my comment below to get a better understanding of what is going on

[Steve]

As others have noted, don't use type List here; use EnumList instead. Generally speaking, the app creator should not manually configure a column to be type List.

[andresjgc]

Why shouldn't the app creator configure a column to be a List? EnumList doesn't work with what I'm trying to do. Maybe I should explain a bit further what I'm trying to achieve.

Given two columns, remove all values that appear twice, including the original value. So if we have in one column with 4 rows (Column A) [1, 3, 5, 8] and in the other column (Column B) [[3, 5, 7, 9], I want to get a list with the values [1, 7, 8, 9]. Then using a different column of type Text I'm doing some  processing so it display as "1- 7 | 8 - 9". The first operation needs to be a List and no user input is needed and has to be dynamically calculated based on the values of Column A and Column B.

All this works perfectly, the only issue is when a second entry is added to the table, but it doesn't happen again if more entries are added to it

[SkrOYC]

I think that you are overthinking it. EnumList <> UserInput. It's just a way to have data on a field that is understandable to the AppSheet platform and can be used to get user input, but AppSheet is flexible enough to prevent that, or even hide it.

I have VC of type Enum so that the info inside of it can be searched using checkboxes on the filter area instead of require typing. Enum on a VC? Well, yes. It doesn't make sense if you think of it as a User Input kind of field.

I really don't know why this is not working in your case, I keep being intrigate. But honestly I'm not sure if you already tried by changing that "List" to "EnumList" basetype Decimal

[andresjgc]

Yes, I guess I get a bit confused between the use cases of a EnumList vs List in a VC, but your example is a good one.

I tried making that change and as you can see in the image below, the test result is giving me the expected value but the column on the far right which should hold the value 0.00, 2.00 is not showing in the second column

[andresjgc]

For some reason it won't let me upload a picture not sure why, but yeah, changing the column to EnumList didn't work either. Thanks anyways