+Praveen Seshadri [AppSheet] +Sarah Gould [Ap...

[Levent_KULACOGL]

@praveen @Sarah_Gould_AppSheet

In one of my client’s app, I have 2 tables: REGISTRATION and COUNTRIES respectively. You can see the COUNTRIES table in the attached image. I have created a Virtual Column in this table, with a simple CONCATENATE expression: =CONCATENATE([CODE]," - ",[COUNTRY])

In my [NATIONALITY] column in REGISTRATION table, I call this list with a simple Valid_if expression:

COUNTRIES[VC_Name]

However, as you can see from the 2nd attached image, the content of the dropdown is way different then the content in the table. I have tried various forms of expressions like:

SELECT(COUNTRIES[VC_Name],TRUE) TOP(SELECT(COUNTRIES[VC_Name],TRUE),233) SELECT(COUNTRIES[VC_Name],ISNOTBLANK([COUNTRY])) _SELECT(COUNTRIES[VC_Name],AND(ISNOTBLANK([COUNTRY]),ISNOTBLANK([CODE]))) etc.

but no any one seems working as it’s expected. Could this be a bug on your side, or am I doing something wrong here?

Thanks for your consideration and time to reply.

[tony1]

@Levent_KULACOGLU I kind of got lost following this thread, but here are some AppSheet facts:

• There is no guaranteed order of values returned by a SELECT formula or a TableName[ColumnName] formula

• If you provide an explicit list of EnumValues, the order will be respected

• You can use an ORDERBY formula in the valid_if of a Ref column to control the order. See this example app: appsheet.com - Ordered Dropdown - How to order/sort the values in dropdowns

Hope this helps Ordered Dropdown - How to order/sort the values in dropdowns appsheet.com

[Steven_Coile]

The image of the spreadsheet does not appear to show the VC_Name column your expressions reference. Would that account for the discrepancy?

[Levent_KULACOGL]

@Steven_Coile VC_Name corresponds to the name of my Virtual Column in the column structure schema. It’s not a physical column.

[Steven_Coile]

@Levent_KULACOGLU I see nothing wrong with the app list, and you’ve done nothing to help me understand your concern. The app appears to be doing what you’ve asked it to.

[Levent_KULACOGL]

@Steven_Coile provided you are not using a REF column and constructing a list explicitly with TOP(ORDERBY…)) expression, your dropdown values shall reflect the same order you have specified in your selection and therefore in your sheet. I’m not using a REF column and I’m not using a TOP(ORDERBY(…)) expression either. What I am using is a simple CONCATENATE expression, which concatenates the row values in [CODE] column with the values in [COUNTRY] column as I have already and “explicitly” stated in my post.

Therefore I expect the order of the dropdown values should appear as per the exact order in the sheet, where it should be starting with TR - Turkey, not with AF - Afghanistan. So may I humbly ask you, do you really think, see and evaluate that the app is doing exactly what I’m requesting it to do??

Provided you “explicitly” reply “YES”, then it seems I have a very big problem with either expressing myself or my problem or both.

[Levent_KULACOGL]

@Steven_Coile I’m eager if you have noticed the missing rows 7, 10, 11, 15, 16, 18, 19, 20, 22, 27, 28, 29, 31, 32… in the dropdown list values?? Could you please be a bit more “explicit” with your statement: " The app appears to be doing what you’ve asked it to do "?

[Steven_Coile]

Me: “so your concern is that the list in the app is not in the order of the entries in the table?”

You: “that’s not a concern.”

I’m done.

[Levent_KULACOGL]

Me: " that’s not a concern. That’s how it supposed to be and it’s not, as you can clearly see. "

If you cannot read or understand what you have read, responding is a waste of time for you and replying back is a waste of time for me, where both does not solve my problem. Thanks for being done.

[Lynn]

Hi @Levent_KULACOGLU

do you see any of the are missing rows iif you scroll down to the bottom of the dropdown list?.

Ii had a similar problem back in February

[Levent_KULACOGL]

Yes @Lynn I have them at the bottom but in an unexpected order. Have you been able to solve your problem?

[Lynn]

Hi @Levent_KULACOGLU

I think I posted twice about it but never had any useful replies and didnt solve the problem. I am not using the app at the moment and wont need it again until next year but it would be good to get it solved before then. I think my erratic lists were caused by people opening the form and then cancelling out. The lists would get back in order again after the next sync.

plus.google.com - Hi sometimes my enum lists are showing up out of order. The lists in my sheet… Hi sometimes my enum lists are showing up out of order. The lists in my sheet… plus.google.com

[Levent_KULACOGL]

@Lynn thnx for your reply. However my list is not going back in order after a sync or even browser refresh. Thanks to @Aleksi_Alkio, he proposed using another character other than " - " in my concatenation for a trial. I’ve switched to using " > " and it seems that the problem is solved up-to now. Haven’t experienced an error yet. Though the expression’s own nature is not suitable for that, I believe " - " character is causing an effect something like similar to {list} - {list} operation.

