LinkedIn
Twitter
Hi All,
I have an expression setting the value of a percent column. When I test the expression it work perfectly. Currently it should be 100% done with a value of 1.00 and that is what the test result shows. Now when I view this data either in the form or in the database after it is saved for some reason it is 20%. It seems that my expression where I am using SELECT() functions work correctly during testing but are not working as expected outside of testing. Any idea why this would be? This is the first time I have had a test value that is different than the actual so I am not sure how to trouble shoot this one.
Database
Testing
Form View
AppScript must be buggy!?
I have an amount calculated based on a time duration (the Hourly Rate used here is $60, I'm also rounding to the nearest 15 minutes)...
IF( Round(Totalhours([Duration])*60+14)/15*15/60.0 > 0,
(Round(Totalhours([Duration])*60+14)/15*15/60.0)*DECIMAL([Client].[Hourly Rate]),
0
It works perfectly when testing:
However, rendered in the app it looks like this!? Huh?!
This first Due of $399 should be $390 - $60 X 6.5 hours. I get the whole precision problem, but this is wild!
OK, so I thought maybe it had to do with using a spreadsheet as a source. I built the same with AppSheet DB and here's a side-by-side comparison after updating the record with the formula shown in my previous post.
I made this a TEXT field so I could see various values - factor and due - but results were the same if it was a PRICE field...
The above is the Test run on the expression/formula running from the AS DB version and what is showing in the preview pain (to the right). This result (same expressions used) had different values then what I am getting in the gsheet source app (see my 6.5 hours example above)!!
See below - as compared to my previous post...
What am I missing?! ๐ค
Hi @WillowMobileSys thanks for the questions! See my post below, I'll consider this the solution for now.
I think I was over complicating matters, but yes, there certainly does seem like rounding gets funny when it comes to Time values and converting them!!
UPDATE:
In case you are interested, I've had a bit more time to respond. Your questions are in Blue \ @WillowMobileSys :
I have a Client and Tasks table used for my app.
(is there an easy way to capture table definitions for posting?)
Tasks is now changed to what I posted below. So likely not that helpful.
- What does the raw data look like, for both Rate and Hours, in the datasource? (maybe AppSheet values are being rounded for display?)
Rate is defined in a Client table as:
Hours was a TEXT field (changed now) where I tried to assemble (via an expression) the "HH:MM" text Using another DURATION field in my Tasks Table and from [Stop] - [Start] TIME values entered by the user. These are also rounded to the nearest higher 15 minute mark. - How is your Duration of "6:30" being derived?
Simply in a DURATION field from "[Stop] - [Start]" times entered - a "Formula" in the Tasks table. - How are you converting the Duration of "6:30" to the decimal of "6.5"?
An expression (or Formula - are they synonymous?) as this:
Round(Totalhours([Duration])*60+14) / 15 * 15 / 60.0 - How are you building your test strings?
A expression/formula as follows (this is now changed though, to an expression using MOD() function):
IF(Number(Index(Split([Duration],":"),2))+14.0 > 59,
Text(Number(Index(Split([Duration],":"),1))+1),
Text(Number(Index(Split([Duration],":"),1)))
)
&IFS(
Number(Index(Split([Duration],":"),2))+14.0 > 59, ":00",
Number(Index(Split([Duration],":"),2))+14.0 > 44, ":45",
Number(Index(Split([Duration],":"),2))+14.0 > 29, ":30",
Number(Index(Split([Duration],":"),2))+14.0 > 14, ":15",
Number(Index(Split([Duration],":"),2))+14.0 < 15, ":00"
)
I am pretty sure that AppSheet has issues with TIME value conversions in general. This was another one I posted and must of the info of what I was doing can be gleamed from the test result capture - formula, headings show data types, etc. The test result is good - rendering bad.
I've since fixed this as well - see this post . None the less, I don't know why this didn't work - even if it was overkill. ๐
