Help with expression

tcanelli · 03-18-2020 08:20 AM

I need help with this…it’s not picking up the OR part. I need something that says if check in and out are both blank so action. If both check in AND checkout are not blank show action. I don’t want the action to show if check is in not blank but checkout is blank.

AND(ISBLANK([Recent Row].[Check In Time]), (ISBLANK([Recent Row].[Check out Time]), OR(ISNOTBLANK([Recent Row].[Check In Time]), ISNOTBLANK([Recent Row].[Check out Time]))))

tcanelli

@Steve, @WillowMobileSystems, @LeventK - can any of you please help me? need to get something fixed ASAP. Thanks

WillowMobileSys

“so” action??? I’ll assume you meant NO action

I had an expression but not sure if it matches your needs. Can you describe the what the action is for? It might help make sense of the expression

tcanelli

Sorry, the action button. When employees open the app the first time it opens to a view for them to sign in - action button adds row to the time in/out table.

I want this action button to disappear until they checkout. Then it should reappear so they can check back in.

WillowMobileSys

So they checkout first and then Check back in to complete the checkout? Do I have that right?

If so then I think you just simply need this:

AND(ISNOTBLANK([Recent Row].[Check out Time], ISBLANK([Recent Row].[Check In Time])

tcanelli

no, the very first time they are just checking in.

then they checkout after shift.

then next day they will check back in…and so on.

I’m trying to hide the button after they check in so they don’t keep adding rows to the check in table. This actually worked. However, when someone checked out the button didn’t come back.

tcanelli

so what you had is what I used. but I also need it to be if

check in time is not blank AND check out time is not blank - show the button again.

WillowMobileSys

Just based on what you said this is what you would need.

OR(
   AND(ISNOTBLANK([Recent Row].[Check out Time], ISBLANK([Recent Row].[Check In Time])
   AND(ISNOTBLANK([Recent Row].[Check out Time], ISNOTBLANK([Recent Row].[Check In Time])
)

But I am confused because I am not sure how you tracking the multiple check-in/check-outs. It doesn’t seem you should ever allow a case where Check-Out has been filled in but Check-In is blank.

So is a Check-In button only and you are trying to show/hide it at the proper times?

tcanelli

I keep getting errors. i guess i’ll just have to leave the action button showing and hope people don’t click it if they’re already checked in.

WillowMobileSys

If you could explain a bit more what you are doing I can certainly help get you the exact expression you need. But things are not adding up and I need more information to help.

tcanelli

Here are some screen shots - i know i’m not explaining it well.

first step - check in

Then when checked in is not blank (this is the recent row ref) but checkout IS blank I want the action button to go away.

Then to checkout they access their record and click the button with the x.

now we have both checked in and checked out complete and neither are blank I need the original check in button to show again.

tcanelli

Just FYI - the check in button (blue head with checkmark) is the personnel table - the checked in and checked out column in that view are virtual columns for recent row ref.

The blue head with x for check out is in the time log table.

WillowMobileSys

If [Check In Time] and [Check Out Time] are Virtual Columns, then the Action expression should probably not use [Recent Row] - though you would need to use [Recent Row] inside of the VC’s.

So the expression should probably be:

OR( ISBLANK([Check In Time]),
        AND(ISNOTBLANK([Check Out Time]), ISNOTBLANK([Check In Time])) 
)

[Recent Row] is a Virtual Column as well? I am not clear if the VC will update immediately when a new row is added or a sync is required. @Steve ??

If that’s a problem, then maybe those columns need to be added to the datasource.

WillowMobileSys

That helps a lot! So when a NEW Check-In occurs you are at that time re-setting Check-Out to blank?

What I had before should work but… (EDITED: Just realized my expression earlier was missing parens.
Maybe why you were seeing errors.)

I think I would handle it like this:

OR( ISBLANK([Recent Row].[Check In Time]),
        AND(ISNOTBLANK([Recent Row].[Check out Time]), ISNOTBLANK([Recent Row].[Check In Time])) 
)

If you ever have the case where Check-In is blank but Check-Out is not blank, that would be a bug in my opinion and needs to be corrected.

tcanelli

It doesnt reset checkout. Its adding a new row to the time table.

tcanelli

the multiple checkins are happening with the related time log.

they really can’t checkout without checking in as that is also an action button and only appears if there is a check in time and no check out time.

Bahbus

I have a simpler solution. You could add a YES/NO VC called CheckedIn? with an initial value of TRUE (since when a new row is created, they are checking in). When they checkout, have that value set to FALSE. Then use this to show the button:

NOT(IN(TRUE, SELECT(Time IN and Time OUT Log[CheckedIn?], <expression to select current user>)))

The expression to select the current user, I would imagine would be based off either USERNAME() or USEREMAIL().

tcanelli

Do I need an expression to select the user if there is a security filter and they only see their info?

tcanelli

Seems the initial value for virtual columns is not editable.

Is this a glitch?

Bahbus

Oh, yeah I guess I forgot initial values for VCs don’t exist. In that case, set the app formula to
AND(ISNOTBLANK([Check In Time], ISBLANK([Check Out Time]))

In that case, NOT(IN(TRUE, Time IN and Time OUT Log[CheckedIn?]))

tcanelli

Thank you both. I will try this tonight after people are out of here.

Bahbus

Any luck @tcanelli?

tcanelli

Yes, - I can’t believe how simple it ended up being: OR(ISBLANK([Checked In]), ISNOTBLANK([Checked Out]))

But first day I learned a LOT about user behavior and have made improvements all week to prevent mishaps. I think we are 99% there. What I thought was so easy apparently wasn’t.

Bahbus

That’s almost how it always is in the “programming” world. Something difficult ends up being easy. Something seemingly easy ends up being impossibly hard. And the stuff that all makes sense to you ends up making zero sense to the end user. It’s a common thing.