(Re-posting because my last post dissapeared after updating it)
Hello,
I have a Canvas app with form which needs to pull a range of records from an Embedded Excel file using either LookUp or Filter. This range of records is pulled using a number value from a text field in the form.
For example, if I have a value of -0.8 (representing an Average Temperature) that is entered into a Text field called DataCardValueNvAvgTemp, I want to pull a record from the Excel file that is +1.0 from that value (-0.8 + 1.0 = 0.2).
Note that the column I am querying is set to a Number data type in excel, and I have confirmed the value of 0.2 exists in the file.
I am attempting to do this query dynamically with the following formula, which returns no records:
//This function returns no records
Filter(
NetVcfat510, //This is the excel file
'Average Temperature' = Value(DataCardValueNvAvgTemp.Text) + 1.0 //DataCardValueNvAvgTemp value is -0.8
)
However, if I hard-code the value of 0.2, the query succeeds
Filter(
NetVcfat510,
'Average Temperature' = 0.2
)
What is going on here? Why does the first formula fail while the second succeeds? Am I missing something here?
I have confirmed with a Label that 'Value(DataCardValueNvAvgTemp.Text) + 1.0' is 0.2, exactly the same as my hard-coded value...
EDIT:
I did some more testing on this using hard-coded numbers, it appears it may be related to how Numbers are stored in the back end:
//This returns no results.
Filter(
NetVcfat510,
'Average Temperature' = (-0.80) + 1.0 //-0.8 + 1 = 0.2
)
//This returns the expected result
Filter(
NetVcfat510,
'Average Temperature' = 0.2 //-0.8 + 1 = 0.2
)
2nd Edit:
More testing has revealed that it is indeed an error with how numbers are stored. If I use the Round function to round to the nearest tenth of a decimal, the formula succeeds:
//This returns the expected result
Filter(
NetVcfat510,
'Average Temperature' = Round((-0.80) + 1.0, 1) //This works
)
I'm not sure if this is expected behaviour but this is definitely not intuitive and will certainly cause non-coders a lot of headache trying to debug such inconsistencies...