[Steven_Coile]

@Levent_KULACOGLU so you’re saying the image from the app doesn’t correspond to the values generated by you VC_Name column?

[Levent_KULACOGL]

@tony can you please tell me this: 1.) Provided you have a list of i.e. 500 items in an enumlist column, what would be the easiest way to get the items to the list other than a simple SELECT expression? I hope you won’t be expecting us to enter those 500 items one by one to the column structure 2.) Your statement " there is no guaranteed order of values returned by a SELECT formula or a TableName[ColumnName] formula " is a bit ridiculous I believe as it should by-default respect the row order in the sheet than. 3.) I’m aware of the fact that ORDERBY expression is valid with REF column types only. But can you please remember that we might need that order out of a ref also?

I believe there is nothing to be lost here. Skip the useless and waste of time replies from Mr. Coile. If you stick to the essence of my original post, you will realize the problem, which I believe is a bug on your side. @Lynn have the same unsolved problem still, btw.

[tony1]

@Levent_KULACOGLU Internally, the app may be rearranging the order of the rows for various purposes. I agree that we should make this problem solvable without resorting to a Ref/ORDERBY solution. But unfortunately, that’s the only way as it stands. Adding @praveen so he can see this.

It looks like you have a few commonly accessed values at the top of your sheet, is that correct? In that case, you could make two separate fields in your form. The first field would allow the user to select from a few of the most common options. The second field would be a dropdown with all the rest. Just a thought.

[Steven_Coile]

@Levent_KULACOGLU my comments reflected the accurate understanding that the list is unsorted unless explicitly sorted. Your failure, which you repeated several times, is your invalid assumption the list would be returned in a predictable order. In fact, even the documentation contradicts your assumption.

[Levent_KULACOGL]

@tony

The table that I read those enum values is a READ-ONLY table, so the app cannot re-arrange any order by itself. You may have a right when reading values from a normal table where if you delete any row data, rather than deleting the whole row from the sheet, AppSheet cleans the whole row data and when you make a new record, it’s either recorded to the first empty row in the table, if not it’s appended. There we might need that ORDERBY out of the ref.

Thnx for your proposition, but at this level of development it’s not the solution and not acceptable either.

[Steven_Coile]

@Levent_KULACOGLU “Read-only” applies to the data as stored. Data in-memory used during computation is routinely reordered and munged to meet the needs of the application.

It’s perfectly reasonable and common practice to reorder lists in memory for the convenience of the application.

[Levent_KULACOGL]

@Steven_Coile I thought you were done and I’ve much appreciated it. So may be you prefer to take your own failures and stand out of my path behaving me like a rookie. As a side note to you; SELECT expression is basic and simple SQL command, where it’s expected to obey table’s own row order unless “explicitly” stated otherwise in the query structure. And I have not read any piece of AppSheet documentation contradicting with my " invalid assumption ". Even the existence of such documentation does not change the strong fact that this evaluation of AppSheet is not correct in terms of database structuring, which we were discussing with @tony.

[tony1]

@Levent_KULACOGLU @Steven_Coile Let’s end this thread for now, no need for further responses.

[Levent_KULACOGL]

@Steven_Coile I have not stated that AppSheet is a SQL platform. I have stated that SELECT expression is a basic SQL command Read and understand first, before you reply me. I repeat, please stop behaving me like a rookie. From the documentation you refer to: " SELECT is a powerful way to construct a list from another table. It is a stylized SQL “select-from-where” query. It constructs a list of values from a specified column of a table. However, a filter can be applied to control which rows are included. The resulting list only includes the specified column value of the included rows. "

appsheethelp.zendesk.com - List expressions and aggregates List expressions and aggregates appsheethelp.zendesk.com

[Levent_KULACOGL]

Yes @Steven_Coile you’re right

[Steven_Coile]

@Levent_KULACOGLU what is the App formula for the VC_Name column?

[Levent_KULACOGL]

@Steven_Coile it’s simple CONCATENATE expression as I have mentioned in the post

[Steven_Coile]

@Levent_KULACOGLU I’m confused, then. In what way is the list in the app not what you expect? What are you expecting? The list in the app appears to be the values I would expect in VC_Name.

[Levent_KULACOGL]

@Steven_Coile please take a careful look at the order in the sheet and look at the order in the app dropdown. Are they looking the same??

[Steven_Coile]

@Levent_KULACOGLU so your concern is that the list in the app is not in the order of the entries in the table?

How about being more explicit about your concern?

[Levent_KULACOGL]

@Steven_Coile that’s not a concern. That’s how it supposed to be and it’s not as you can clearly see. How much do you expect me to be more explicit? I think my explanations are quite clear.