9 Replies Latest reply on Aug 9, 2017 7:43 AM by ulisant15

    Creating a related record from a portal field

    ulisant15

      Hi everyone,

       

      I have an issue regarding the creation of related record based on a portal field. I've been lurking the Filemaker community boards for almost a year, but this solution I'm building for work has me stumped. I have been attempting to fix this for more than a week, and it's the last piece I need to complete my very first solution. I have attached a PDF image with the following information about my issue:

       

      1. A diagram of my desired workflow with the layouts in question.

      2. Text about the issue itself, the problem I aim to solve, the goal of it's function, and a description of the scripting I attempted to solve the problem

      3. The underlying relationships between the tables in question

       

      Any feedback is well appreciated,

       

      Thanks!

        • 1. Re: Creating a related record from a portal field
          BruceRobertson

          This would seem like one important change.

          graphMod.png

          1 of 1 people found this helpful
          • 2. Re: Creating a related record from a portal field
            ulisant15

            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!

            • 3. Re: Creating a related record from a portal field
              philmodjunk

              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.

              1 of 1 people found this helpful
              • 4. Re: Creating a related record from a portal field
                BruceRobertson

                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?

                • 5. Re: Creating a related record from a portal field
                  ulisant15

                  Thanks! I'll look into it and see where I can go from there!

                  • 6. Re: Creating a related record from a portal field
                    ulisant15

                    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).

                    • 7. Re: Creating a related record from a portal field
                      BruceRobertson
                      The data structure is there

                      No, it isn't actually. That is what your detailed explanation makes clear. You need a join table.

                      More shortly.

                      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.

                      • 8. Re: Creating a related record from a portal field
                        BruceRobertson

                        I think you're going to need something more like this at minimum.

                        But there are going to be other details you will need to keep track of.

                         

                        Patients

                        SampleGroups

                        Locations

                        SampleGroup_Locations (join table)

                         

                        LabResultStorage.png

                        • 9. Re: Creating a related record from a portal field
                          ulisant15

                          Thanks for pointing me in the right direction Bruce! I'll go ahead and make the necessary adjustments and see how it goes.