So, funny enough. The arrangement that you proposed was my original configuration for the relationship. However, I still couldn't get a script to discriminate between being able to view an existing value in the SampleGroupLocation table, and being able to create a new record in that table for an existing sample group with no related record. Remember that I am also trying to have a script that can autofill the SampleGroupID into a new record in the Location table.
I had a previous iteration with your proposed adjustment where I could manually enter the SampleGroupID in a new record in the location layout, and it would work well. I just want to have it automated for an easier user experience.
I'll change it back to your suggestion, but the second part of this issue is still there. Thank you!
1 of 1 people found this helpful
I suggest that you research "magicKey" as one approach. Basically, you can't use a one to many relationship to create new records unless you use a portal to do so and then only if you put the focus on the right portal row. MagicKey avoids a lot of issues that can arise with that approach.
Other scripts for creating records do it by setting variables to needed values, changing to a layout based on the related table where new record and a set of set field steps--that copy data from the variables into fields of the new records create your new record before changing back. That is a commonly used method, but, especially in pre 16 versions, can trip a lot of script triggers with ensuing complications that have to be handled if they are.
That's just a simple logical start. And Phil's suggestion is good.
But I doubt very much that you're ready to do any of that.
You don't even have a useable data structure.
What is the nature of the relationships between sample groups and locations?
Is one SampleGroup stored in one location?
Are other sample groups stored at that location?
Is one sample group stored at various locations?
Thanks! I'll look into it and see where I can go from there!
The data structure is there, it's just not very clear on the diagram because of the way it's represented. The logic goes:
A) one sample group is stored in one location
B) one location can have many sample groups in them
It seems like a simple one-to-many relationship until you get to your 3rd point, what happens when you have a sample group that is stored in more than one location? The work around I did for this was to break it down into more sample groups. Say you have 20 vials of a blood sample; however, you can't physically put it all into one location. You end up having to split it into 2 locations. 10 vials in the first location, and 10 vials in the second location. In this setup, you actually have 2 physical groups of samples that only differ in where they are stored. Thus, each of them should get 2 different separate sample group ID's because they differ in their storage.
Second, the location values you see in the layout are the attributes of the location. The location (the combination of the attributes on the layout) themselves are not unique values, and even in combination they cannot produce a concatenated primary key. Instead, I simply use a serial numbered LocationID as a primary key for the location table. In turn it leaves a one-to-one relationship between the SampleGroupID and the LocationID.
When I was planning I noticed there were cases where a large quantity of samples (100+vials) were stored in 3+ different locations. Instead of approaching the situation as a many-to-many relationship; I figured the one-to-one like this would be easier to maintain in the long run. If you have a more efficient way to handle this type of relationship, I'm more than willing to apply it since I'm still a newcomer to Filemaker.
I should also mention in this post, that I am using a pre-FMP16 version (FMP13).
The data structure is there
No, it isn't actually. That is what your detailed explanation makes clear. You need a join table.
Instead of approaching the situation as a many-to-many relationship; I figured the one-to-one like this would be easier to maintain in the long run.
Instead of actually modeling your data, you modeled something simple; something that does not represent your real world.
Oops. I don't think that's working.
Thanks for pointing me in the right direction Bruce! I'll go ahead and make the necessary adjustments and see how it goes.