Another use for one-to-one
I thought of another use for a one-to-one relationship that I do in fact still use. Might not apply to anything anybody would do with Knack, but I forgot about it earlier.
For many years now all my clients have been law firms doing litigation. In the apps I’ve built (in FileMaker) for some of my firms, there’s a single CLIENTS table, and then linked tables for each new lawsuit that they file. Every client they represent appears in a record in the CLIENTS table, and then, if that person is part of an active lawsuit, they’ll have a (single) linked record in the table for that lawsuit, as well. In other words, there might be 5000 records in CLIENTS but only the 300 who are part of the current lawsuit against Acme Inc will have records in the ACME table. Their unchanging personal info goes into the client record, and info that’s specific to any lawsuit they are part of goes into the case table (Acme, or whatever). That relationship is one-to-one. There are pros and cons to this approach and I haven’t used it in all my apps. But one advantage is that it makes it easy to see if a client has ever been part of another lawsuit. (It’s not common, but it happens.) Almost makes it easy for the law firm to do a mass mailing that sends just 1 communication to each person they’ve ever represented.
I think this is an interesting example, almost an exception that proves the rule. In this example, the entity person (represented by a record in Clients) is logically distinct from the entity plaintiff-in-a-particular-lawsuit and thus I think this approach, while it looks very unorthodox, might be defended as technically kosher. Not that I have anything against de-normalization!
Bottom line: One-to-one relationships do occasionally make sense. Or at least they seem to, if you squint and turn your head sideways. 
.
Filtering options from linked table in a form
You wrote:
I seem to remember that either a multiple choice or a connected field selection in a form can have the available options filtered. That may be a clue here. But I don’t know the process to make that option appear for the field. I’ll need to play around more.
I’m not 100% sure I understand you but it sounds like you’re talking about what in the FileMaker world at least we would call a conditional or contingent value list. This occurs when the child record (say, an order placed online) may have options that are themselves contingent on some other previously selected options. If you pick “Fruit” in field A, the options in field B are apple, banana, grape, etc. but if you picked “Dessert” in field A, the options in field B might be jello, apple pie, chocolate cake, and flan. (Pro tip: Don’t order the flan.)
I’m being detailed here because **as far as I can tell, we cannot do this in Knack, but I would love to be told I’m wrong.**
ADDED A DAY LATER: I AM DELIGHTED TO ACKNOWLEDGE THAT I WAS WRONG! See Peter Jurgen’s succinct but illuminating reply below.
I am working today on a to-do app. Tasks are linked to a table called Groups. A record in Groups has two important fields:
- Category: options are limited to Personal and Work
- Project: can be anything so long as it’s unique
Now when I create a task, what I’d LIKE to do is click on a radio button to pick Personal or Work, then click into the Project field and see a list of projects for that category. But as far as I can tell, that’s not possible.
NOTE: It is possible to filter the options that appear in that dropdown to some extent. So there’s a third field in Groups I didn’t mention above: Active. In Knack this is a Yes/No field. When I click into the Projects connection field for a new task, I want to see only projects that are active. So I filter that field to show only projects whose Active field value is “Yes”. But this is a literal string. As I said, as far as I can tell, there’s no way to say “Show me projects that have the Category I already selected.”
Looking forward to being told that I’m totally wrong here!
.
The workaround for contingent choice lists
THE FOLLOWING, WHILE PERTINENT TO AIRTABLE, IS NOT NECESSARY IN KNACK AND YOU SHOULD IGNORE IT.
Finally, if I’m NOT wrong, then the workaround — which I’ve perforce chosen — is to create a text equation field in the parent table (Groups) that combines the two fields. So in my Groups table, the primary field is actually an equation field with this formula:
* *{Category}: {Project}* *
Which produces a choice list over in Tasks that looks like this:
Personal: Home
Personal: Medical
Personal: Pets
Personal: Travel
Work: Office
Work: Clients
and so on. When I click into the new connection field “Cat: Pro”, I type “P” and filter the options down to all the Personal ones. This is how you have to do it in Airtable, too, by the way. Not ideal, but does get the job done.
