Exclusive many to many form link

[Ed Bettencourt]

Is there a way to link to existing records in another form, but make each link exclusive to only one parent record? To make this clearer (hopefully) here’s my use case: I have a parent record which are wines. I have child records which are wine storage locations. These locations are pre-defined and limited since there are only so many locations. With a many to many link, I can associate different locations to different wines. Good so far. But ideally, I would like the child storage location records linked to ONLY one wine since I can’t store two wines in the same location. Kind of like a one to many link. In addition, when I drink a wine (Yay!), that location should now be made available so another wine can be linked to that location when I buy another one and put it there.

What I am basically looking for is a one to many record link, with the ability to link ONLY to existing records that aren’t already linked to another parent record. And then, as I de-link child records, those will be made available for linkage to other parent records. I know, theoretically this shouldn’t happen. But it does. This would be an added integrity feature/check.

I will be converting from Bento (tested the import in the demo version and it worked like a champ!). Bento seems similar to TF in that I can mistakenly link to a child record (wine location) that is already linked to another parent, so I’m not losing anything here moving to TF. Just was wondering if TF can, or maybe in the near future will, give me this added ability.

By the way, TF seems to be a great product. Easier and better than Bento in many ways – especially the iCloud sync. Wish I would have discovered TF earlier! Thanks.

[Brendan]

Hi Ed,

I think a solution to this problem would be for me to display a checkmark button just like I do for many-to-many links that let you select from a list of all available child records and then when you select a record, that would “steal” the record from wherever it was previously linked. But it would probably have to have the ability to filter for those records which are not already linked elsewhere too.

Thanks!

Brendan

[Ed Bettencourt]

I think that would do it Brendan. Pretty much just what I had envisioned. With the key being to give an option to filter the related records to only show the ones that are currently parentless so that you don’t accidentally steal/move a location to a different wine. Without this (and like I said, Bento doesn’t do this either), I would be prevented from assigning a used storage bin to a new bottle which has happened several times by mistake. This ends up causing a cascading inventory glitch affecting, over time, more than just the one location. Really hard to correct once discovered too.

I can see other similar use cases for this too. Thanks Brendan for taking the time to consider this.

[Author]

Posts