Hello all, Here is what I'm trying to accompl...

[jaron_turkingto]

Hello all, Here is what I’m trying to accomplish, if anyone has any ideas how to go about it: I have a table for a message board.

Each row is a comment, and is tagged to a particular project.

When viewing the project, a user can use an action button to go to a filtered view of the message board, showing only messages for that project. Now, when adding a new message, the user must select a subject.

I would like for the user to be able to either choose a subject that has already been used for a particular project, or else start a new subject.

In this way, the UX for the message board can be grouped by subject. The problem is that when I set the ENUM for the “subject” column to allow other values, it will show all subjects that have ever been used for any project, even though the view is filtered.

I can of course use a Valid if constraint, but then the user will not be able to add his own subject. Does anyone have any ideas how else I might have both a filtered list of previous values and the option to add new at the same time?

[Harry2]

@jaron_turkington Here’s a possible solution:

1/ First create a table that stores all existing subjects. Each subject will be a row in this table. Ensure that this table allows add operations.

2/ In the message table, change the subject column into a ref column that references the subject table.

3/ For this new ref column, use a SELECT statement that selects subject based on project as the Valid_If constraint of the column.

Once this is done, each time the app user creates a new message, she can either select an existing subject in the drop-down list of filtered subjects, or she can click on the “Create new” button in the drop-down to add a new subject directly to the subject table.

Of course, you will need to figure out how to implement the SELECT statement to filter subjects based on project.

[jaron_turkingto]

@Harry Thank you.

That does work well.

The only disadvantage is that the user will have to manually enter the Project Number when creating a new subject, unless there is a way to set the initial value. But since the message doesn’t save back to the spreadsheet until the subject has been entered, I don’t see a way to do that.

Edit: having to remember the project number from a previous screen so as to enter it again when creating a new subject is a deal breaker for user experience when posting to a message board/forum.

I’ve been trying various things unsuccessfully.

Let me know what you think of my latest idea:

The action that goes to a view of the message board can be changed to a series of actions, the first of which would be to write the useremail to a new column in the project table.

Then, after having left that view and moved to the message board view, when the user is creating a new subject, the project number column can be set up with an initial value using a SELECT that looks for the useremail in the projects table (this would prevent mixups if several users are posting simultaneously).

Later, I would just need to execute an action on saving the post that would clear that useremail from the column.

Since it is only a placeholder, it will only ever appear once at a time, so I am wondering if I can locate it using a FIND or SELECT. This is my next experiment, unless anyone has any input or some obvious solution I’ve overlooked.