Hi, Guys: New problem. I have a list of cour...

(Jayne Hawkins) #1

Hi, Guys:

New problem. I have a list of courses set up as enum in the column definition. The list of courses have been entered individually as Enum Values.

The list contains a mix of public and private courses.

I’m using a form view to add participants to the courses. However, I only want the public courses to appear in the dropdown list of courses (so that random people aren’t accidentally added to a private course). It doesn’t look like a filtered slice is working.

I also tried a formatting rule to strikethrough the private courses, but I’m guessing the enum list overrides all of this.

How can I filter out the private courses and only show the public courses in the form’s dropdown list? Can it be done?

I can’t use a dependent dropdown because I don’t have another field to refer to (since the public/private column is populated by the course via LOOKUP).

(Jacob Lin [AppSheet]) #2

@Jayne_Hawkins So from what I understand, you have a column that defines if the course is public or private? If that’s true, you can just edit the column you’re using for the dropdown list. When you go to ‘Edit Column Definition’, make sure your column type is set to Enum and set EnumInputMode to ‘Dropdown’. Then, modify the Valid_If expression to SELECT only rows whose public/private column is ‘public’. E.g. SELECT([Course Name], [Public/Private] = “public”). Modify that expression to fit your own data, but it should work.

(Jacob Lin [AppSheet]) #3

Oh yeah, remove all the courses you inputted from the EnumValues, this Valid_If statement will populate for you.

(Jayne Hawkins) #4

+Jacob Lin I have two tables, Participants and Courses. Participants is where the actual form data goes. Courses is a pre-populated list of courses and details (time, date, location, etc).

The reason I manually entered the EnumValues was because the ‘course’ column of the Participants table is blank (until a form is submitted of course). Once the user selects a course, most of the other fields in the Participants table are populated via LOOKUP (taking the information from the Courses table, such as date, time, etc.). One of the LOOKUP columns is Public/Private - so the public/private status isn’t populated until after the course has been chosen. Cart before the horse, as they say. With the way it’s currently set up, if I remove the EnumValues, that will leave me with no courses in the dropdown, correct?

See attached image. What I really need is for the “Course” dropdown options to come from the Courses table… but I need the City, Venue, Date, etc. to be looked up and populated into the Participants table. I can’t find a way to populate that one “Course” dropdown field from the other table.

I don’t think populating the dropdown with courses from the Participants tab will work (unless they’re manually entered as EnumValues, like I’ve done) because the only courses that appear on that list are courses for which someone has already registered. I need to draw from the full list of courses on the Courses table.

Make sense?

(Jacob Lin [AppSheet]) #5

@Jayne_Hawkins Ok, so what this boils down to is retrieving data from a second table to put into your Course dropdown, based on the ‘public’ column in your second table? I’m again assuming that your ‘public’ column is an actual column in your Courses table.

Try this in Valid_If of Course dropdown: SELECT(Courses[Name], [Public] = “public”)

Replace “Courses[Name]” with the column that you want the enum to make a list of, I’m just guessing that you’d want the course name.

You’ll have to remove the manually entered Enum Values. Manually entering them when

you want to base their selection off another column will never work.

(Steven Coile) #6

@Jayne_Hawkins Remove the Enum values from the [Course] column of the Participants table, change its type to Ref and set the ReferencedTable property to Courses, and add a Valid_If expression that draws in the appropriate courses from the Courses table:

=filter(Courses, in([Public/Private], list(“Public”)))

Then, in the other columns in Participants you want auto-filled from the course information, set the App formula for (e.g.) the [City] column to to something like:


(Jayne Hawkins) #7

+Jacob Lin & +Steve Coile - YESSSSSSSSSSS. Exactly what I’m trying to do (both explanations)! Thanks!!