Dependent dropdown with date (weekday or weekend) as a qualifier

I have a set of locations, each with a set of shifts. However, the shift times are different on the weekends versus weekdays. I have the first dependent dropdown Valid_If for Location working correctly. I also have the expression to validate if the shift date entered is a weekday or weekend.

I’ve created my Shifts entity table with 3 columns:

Location, Weekend/Weekday, Shift

Data:
Location1, WD, 8-12
Location1, WD, 12-5
Location1, WE, 9-1
Location1, WE, 1-6
Location1, WD, 9-2
Location1, WD, 2-7
Location1, WE, 8-1
Location1, WE, 1-4

This is the expression I have so far, but I can’t figure out how to make it work:

IN([Shift], SELECT(Shifts[Location], [Shift].[Weekday/Weekend] = IF(IN(WEEKDAY(TODAY()),LIST(1, 7) ), “WE”, “WD”)))

What I want is for the Shift dropdown to include all Weekend or Weekday shift times for a location based on the shift date that is entered above.

I think you were very close!

There are some details that I have made assumptions about so I’ll rely on you to correct me.

If I understand correctly, in a second table you are trying to assign shift details. You want a Shift column that will show a dropdown list of options of Shifts based on WE or WD from your table posted above.

In your Shifts table you first need to have an appropriately identified Key. Then you want to set the Shift column as the Label column. This is what is presented in the dropdown.

In the second table you would create a Ref column, named anything but to prevent confusion lets call it “Selected Shift”. In the Valid_If of the “Selected Shift” column you would insert the expression:

SELECT(Shifts[_ComputedKey], 
              [Weekend/Weekday] = IF(IN(WEEKDAY([Date]),LIST(1, 7) ), “WE”, “WD”))

[Date] is the column in this second table where the app user chooses the date for which the want to select shifts for. The result would be something like the below:

EDIT:  Of course, in my rush I named the "Selected Shift" column mentioned above 
simply as Shift.  In the examples below, pretend Shift is actually named 
"Selected Shift".

For 11/3/2019, tapping Shift dropdown gives you Shifts shown on right

36%20PM

For 11/4/2019…
45%20PM

I hope this helps!!

2 Likes

Sorry, nevermind all that; not enough coffee. I was putting the expression in the wrong table (Shifts).

It’s still not working quite right, but I think I might need an actual unique ID in the table Shifts instead of using a computed key. I’ll keep working at it.

@WillowMobileSystems, thanks! This is very helpful and I’m almost there. (I had reversed Weekday/Weekend in my question above based on what I actually have in my table, so I fixed that.)

However [date] is coming from the current form/table (called ActivitySheets). When I enter the formula you have above, I get the following error:

SELECT(Shifts[_ComputedKey],

[Weekday/Weekend] = IF(IN(WEEKDAY([Date]),LIST(1, 7) ), “WD”, “WE”))

Unable to find column ‘Date’, did you mean ‘Shift’?

I think I need some help understand how table and column references work. I tried this, but got this following error instead:

SELECT(Shifts[_ComputedKey],

[Weekday/Weekend] = IF(IN(WEEKDAY(ActivitySheets[Date]),LIST(1, 7) ), “WD”, “WE”))

Parameter 1 of function WEEKDAY is of the wrong type

Thanks! What part is not working? In my little tester app I purposely did NOT use an ID column opting for the _ComputedKey simply to to make sure it worked that way (Sidenote: I almost always use a dedicated ID column).

Thanks for your help. Sorry, I’m just getting back to this issue. The problem is that I need to filter on location, also. Right now I’ve got weekend/weekday working, but it will return the results from every location. I have this so far, but it’s not working:

SELECT(Shifts[_ComputedKey],
[Weekday/Weekend] = IF(IN(WEEKDAY([Date]),LIST(1, 7) ), “WE”, “WD”), ([Location] = Shifts[Location]))

I’m not sure how to capture the selected location from the current form and compare that with the list of locations in the Shifts table.

Since you are now filtering on two items you’ll need to use an AND(). Also, when filtering you simply need to compare against the Store location you are entering the row for. BUT because your fields have the same name you’ll need to use the [_THISROW] variable.

So your actual expression should look like this:

SELECT(Shifts[_ComputedKey],
       AND(
           [Weekday/Weekend] = IF(IN(WEEKDAY([Date]), LIST(1, 7) ), “WE”, “WD”), 
           [Location] = [_THISROW].[Location]
        )
)

Good luck! And post again if you still need help.

1 Like