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")
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
Time handling is odd, yes.
Your expression reformatted for clarity:
SUM(
SELECT(
[Related Process][Hours],
([Status] = โPendingโ)
) ) - CONCATENATE(
FLOOR(
(
HOUR(
SUM(
SELECT(
[Related Process][Hours],
([Status] = โPendingโ)
)
)
(โ17:00:00โ < TIMENOW()),
โ000:00:00โ,
(โ17:00:00โ - TIMENOW())
)
)
MINUTE(
SUM(
SELECT(
[Related Process][Hours],
([Status] = โPendingโ)
)
)
(โ17:00:00โ < TIMENOW()),
โ000:00:00โ,
(โ17:00:00โ - TIMENOW())
)
)
/ 60
)
SECOND(
SUM(
SELECT(
[Related Process][Hours],
([Status] = โPendingโ)
)
)
(โ17:00:00โ < TIMENOW()),
โ000:00:00โ,
(โ17:00:00โ - TIMENOW())
)
)
/ 3600
)
)
/ 8
)
โ:00:00โ )
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]
(โ17:00:00โ < TIMENOW()),
โ000:00:00โ,
(โ17:00:00โ - TIMENOW())
)
)
MINUTE(
[Pending Hours]
(โ17:00:00โ < TIMENOW()),
โ000:00:00โ,
(โ17:00:00โ - TIMENOW())
)
)
/ 60
)
SECOND(
[Pending Hours]
(โ17:00:00โ < TIMENOW()),
โ000:00:00โ,
(โ17:00:00โ - TIMENOW())
)
)
/ 3600
)
)
/ 8
)
โ:00:00โ )
You then adjust Pending Hours the same way three times. Moving that to Adjusted Pending Hours with:
=[Pending Hours]
(โ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
)
โ:00:00โ )
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?
You are correct: you cannot multiply a duration.
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.
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.
Should I fudge that by doing something like โฆ+โ000:00:00โ ?
User | Count |
---|---|
35 | |
35 | |
28 | |
23 | |
18 |