I have 2 duration columns. On their own, the...

[Simon_Robinson]

I have 2 duration columns.

On their own, they work fine.

If I create a virtual column and do [columnA]-[ColumnB] that works

But I copy the 2 formulas into a virtual column as:

“Formula from ColumnA” - “Formula from ColumnB”

I get "…has inputs of an invalid type ‘Unknown’ "

Anyone any ideas?

Formula from ColumnA =SUM(Select([Related Process][Hours],[Status]=“Pending”))

Formula from ColumnB =CONCATENATE(FLOOR(( HOUR(SUM(Select([Related Process][Hours],[Status]=“Pending”))-IF(“17:00:00”<TimeNow(),“000:00:00”,“17:00:00”-TIMENOW())) +(MINUTE(SUM(Select([Related Process][Hours],[Status]=“Pending”))-IF(“17:00:00”<TimeNow(),“000:00:00”,“17:00:00”-TIMENOW()))/60) +(SECOND(SUM(Select([Related Process][Hours],[Status]=“Pending”))-IF(“17:00:00”<TimeNow(),“000:00:00”,“17:00:00”-TIMENOW()))/3600))/8) *8,":00:00")

[Simon_Robinson]

Managed to sort by removing the CONCATENATE since I presume that [Duration]+1 adds one hour.

But still odd and would like Appsheet to look at

@Aleksi_Alkio

[Steven_Coile]

Time handling is odd, yes.

[Steven_Coile]

Your expression reformatted for clarity:

SUM(

SELECT(

[Related Process][Hours],

([Status] = “Pending”)

) ) - CONCATENATE(

FLOOR(

(

HOUR(

SUM(

SELECT(

[Related Process][Hours],

([Status] = “Pending”)

)

)

• IF(

(“17:00:00” < TIMENOW()),

“000:00:00”,

(“17:00:00” - TIMENOW())

)

)

• (

MINUTE(

SUM(

SELECT(

[Related Process][Hours],

([Status] = “Pending”)

)

)

• IF(

(“17:00:00” < TIMENOW()),

“000:00:00”,

(“17:00:00” - TIMENOW())

)

)

/ 60

)

• (

SECOND(

SUM(

SELECT(

[Related Process][Hours],

([Status] = “Pending”)

)

)

• IF(

(“17:00:00” < TIMENOW()),

“000:00:00”,

(“17:00:00” - TIMENOW())

)

)

/ 3600

)

)

/ 8

)

• 8,

“:00:00” )

[Steven_Coile]

Your formula performs the SUM(SELECT(…)) four times, which is inefficient, potentially slow, and cumbersome to maintain. I recommend you move it to a virtual column so its computed only once:

=SUM(

SELECT(

[Related Process][Hours],

([Status] = “Pending”)

) )

Call it (e.g.) Pending Hours. Then your formula reduces to:

[Pending Hours] - CONCATENATE(

FLOOR(

(

HOUR(

[Pending Hours]

• IF(

(“17:00:00” < TIMENOW()),

“000:00:00”,

(“17:00:00” - TIMENOW())

)

)

• (

MINUTE(

[Pending Hours]

• IF(

(“17:00:00” < TIMENOW()),

“000:00:00”,

(“17:00:00” - TIMENOW())

)

)

/ 60

)

• (

SECOND(

[Pending Hours]

• IF(

(“17:00:00” < TIMENOW()),

“000:00:00”,

(“17:00:00” - TIMENOW())

)

)

/ 3600

)

)

/ 8

)

• 8,

“:00:00” )

[Steven_Coile]

You then adjust Pending Hours the same way three times. Moving that to Adjusted Pending Hours with:

=[Pending Hours]

• IF(

(“17:00:00” < TIMENOW()),

“000:00:00”,

(“17:00:00” - TIMENOW())

)

Your expression further reduces to:

[Pending Hours] - CONCATENATE(

FLOOR(

(

HOUR([Adjusted Pending Hours])

• (MINUTE([Adjusted Pending Hours]) / 60)

• (SECOND([Adjusted Pending Hours]) / 3600)

)

/ 8

)

• 8,

“:00:00” )

[Simon_Robinson]

I’m trying to reduce the number of VC’s because I had it working fine with 6 of them.

But I was getting different answers in the Form and Detail views

In one formula the 2 views were getting TRUE from 2 different sections of an IF equation.

I hypothesized that Appsheet was calculating the VCs in an odd order.

Or there was a race condition somewhere.

So I tried to condense them to eliminate this, but hit upon the maximum equation size.

So I’m redoing it from scratch.

Am I correct in thinking you can’t multiply a duration?

[Steven_Coile]

You are correct: you cannot multiply a duration.

[Steven_Coile]

It’s more likely the bug is in AppSheet in the way form and detail view values are computed than with the number of VCs, I’d think.

[Steven_Coile]

Note, too, that Date, Time, and Duration values appear to only be automatically recognized if provided as literals in an expression. Your attempt to construct a Duration using CONCATENATE() will not be recognized as such, and instead be seen simply as a Text value. Then you’re attempting to subtract Text from a numeric value.

[Simon_Robinson]

Should I fudge that by doing something like …+“000:00:00” ?