Solved: Can't figure out the formula

BLMBanjarmasin · 06-28-2020 09:41 PM

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

BLMBanjarmasin

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])

View solution in original post

hru

Move your expression of NOW() or TIMENOW() to Initial Value.

BLMBanjarmasin

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

hru

Try to follow through the guidance from Steve above and let us know.

Steve

Jam Masuk

Required?

(CONTEXT("View") = "In")

Initial value

TIMENOW()

Editable?

FALSE

Reset on edit?

AND(
  (CONTEXT("View") = "In"),
  ISBLANK([Jam Masuk]),
  ISBLANK([Jam Keluar])
)

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])
)

BLMBanjarmasin

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.”

hru

Try to remove this:

BLMBanjarmasin

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’

hru

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.

BLMBanjarmasin

Yep its worked. But next problem, in the form “Jam Pulang” not showing. Wkwkwk

hru

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?

Steve

Replace In and Out with the names of the corresponding views in your app configuration.

BLMBanjarmasin

Yep its worked. But next problem, in the form “Jam Pulang” not showing (time for presence out dissappear)

Steve

This is the first you’ve mentioned Jam Pulang.

Steve

Fixed in the original. Thanks!

BLMBanjarmasin

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])

Rey_Urbina

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,

REY
BUTCHUPANO@GMAIL.COM

hru

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.

Steve

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.