(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 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...
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 )
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...
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.
Have you solved your problem?
If you have solved your problem, please go head to click "Accept as Solution" to identify this thread has been solved.