If I have a table with a column formatted as date and use the following formula:
[Table 1].Lookup(Date, thisRow.RowId()) I get an error: “Cannot convert value to the specified format”. However if the column is formatted as a number then I get the expected behaviour.
Just to point out that the columns are formatted correctly to accept the date. Here is what I get. The error is in Column 5. Column 2 has the same formula and behaves properly:
Anyone encountered this before?
This is my understanding:
[Column 1] = number data type
[Column 2] = number data type
[Column 1] & [Column 2] are matching data types
[Date] = date data type
[Column 5] = data data type
[Date] & [Column 5] are also matching data types
However, RowID() returns a number data type, which does not match the values in the [Date] column you are searching, which is a date data type.
When you place your cursor on a function in a formula, the info modal shows you what data type it returns. (ignore everything in the image below except the formula info modal)
Thank you very much. This makes sense now. It was my confusion about what the lookup function actually did. What I was trying to do was to reference the value in the column not go searching for it. The error message totally made sense.
The proper way that I should have done it was with something like this:
thisTable.Date.Nth(RowId(thisRow))