I seem to be having a problem with certain co...

(Green Gorilla) #1

I seem to be having a problem with certain columns in my app not calculating properly (or at all) when previously they did…

The formula:

=( HOUR([Jin.Clock]-[Hout.Clock]) + MINUTE([Jin.Clock]-[Hout.Clock])/60 + SECOND([Jin.Clock]-[Hout.Clock])/3600 )

Works with the following data:

[Hout.Clock] is 14/08/2018 10:22:17


[Jin.Clock] is

14/08/2018 10:54:00

I get the result: -426177.37

One part of the error, according to the Verify process, is when the MINUTE([Jin.Clock]-[Hout.Clock])/60 part of the calculation divides by 60. There are 31 minutes, which are calculated, which when divided by 60 should show approx 0.5. It shows 0.

So there is some rounding going on in the formula, but not sure what…

Any ideas?

Reiterate: This works perfectly well yesterday, but not today…

(Green Gorilla) #2

How odd. That appears to work. We’ve used this formula in Apps for so long, and it’s never failed before…is something new?

(Suvrutt Gurjar) #3

Hi @Green_Gorilla, good to know it works for you. I think when we need to have results in fractional values , it works best with decimal division and decimal fields. I will request other senior community colleagues to guide on your query on it working before with integer values in division.

(Green Gorilla) #4

I spoke too soon. This is working in the Verify stage from the expression editor, but for some reason it is not calculating and populating the spreadsheet. So the app thinks it has a value, but that value is not being placed in the correct place on the spreadsheet. That is showing as blank, now.

It’s as if the App is not syncing the changes when I edit the record.

Also, the Sync is painfully slow, and this is not a big app… Have we any reason to suspect that syncing is at fault?

(Suvrutt Gurjar) #5

Hi @Green_Gorilla, I am sharing more details for your reference. I had tested it at my end. It works well.

Please note that in teh below image, the column TimeDiff

is a virtual column and calculates time difference as [Second Time] -[ First Time] ( Of course by using the detailed expression that we have shared before)

This virtual column TimeDiff gets calculated immediately.

The column TimeDiff2 is a real column and is set as=[TimeDiff}

This real column gets calculated and saved at teh back end only when the record is opened and saved.

I am getting the calculation behavior seamlessly.

(Green Gorilla) #6

My issue seems tro be to do with Hidden columns that are not in the slices needed…

Gradually getting to the bottom of it… 150 second sync times don’t help…

I can’t use Virtual Columns for this as the data needs to be output via a query in google Sheets to a PDF.

(Suvrutt Gurjar) #7

OK. Thank you for sharing the updates and experience at your end.

(Suvrutt Gurjar) #8

Hi @Green_Gorilla,May I request you to try the following expression

=( HOUR([Jin.Clock] - [Hout.Clock])

  • MINUTE([Jin.Clock] - [Hout.Clock])/60.00 + SECOND([Jin.Clock] - [Hout.Clock])/3600.00 )

Please have the field where this expression is entered as decimal type field.