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

[Ryan_Rands]

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.

[WillowMobileSys]

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

For 11/4/2019…

I hope this helps!!

[Ryan_Rands]

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

[WillowMobileSys]

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

[Ryan_Rands]

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.

[WillowMobileSys · Accepted solution]

Ryan_Rands:

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

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.

[Ryan_Rands]

@WillowMobileSystems, thanks again. This is working, but the location name and WE or WD show up in the shift description that is shown to users and I’d like to remove that if I can.

The user result looks like this: Location 1:WD:12:30PM-5PM

I’d like to just show: 12:30PM-5PM

[WillowMobileSys]

This is most likely because the _ComputedKey value is being used as the Label property for the Shifts table. Simply go to the Columns list for that table, uncheck the _ComputedKey column as the Label and then check Label for the column that gives you the shift time range.

AppSheet performs a display translation and uses the column (or columns) marked as Label for display instead of the key column.

[Ryan_Rands]

The Label property in the table Shifts was already set to Shift. Just to be sure I switched it to Computed Key and then back again, but the result looks the same. Can you think of anything else I should check?

[WillowMobileSys]

Hmm, I probably don’t have all the details. When values are displayed with “:” that is a good indication that what’s being used is a Computed Key. So, we may not be in sync on where you are seeing the issue.

Can you provide some images of where the issue is shown? Also, it might help to show images of the data and column definitions as well.

[Ryan_Rands]

Found it. Shift was TYPE Text. Changed it to Ref and now I’m getting the format that I want.

Thanks again!