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

expressions
(Levent KULAÇOĞLU) #1

@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.

(Tony Fader) #2

@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) #3

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

(Levent KULAÇOĞLU) #4

@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) #5

@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 KULAÇOĞLU) #6

@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 KULAÇOĞLU) #7

@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) #8

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 KULAÇOĞLU) #9

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.

#10

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 KULAÇOĞLU) #11

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

#12

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 KULAÇOĞLU) #13

@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) #14

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

(Levent KULAÇOĞLU) #15

@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 :slight_smile: 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.

(Tony Fader) #16

@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) #17

@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 KULAÇOĞLU) #18

@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) #19

@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 KULAÇOĞLU) #20

@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.