Can't crack a relationship

[FrancoFranco]

Hi all,

I've got a problem I've been struggling with for a few days now and I'm hoping someone here can help. Apologies if this is actually quite simple. I'm not a complete beginner with FM, but far from advanced.

Anyway, here's the problem: I have a parent table called Collections, and a child called Items. 1 Collection can have many Items. I also have a table called Requests. This table should allow a user to request a selection of items from a particular collection, so that these items can be physically delivered from storage to our premises. Each Request should be unique and retained as a permanent record, allowing us to have a history of when Requests were made and what Item they were for.

This is where my problem starts. I've attached an image showing how the relationships are set up between tables. My problem is that even though on the Request form I can bring up a checkbox set of items that changes depending which Collection has been selected (drawn from a value list), when I create a new request for that Collection the previous Request' selections are already checked, and if changed will effect the previous record. How should this relationship be set up to avoid this? I'd assumed I should use a join table at some point, but wherever I try this I'm told I can't have more than one relationship instance between any two tables...

To take it a step further, often a Collection will have so many Items that the checkbox set field isn't anywhere near big enough. Without a scroll bar option on checkbox sets, it seems to me that in the Requests form that field should in fact be a portal, which will allow a scroll bar. In this instant, how would this effect the above relationship problem?

Thanks again for the help, and taking the time to read this!

Franco

[comment]

when I create a new request for that Collection the previous Request' selections are already checked, and if changed will effect the previous record.

It sounds like the field where you are making the selections is in the Collections table, instead of being in the Requests table, where it belongs.

 

To take it a step further, often a Collection will have so many Items that the checkbox set field isn't anywhere near big enough. Without a scroll bar option on checkbox sets, it seems to me that in the Requests form that field should in fact be a portal, which will allow a scroll bar.

Indeed, that would seem like a more flexible solution - see if the attached helps:

Collection 2.fp7

Edited September 17, 2015 by comment

[OlgerDiekstra]

Seems to me that your requests TO shouldn't be related to collections, but to the items TO instead. After all, you're requesting items from a collection, you're not requesting a collection.

You can then have multiple requests per item, and maintain your history.

[comment]

Seems to me that your requests TO shouldn't be related to collections, but to the items TO instead.

What would be the match fields for such relationship?

[FrancoFranco]

Thanks, Comment. Your example was exactly what I needed. The selection field was in the Request table, but the relationships necessary were all wrong. Thanks so much. The end of a long headache!

 

[comment]

The selection field was in the Request table, but the relationships necessary were all wrong. 

Not sure if it matters now, but I cannot reproduce the problem, using the exact relationships shown in your original post:

 

Collection 1.fp7

[FrancoFranco]

Hi Comment,

It was due to the fact that I wanted a 4th table that would host the item picking list, so that in the 'Request' table I could show that picking list checkbox set in a portal. I abandoned this idea in the end, however, as some collections will potentially hold hundreds of items, so scrolling through them would take forever. I have now set it up so that clicking a 'select items' button in Requests launches a new dedicated window showing all the items. Those checked here will then appear in an edit box in Requests, which I can export as an email message for someone to action. 

Frustratingly, however, though I got this working correctly on Friday, I have today come into the office to find one of the scripts I'm using is no longer working. Perhaps you're able to shed light on this if I provide info?

Basically I have a script which when you hit the 'select items' button copies the request ID, opens a new window, goes to layout 'Picklist', creates a new record, enters the copied ID (which creates the link between that record and whichever collection is selected in 'Requests'), and should thus show the items connected with that collection via a value list. As I say, this was working fine on friday. Now, however, though the script runs as expected, copying and pasting the necessary ID, it fails to bring up the list of items. I've double-checked the relationships and they're as they were when I left work Friday, so I have no idea what's gone awry...

I've had similar experiences with FM before. Also things like the nature of fields changing inexplicably between uses of the database, i.e a field that's set to show a value list on one occasion with somehow change to an edit box on another, even though I've made no changes there and am the only person with admin rights for the db. Maddening stuff. Are there any general issues with FM that might cause this?

Cheers,

Franco

 

PS - Attached is the relationship graph as it stands:

[comment]

Are you sure you have only one file you're working on? Is there any kind of sharing going on?