(Re-posting because my last post dissapeared after updating it)
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 calledDataCardValueNvAvgTemp, 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 Numberdata 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
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...
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:
I think I found the answer to my problem. Firstly it looks like my issue was related to my data source: Common Data Service for Apps.
I wasn't expecting precision errors at one decimal place: I suspect it's because the floating point operation (-0.8 + 1.0 = 0.2 ) is false because of this floating point imprecision. I will see if I get this behaviour with using the decimal data type.