Why Aren't One to One Connections Enforced?

Hello,
After reading the documentation about connection fields, it says that One to One relationship fields are NOT enforced. I can confirm this because if you create this relationship, it’s technically a one-to-many functionality. If you make a list of something that only belongs to one other thing, the app lets you assign one thing to many others, instead of removing it from the list of available selections.

I’m curious why this is?
This makes things blurry in certain types of apps and can really limit the type of data that Knack can handle.

1 Like

@RealEyesDisplays

Hi Alex, good question, I’m not sure why this doesn’t work as it would in other databases, specifically, for me MS Access.

That said, I can’t remember the last time I needed to use a one-to-one relationship in Knack.

It should be rare in any relational database design. In fact, it could indicate that two entities actually belong in the same object.

When I started building databases I was encouraged to split tables to reduce memory usage, this entailed using one-to-one relationships, essentially using another table as an extension.

Not something we need to worry about nowadays.

Hi Carl,
Agreed - but though it seems like it would be a rare use, I think the functionality of this is quite extensive.

Just off the top of my head - any business that has a limited number of resources that need to be allocated would benefit from this. Whether it’s staff that are assigned a job by location, rental equipment, or even a licensing system for usage of an asset.

And even more, one to one relationships can also be used for prioritization, which is how I found this limitation in the first place. Let’s say you need have a task list and want to assign the top 3 things to work on today. Or let’s say you have a group of quizzes or forms and need someone to complete them in a certain order. Though these are possible with a one to many relationship, it’s messy, because the user can continually assign the same order or priority to something…essentially defeating the whole purpose.

The point is, there’s probably hundreds of additional uses for this type of relationship. It can be extremely powerful if it worked as intended.

1 Like

Some interesting points well made Sir :sunglasses:

1 Like

I asked about this myself a while back. Jesse and Carl suggested that there is a workaround: Configure your new-connected-record form so it is hidden if a connected record already exists.

Enforcing cardinality: possible? - Help Center / Get Answers - Knack Community Forum

Seems like a satisfactory workaround.

I have used one-to-one relationships now and then in the past, in FileMaker. I did it for different reasons. Sometimes it made it easier to control access to data by users with different levels of privilege. Say I had a table named Employees. And say further that a lot of the info about employees is okay for access by all users, but some of the data (personal evaluations, perhaps) is intended to be access only by supervisors. And in the more distant past, I used one-to-one for reasons having to do with storage. In one app that I recall, the secondary connected table was in a secondary file which contained nothing but large image files (plus of course a key field that allowed the link to the main table in the other file). Backup options in FileMaker are on a per-file basis. So by putting the images in a separate file, I could configure that file so it wasn’t getting backed up so often.

But I haven’t used one-to-one relationships for a few years now.

I do agree that, if Knack’s relationship set-up dialog OFFERS a one-to-one option, it ought to be enforced. Otherwise, it’s like this conversation at a restaurant:

WAITER: Chateaubriand and pommes frites. Very good sir! And will you be having wine as well?
ME: Yes, wine sounds good. House red, please.
WAITER: I’m sorry, we don’t actually serve wine.

Thanks guys, I’m glad there’s some support behind this, and I like your wine example :slight_smile:

I’ll need to take a look at the work around you’re recommending. Off the top of my head, I’m not sure it will apply to my use-case, but perhaps it can point me in the right direction.

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.

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. :slightly_smiling_face:

.

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.

You mean Dynamic Dropdowns?

1 Like

Yes yes yes!

This is exactly what I mean. I promise, Scout’s honor, I had searched for this but didn’t find it. Brilliant.

I have just implemented this in my own solution — and I will correct my remarks above.

¡Muchas gracias, Peter!

One of the downsides of the help docs is while there are lots of good tips, it’s hard to remember where to find them. But the search isn’t too bad. That’s how I found it again. Of course, sometimes you have to be able to remember the right search terms in order to find what you are looking for :wink:

Yes that’s always the case: You have to know what to search for. And I’m still suffering from a lot of mental interference due to 20+ years of working with FileMaker.

But Knack is looking better and better. Thanks again.

This might help too: