Need help with a date select expression

[Mic_L_Angelo]

Hey folks, I help with an expression I’m having trouble with writing.

I have an application for managing weekly schedules for people on duty.

I have two tables.

A SCHEDULE table - how a list of weekdays listing which user is assigned to which day.
(Jane has Monday’s date, Mark has Tues. date, etc.).
[User] - list the user
[Day] - list the date of the weekday

A SHIFT table - listing specific days user has traded
(Jane covering Tues. 2/4/20, Mark covering Fri. 2/7/20 etc. )
[User] - list the user
[Request Start] - list the date the shift starts
[Request End] - list the date the shift ends
[Trade?] - list if they need to trade

I need an Action expression that grabs the user’s next scheduled date from the SCHEDULE table and add it to the SHIFT table if the date is not already in there.

(In English, If the user wants to trade a shift that hasn’t already requested, their next scheduled shifts will be automatically added in the SHIFT table entry.)

Here’s what i wrote as an action in the SHIFTS tale

MIN(SELECT(Schedule[Day], 

AND([Day] >= [_THISROW].[Shift Start],

[Day] <= [_THISROW].[Shift End],

[User] = [_THISROW].[User],

[Trade?] = "Yes",

)))

I wrote is as “If the user indicated they want to trade, select the user’s next scheduled date from the Schedule Table. Filter the value by the date after the user Request Start field and before the Request End field”

However, it’s grabbing the wrong date. I’ve been stuck for a while and could use some assistance. Is anyone willing to lend a hand?

[Aleksi]

Is it because you have [Shift Start] twice?

[Mic_L_Angelo]

@Aleksi sorry that was a copy/paste error. I updated the code accordingly.

[Steve]

Mic_L_Angelo:

it’s grabbing the wrong date

In what way is the date wrong?

[Mic_L_Angelo]

@Steve See image below.

1.“themiclangelo”'s next scheduled date should be 2/1/20. Instead, It’s grabbing 1/29/2020, the next date on the list

2. “IGBoston” should be blank as it’s not in the scheduled list at all

So it seems it’s not filtering the date by the user who made the Shift entry

[Steve]

Please provide View Data screenshots of the Schedule and Shift tables that include the columns you’ve mentioned in your original post.

[Mic_L_Angelo]

@Steve here you go.

[Steve]

[Mic_L_Angelo]

@Steve sorry, typing error. But that’s not the issue.

FYI, i renamed the values and fields to make this easier to explain on here, so i may have accidentally mistyped while i explained it.

the code runs, it’s just giving me the wrong value.

[Mic_L_Angelo]

@Steve For a bit of context, see the new screenshot.

This is what the Shifts spreadsheet looks like when I add a new row.

In the Schedule table, themiclangelo’s dates are listed for 1/29/20, 2/1/20 & 2/4/20

In the Shifts’ table, themiclangelo indicated to trade all his scheduled dates from 1/29/20 to 2/7/20
(See Row #3)

So I want to create an action that, when pressed, will in the earliest date themiclangelo scheduled for. (in this this case, it would be 1/29/20

The two rows in this screenshot show the result of that action.

Row #4 worked as expected; it input 1/29/20 as the Shift Start and Shift End date.

However, Row #5 still input 1/29/20, when it should have put 2/1/20

It seems the [User] = [_THISROW].[User] rule is not recognized when the action is pressed, which would pull themiclangelo next earliest date. Instead, it’s inputting the earliest overall date.

Essentially. I need to know is how modify my current code to filter out a user from another sheet using an action.

I hope this makes sense. I know it sounds complicated. Let me if you have any questions.

[Steve]

Is there a Trade? column in Schedule?

Please also post a screenshot of the action’s expression rather than the transcription above.

[Mic_L_Angelo]

@Steve No, the "Schedule table does not. That’s just used as a check to run the action

I pasted the actual expression. (It was too cumbersome to rename the columns. “Moderator = User”)

But i need a just function that gives me a filtered list of all the Moderators in “The Scheduler” table based on a Moderator field in “The Shifts” table.

That’s the part of the code I’ve having trouble with.

[Steve]

The original expression you posted is entirely different from this screenshot. In the future, I encourage you to always post the actual expression. The expression you posted originally looked good to me, so I’ve been confused why it hasn’t worked for you. The expression in the screenshot, though, is obviously bad.

[Mic_L_Angelo]

@Steve this is the current version of the expression,

I was trying some other ideas since it seems there was no clear solution to the original version.

I pasted the original one below (i just renames some of the columns). However, It gives me the exact same result

Yes, I know the current one is bad, which is why I was still asking for help.

Sorry for the earlier confusion. I was hoping to explain the scenario as simple as possible.

But in any case, no need to worry about this anymore. It seems what I’m asking for is a bit too complex to keep asking yall for help. So I’ll figure something else out.

[Mic_L_Angelo · Accepted solution]

@Steve @Aleksi

Just a follow up guys. I figured it out

NOT(IN([Next Shift], The Shifts[Nom Shift]))

This omits every date in the Schedule sheet that was already in the Shift sheet.

Therefore i get the users’ earliest date not already covered in the “Vacay” entry, as expected.

Without this, i was getting the Moderator’s earliest date, covered or not.

I sent a screenshot of the result.

In any case, just wanted to let you guys know how I solved it.