I watched some video review in youtube about app for employee presence. Then tried to adapt that app for my office but still confused about the app mechanism. The app maybe simple but I still canโt figure it out. Here the screenshot for better explanation
In the first image. This show the table view of Presence In for employee
Then the second image. This show the table view of Presence Out and integrated with the Presence In data. Certainly this view is using slice. In this view of Presence Out, employee click the data table to entry the out of office time
The third image. This is the Presence Out Form that I confused to make. In the form employee can see the previous time for presence in. And then automatically the time for presence out set. I try to use for both presence time in and out the TIMENOW() or NOW() to set automatically for the time. But when I click this Presence Out Form, the time for presence in changed with present time. So the time for presence in and out are same time now.
So how can this app can lock the previous time for presence in so that I can calculate the duration of work hour. Please share and give me your suggestion. Thank you
Solved! Go to Solution.
Thank you for Steve Coile and Heru Herlambang for guidance. After many configuration, it works.
For Jam Masuk , just like formula mentioned by Steve Coile.
And for Jam Keluar, the formula became like this:
Valif If
([_THIS] > [Jam Masuk])
Required?
ON
App Formula
IFS(ISNOTBLANK([Jam Masuk]), TIMENOW())
Editable?
ON
Reset on Edit?
([_THIS] = [Jam Masuk])
Move your expression of NOW() or TIMENOW() to Initial Value.
Just like my explanation that didnโt work. Because when formula put in initial value and not editable the column didnโt show in the form. And I want the time to automatically set and canโt be edit in the form. Thanks
Try to follow through the guidance from Steve above and let us know.
Required?
(CONTEXT("View") = "In")
Initial value
TIMENOW()
Editable?
FALSE
Reset on edit?
AND(
(CONTEXT("View") = "In"),
ISBLANK([Jam Masuk]),
ISBLANK([Jam Keluar])
)
Valid If
([_THIS] >= [Jam Masuk])
Required?
(CONTEXT("View") = "Out")
Initial value
IFS(
AND(
(CONTEXT("View") = "Out"),
ISNOTBLANK([Jam Masuk])
),
TIMENOW()
)
Editable?
FALSE
Reset on edit?
AND(
(CONTEXT("View") = "Out"),
ISNOTBLANK([Jam Masuk]),
ISBLANK([Jam Keluar])
)
Thanks Steve, is it alright if Im using Slices in the app? Because the โIFSโ and โANDโ formula above got error warning. โcould not be parsed due to exception: Sequence contains no elements.โ
Try to remove this:
Thanks Heru, the formula is correct now. But then another warning : Control โInโ could not find data โInโ and Control โOutโ could not find data โOutโ
Could you try to write it like:
AND(
("Out"= CONTEXT("View")),
ISNOTBLANK([Jam Masuk]),
ISBLANK([Jam keluar])
)
and similarly to the IFS also.
If still not working, please share screen shoot of the actual expression and also the error.
Yep its worked. But next problem, in the form โJam Pulangโ not showing. Wkwkwk
I am a bit lost without snapshot, Perhaps you need to change something to the Required? for the Jam Keluar.
If you save the form, did the number go through?
Replace In
and Out
with the names of the corresponding views in your app configuration.
Yep its worked. But next problem, in the form โJam Pulangโ not showing (time for presence out dissappear)
This is the first youโve mentioned Jam Pulang.
Fixed in the original. Thanks!
Thank you for Steve Coile and Heru Herlambang for guidance. After many configuration, it works.
For Jam Masuk , just like formula mentioned by Steve Coile.
And for Jam Keluar, the formula became like this:
Valif If
([_THIS] > [Jam Masuk])
Required?
ON
App Formula
IFS(ISNOTBLANK([Jam Masuk]), TIMENOW())
Editable?
ON
Reset on Edit?
([_THIS] = [Jam Masuk])
HI MY FORMULA BELOW HAS ERROR, โcould not be parsed due to exception: Sequence contains no elements.โ
IF (
(LEFT(MID(
CONCATENATE(TEXT(YEAR(TODAY())&โโ),"-",
IF(LEN(TEXT(MONTH(TODAY())))=2, TEXT(MONTH(TODAY())),
CONCATENATE(โ0โ,TEXT(MONTH(TODAY()) ))),
IF(LEN(TEXT(DAY(TODAY()))) =2, TEXT(DAY(TODAY())),
CONCATENATE(โ0โ,TEXT(DAY(TODAY()) ))),
โ-โ ,
RIGHT(
(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )),
(LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10) ) ), 6,4),4)),
=
(CONCATENATE(
IF(LEN(TEXT(MONTH(TODAY())))=2, TEXT(MONTH(TODAY())),
CONCATENATE(โ0โ,TEXT(MONTH(TODAY()) ))),
IF(LEN(TEXT(DAY(TODAY()))) = 2, TEXT(DAY(TODAY())),
CONCATENATE(โ0โ,TEXT(DAY(TODAY()) )))))
,
(CONCATENATE(
TEXT(YEAR(TODAY())&โโ),"-",
IF(LEN(TEXT(MONTH(TODAY())))=2, TEXT(MONTH(TODAY())),
CONCATENATE(โ0โ,TEXT(MONTH(TODAY()) ))),
IF(LEN(TEXT(DAY(TODAY())))=2, TEXT(DAY(TODAY())),
CONCATENATE(โ0โ,TEXT(DAY(TODAY()) ))),
โ-โ ,
IF(LEN( RIGHT(
LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ ),
LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10)) = 2,
CONCATENATE(โ0โ,
TEXT(NUMBER(
RIGHT(
LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ ),
LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10)
)+1)
),
CONCATENATE(โ0โ, TEXT(NUMBER(RIGHT(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ), LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10))+1))
)))
,
(CONCATENATE(TEXT(YEAR(TODAY())&โโ),"-",
IF(LEN(TEXT(MONTH(TODAY())))=2, TEXT(MONTH(TODAY())),
CONCATENATE(โ0โ,TEXT(MONTH(TODAY()) ))),
IF(LEN(TEXT(DAY(TODAY())))=2, TEXT(DAY(TODAY())),
CONCATENATE(โ0โ,TEXT(DAY(TODAY()) ))),
"-01โ))
)
THANKS,
Maybe delete comma as below:
Not really sure, but you might want to split and test that long expression to several (maybe 3) VC and check if the result is as expected.
This:
IF(LEN(TEXT(MONTH(TODAY())))=2, TEXT(MONTH(TODAY())),
CONCATENATE(โ0โ,TEXT(MONTH(TODAY()) )))
can be more simply expressed with the more efficient:
RIGHT(("00" & MONTH(TODAY())), 2)
Likewise:
IF(LEN(TEXT(DAY(TODAY()))) =2, TEXT(DAY(TODAY())),
CONCATENATE(โ0โ,TEXT(DAY(TODAY()) )))
can be replaced with:
RIGHT(("00" & DAY(TODAY())), 2)
With those substitutions:
IF (
(LEFT(MID(
CONCATENATE(TEXT(YEAR(TODAY())&โโ),"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
โ-โ ,
RIGHT(
(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )),
(LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10) ) ), 6,4),4))
=
(CONCATENATE(
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2)))
,
(CONCATENATE(
TEXT(YEAR(TODAY())&โโ),"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
โ-โ ,
IF(LEN( RIGHT(
LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ ),
LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10)) = 2,
CONCATENATE(โ0โ,
TEXT(NUMBER(
RIGHT(
LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ ),
LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10)
)+1)
),
CONCATENATE(โ0โ, TEXT(NUMBER(RIGHT(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ), LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10))+1))
)))
,
(CONCATENATE(TEXT(YEAR(TODAY())&โโ),"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
"-01โ))
)
Youโre using TEXT() unnecessarily. Without them:
IF (
(LEFT(MID(
CONCATENATE(YEAR(TODAY()),"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
โ-โ ,
RIGHT(
(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )),
(LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10) ) ), 6,4),4))
=
(CONCATENATE(
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2)))
,
(CONCATENATE(
YEAR(TODAY()),"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
โ-โ ,
IF(LEN( RIGHT(
LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ ),
LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10)) = 2,
CONCATENATE(โ0โ,
(NUMBER(
RIGHT(
LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ ),
LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10)
)+1)
),
CONCATENATE(โ0โ, (NUMBER(RIGHT(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ), LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10))+1))
)))
,
(CONCATENATE(YEAR(TODAY()),"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
"-01โ))
)
If there were a column named Latest Tracking # with an App formula expression of LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )
, the expression would be:
IF (
(LEFT(MID(
CONCATENATE(YEAR(TODAY()),"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
โ-โ ,
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) -10) ) ), 6,4),4))
=
(CONCATENATE(
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2)))
,
(CONCATENATE(
YEAR(TODAY()),"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
โ-โ ,
IF(LEN( RIGHT(
[Latest Tracking #],
LEN([Latest Tracking #]) -10)) = 2,
CONCATENATE(โ0โ,
(NUMBER(
RIGHT(
[Latest Tracking #],
LEN([Latest Tracking #]) -10)
)+1)
),
CONCATENATE(โ0โ, (NUMBER(RIGHT([Latest Tracking #], LEN([Latest Tracking #]) -10))+1))
)))
,
(CONCATENATE(YEAR(TODAY()),"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
"-01โ))
)
Reformatted for clarity:
IF(
(
LEFT(
MID(
CONCATENATE(
YEAR(TODAY()),
"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
"-",
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) - 10)
)
),
6, 4
),
4
)
= CONCATENATE(
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2)
)
),
CONCATENATE(
YEAR(TODAY()),
"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
"-",
IF(
(
LEN(
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) - 10)
)
)
= 2
),
CONCATENATE(
"0",
(
NUMBER(
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) - 10)
)
)
+ 1
)
),
CONCATENATE(
"0",
(
NUMBER(
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) - 10)
)
)
+ 1
)
)
)
),
CONCATENATE(
YEAR(TODAY()),
"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
"-01โ
)
)
From your original expression, this:
(LEFT(MID(
CONCATENATE(TEXT(YEAR(TODAY())&โโ),"-",
IF(LEN(TEXT(MONTH(TODAY())))=2, TEXT(MONTH(TODAY())),
CONCATENATE(โ0โ,TEXT(MONTH(TODAY()) ))),
IF(LEN(TEXT(DAY(TODAY()))) =2, TEXT(DAY(TODAY())),
CONCATENATE(โ0โ,TEXT(DAY(TODAY()) ))),
โ-โ ,
RIGHT(
(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )),
(LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10) ) ), 6,4),4)),
=
(CONCATENATE(
IF(LEN(TEXT(MONTH(TODAY())))=2, TEXT(MONTH(TODAY())),
CONCATENATE(โ0โ,TEXT(MONTH(TODAY()) ))),
IF(LEN(TEXT(DAY(TODAY()))) = 2, TEXT(DAY(TODAY())),
CONCATENATE(โ0โ,TEXT(DAY(TODAY()) )))))
amounts to this:
(
LEFT(
MID(
CONCATENATE(
YEAR(TODAY()),
"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
"-",
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) - 10)
)
),
6, 4
),
4
)
= CONCATENATE(
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2)
)
)
Within that, this:
CONCATENATE(
YEAR(TODAY()),
"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
"-",
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) - 10)
)
)
constructs text of the format, YYYY-MMDD-##...
. You then use MID(..., 6, 4)
to extract the MMDD
componet, the use LEFT(..., 4)
toโฆdo nothing else, getting the same MMDD
. So that entire LEFT() expression amounts to:
CONCATENATE(
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2)
)
which makes the comparison:
(
CONCATENATE(
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2)
)
= CONCATENATE(
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2)
)
)
which is always TRUE, which then means this will never be evaluated:
(CONCATENATE(TEXT(YEAR(TODAY())&โโ),"-",
IF(LEN(TEXT(MONTH(TODAY())))=2, TEXT(MONTH(TODAY())),
CONCATENATE(โ0โ,TEXT(MONTH(TODAY()) ))),
IF(LEN(TEXT(DAY(TODAY())))=2, TEXT(DAY(TODAY())),
CONCATENATE(โ0โ,TEXT(DAY(TODAY()) ))),
"-01โ))
Given that, the entire enclosing IF() expression can be removed entirely, leaving only this:
(CONCATENATE(
TEXT(YEAR(TODAY())&โโ),"-",
IF(LEN(TEXT(MONTH(TODAY())))=2, TEXT(MONTH(TODAY())),
CONCATENATE(โ0โ,TEXT(MONTH(TODAY()) ))),
IF(LEN(TEXT(DAY(TODAY())))=2, TEXT(DAY(TODAY())),
CONCATENATE(โ0โ,TEXT(DAY(TODAY()) ))),
โ-โ ,
IF(LEN( RIGHT(
LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ ),
LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10)) = 2,
CONCATENATE(โ0โ,
TEXT(NUMBER(
RIGHT(
LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ ),
LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10)
)+1)
),
CONCATENATE(โ0โ, TEXT(NUMBER(RIGHT(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ), LEN(LOOKUP(MAX(Permits 2020[_ROWNUMBER]),โPermits 2020โ,"_ROWNUMBER",โTracking #โ )) -10))+1))
)))
or, simplified and reformatted:
CONCATENATE(
YEAR(TODAY()),
"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
"-",
IF(
(
LEN(
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) - 10)
)
)
= 2
),
CONCATENATE(
"0",
(
NUMBER(
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) - 10)
)
)
+ 1
)
),
CONCATENATE(
"0",
(
NUMBER(
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) - 10)
)
)
+ 1
)
)
)
)
Here, note that both cases for the IF() expression do exactly the same thing, rendering the IF() conditional entirely pointless. We can remove it to further simplify to:
CONCATENATE(
YEAR(TODAY()),
"-",
RIGHT(("00" & MONTH(TODAY())), 2),
RIGHT(("00" & DAY(TODAY())), 2),
"-",
CONCATENATE(
"0",
(
NUMBER(
RIGHT(
[Latest Tracking #],
(LEN([Latest Tracking #]) - 10)
)
)
+ 1
)
)
)
As far as I can tell, that is the entire functionality of your original expression. Given the complexity of your original expression, thereโs a good chance I overlooked something.
User | Count |
---|---|
53 | |
29 | |
20 | |
9 | |
9 |