Strange behavior in SELECT () expression

I am locked in something simple:
With this expression:
SELECT (Repairs [Rep Id],[Withdrawn Date]>DATETIME ([Last payment]. [Date]))

  1. If I test the expression with the “Test” button, it correctly returns all Rep Id’s.
  2. In the Form view where I want to show it, you don’t see anything.
  3. If I replace the expression DATETIME ([Last payment]. [Date]) with a written date, it shows the list perfectly.
  4. [Last payment] is a virtual column with the following expression
    MAXROW (“Payments”, “_ RowNumber”), of type Ref, which correctly returns the date of the last payment.

What am I doing wrong?

Additionally, I make another query, how do I locate the inline view of a SELECT expression to modify which columns are displayed?

First Question

If Both [Withdrawn Date] and [Last payment].[Date] are of Date type, there is no need to wrap the last expression with Datetime

Try either of these 2 formulas

SELECT (Repairs[Rep Id], [Withdrawn Date] > [Last payment].[Date] )

SELECT (Repairs[Rep Id],[Withdrawn Date] > Date([Last payment].[Date]) )

Second Question

In the app editor, open your app on the right side and go to the detail view of this specific table. Scroll down till you see your list, you will see something like View. Click and you will see the view name on the bottom. Now, Click the view name and you can modify your inline view as you desire.

1.In the beginning I did it like this, since it didn’t work add DATETIME () to see if it was a data type problem. I also tried DATE () (although I really need the time to be calculated as well) and the only thing that worked is to put the date by hand.
I also tried Concatenating Month (), Day () and Year () in case there was any problem with how to present the date, since here we use DD / MM / YYYY, but it doesn’t work either.

  1. Most of the views put a link under the mobile view, but in this case it does not show it, but I suppose I will have to create a custom view and assign it, when the above works I will see well how I solve this.

If you need to compare datetime values, then change both [Withdrawn Date] and [Date] (in payments table) to datetime types

For the view, does it show something like fasttable or completely blank?. If completely blank, refresh the page. If it shows something like fasttable, it means you need to create your inline view.

[Withdrawn Date] is Change Time Stamp.
[Date] is DateTime,

I assume that if they were of different types I would get an error, or I would not return the list, as shown in the image.
The problem is that it does not show the inline view inside the form, while if I write a date manually, it shows it correctly.

I think you need to remove the space in [Last payment]. [Date]. This will correct the syntax. Then you should be able to remove the DATETIME() function.

Or is that just a typo?

EDIT: Just looked at the test screen shot. It looks like no space there.

I did see where you mentioned that your date formats are DD/MM/YYYY. I do vaguely recall a post where Local Date format was not being applied - it may have been the Change Time Stamp. I’ll try to find it.

I don’t remember the resolution but I think you were on the right track with extracting the Day, Month, Year, rebuilding the date and then comparing. However, you will need to do it to both variables in the same way.

Using CONCATENATE creates a string value and that WILL not compare properly with a Date value.

EDIT: I searched but didn’t find the post I was thinking of. In any event, it should be easy to see if the date formats are not the same.

2 Likes

From Date and Time Expressions:

Note that when dates are used as constant values within an expression, they must be represented in the MM/DD/YYYY format. This doesn’t mean your spreadsheet data must use dates in this format: the date representation in your spreadsheets is determined by the locale/language of the spreadsheet.

Per Apps Using Dates, Times, and DateTimes:

Date , Time , and DateTime formats vary widely across locales. It is important that your worksheet specifies the correct locale and that all your worksheet data cells are formatted correctly, as described here.

Make sure the spreadsheet column format is set to Automatic or Date time.

1 Like

This link is very useful, and as I said above, in this case, the link is kept in view of the form and does not show the link to the inline view.

As I said above, I also tried Concatenating Month (), Day () and Year () in case there was a problem with how to present the date, since here we use DD / MM / YYYY, but it doesn’t work either.
Also in the Test it shows that it is filtering the data well, that is the strangest thing.

Believe it or not, if you construct a Date or DateTime value with CONCATENATE(), you must wrap it in DATE() or DATETIME() before using it as a temporal type.

I even created a virtual column of type DateTime with concatenation and it doesn’t work either!
I’m baffled, I don’t know what to do

Your problem may be the use of ChangeTimestamp column. Unless you’ve set conditions, that value is updated every time the row is modified, so it will always be greater than the last payment date (unless the last payment date is in the future).

I insist that it is not a problem of the expression, as in the other case of Context, in my opinion it is a visualization bug, but it should give an error in the editor, and it would not generate the lists of IDs correctly when testing.
Like “Form” = CONTEXT (“ViewType”) it also returns the correct result but the Form is not seen.

@Steve
I changed the column type Change TimeStamp to DateTime, and it doesn’t work either!
That value changes before this expression is used, therefore the data is in the spreadsheet in DATE TIME format, and is correctly found by the expression LAST PAYMENT.

These are the formulas as they are written without translating:
Ultima Rendición: (type REF)
MAXROW(“Rendición”,"_RowNumber")

List:
SELECT(Reparaciones[Rep Id],[Retirado Fecha]>[Ultima Rendición].[Fecha])

Account ID: 461773
App: TalkNet Adm
Table: Rendición
Columns: Ultima Rendición
Entregados desde la última rendición

You should probably engage support@appsheet.com directly on this.

Something about this scenario (dereference in a select filter where the Ref column is in the outer scope) confuses the expression binding and causes it to behave as equivalent to [_THISROW].[Fecha] rather than ([_THISROW].)[Ultima Rendicion].[Fecha]. We’re looking for a solution.

1 Like

So this turns out to be not so simple to fix. The problem here is the reliance on the implicit [_THISROW], it amounts to a dereference chain which can’t be expressed currently within our internal expression binding model. I think this expression shouldn’t even be allowed by the expression validation, it only seems to work because it happens to be improperly handled in the same manner as if you had written [_THISROW].[Fecha].

As a workaround, you could do the dereference [Ultima Rendicion].[Fecha] in a virtual column, and then use that in your Select like [_THISROW].[Ultima Rendicion Fecha]

1 Like

Thanks @Adam
It worked perfectly!

In my humble opinion, I think that the expression [_THISROW] should be removed, since it is very confusing, and it is not easy to understand where it should be used and where it should not be, and you never quite understand what it means. It would be much better to be able to refer to [Table]. [Column] or Table [Column].
Something similar happens when you want to refer to the data loaded in the application form or to the information previously saved in the spreadsheet for that cell. There should be an expression of type [_Form]. [Column] or [_Sheet]. [Column]

Always keep in mind that AppSheet is a No Code Platform, and from the moment you have to learn a code of “expressions” and a “syntax”, what we are writing is still code.

Greetings and thank you very much.