1 2 Previous Next 17 Replies Latest reply on Dec 28, 2015 6:55 PM by arby

    Table linking

    Gerrly

      This is a novice question, but I'm not sure what I'm doing wrong.  Essentially I have 4 tables and a couple of portals, but one table isn't making the connection.  Here's the problem:

       

      1. Here's the first table (Probands). The portal displays data from TO: Proband-Variants. Relationship is such that if I create or delete in Probands, it will do likewise in Proband-Variants.  They are linked by Proband ID. This all works, but I'm mentioning it because I have a hunch that the portal should actually be directed to the next table I'll talk about (Published Variants).

      Screen Shot 1.png

      2. Here's a screenshot of part of the Proband-Variants table/layout that shows the previously shown portal does in fact create data properly.

      Screen Shot 2.png

      3. Here's the problem. I want this data in Proband-Variants (above) to create a record in the Published Variants table. However, when I click that View/Edit Publication Data button (show related record script), it doesn't work because nothing was created in the Published Variants table. Here is a view of that table/layout so you can see that the Gene, coordinates, c. (nucleotide change), and p. (amino acid change) are where I want the data from Proband-Variants to be created from. (The portal in Published Variants links to Publication Data table.)

      Screen Shot 3.png

      Here's the relationship info (I don't want to delete in published variants).

      Screen Shot 4.png

      Screen Shot 5.png

      Thank you in advance.

        • 1. Re: Table linking
          Extensitech

          Gerrly wrote:

           

          ...Relationship is such that if I create or delete in Probands, it will do likewise in Proband-Variants.  \

          I believe (though I'm by no means positive) that this is the root of your misunderstanding. The "delete" checkbox in a relationship does, indeed, delete one when the other is deleted. The "create" checkbox allows creation; it doesn't do it for you.

           

          When you add records in the portal, you're doing so by adding data. FM doesn't add your portal rows for you.

           

          Likewise if, in your variants portal, the user (or you via script), were to set a value (just about any value, really) into published variants, that would create a record in published variants if it didn't already exist.

           

          Does this help? Was this your challenge?

           

          Chris Cain

          Extensitech

          • 2. Re: Table linking
            Gerrly

            Extensitech wrote:

             

            Likewise if, in your variants portal, the user (or you via script), were to set a value (just about any value, really) into published variants, that would create a record in published variants if it didn't already exist.

             

            Does this help? Was this your challenge?

             

            I don't think so, unless I'm misunderstanding you. I want data from Proband-Variants TO to be created in Published Variants TO.  There is no portal linking the two of those tables.

            From here:

            Screen Shot 2.png

            To here:

            Screen Shot 3.png

            I'm sorry if you understood this and answered appropriately and I'm a dunce.

            • 3. Re: Table linking
              Gerrly

              So essentially when I create a portal row in Probands (first image), I want the data from there to be created in Proband-Variants table (it does) and Published Variants simultaneously. That make sense? I don't think I have it linked properly to do this.

              • 4. Re: Table linking
                Extensitech

                Meh. I'm pretty sure I'm missing stuff, too. No worries.

                 

                If you want those fields to be set in published, you'll need to set them. You can put published variant fields in your variants portal, and have the user fill them out there.

                 

                If you want them to fill out based on what's in Proband, you'll have the usual decisions: Should published always show what's currently in probands? If so, show the probands field on the published layout, and/or calculate fields in published that are equal to the corresponding fields in probands. Should published start out with values based on proband's values? If so, use auto-entry or lookup. Should published be entered directly by the user, and not entered in Probands? Then show the published fields and make them editable by the user, and don't replicate them in Probands.

                 

                As for the focus of your portal: the difference between basing your portal on variants vs published is mainly that you'll see one row for each record in whichever table is the focused. Where that would make a difference is if there are variants with more or less than one published record related. If your focus is variants, you'll see one row for every variant, and any published fields you show will be for the first published record related to the variant on that row. If your focus is published, you'll see separate rows for each published, but a variant with no published wouldn't show, and nor would a published with no variant.

                 

                I'm kind of rambling here, and not sure I'm getting to your key point. (I'm actually coming down with something and my head's a bit foggy today.) However, the main thing is that if you put your published fields on your portal, you can add records via your portal. You have it linked properly, it just that you're not actually setting any fields in published, so no record is being created. Put a published field on your portal and try it, and I think you'll see what I mean.

                 

                Chris Cain

                Extensitech

                • 5. Re: Table linking
                  BruceRobertson

                  So essentially when I create a portal row in Probands (first image), I want the data from there to be created in Proband-Variants table (it does) and Published Variants simultaneously. That make sense? I don't think I have it linked properly to do this.

                  Make sense? No.

                  I think you need to step back and re-examine your structure. There should be no need to duplicate data in the way you describe.

                  Can you provide a clone, or further description of all the tables involved?

                  • 6. Re: Table linking
                    Gerrly

                    BruceRobertson wrote:


                    Make sense? No.

                    I think you need to step back and re-examine your structure. There should be no need to duplicate data in the way you describe.

                    Can you provide a clone, or further description of all the tables involved?

                    Huh? How can you suggest to me what I need or don't need?

                     

                    The database is variant based. So, Proband-Variant is central. The Proband table displays data from Prob-Var in the portal. Obvious. When data is created in the prob-var table, I want that to also go in a new record in the published variants table.

                     

                    Therefore:

                    Each portal row in Probands = new record in Proband-Variants = a new record in Published variants

                     

                    Published Variants should not be related to Proband ID at all. It is variant based, as you can see by the relationship.

                     

                    Extensitech: Thanks for your reply.

                    • 7. Re: Table linking
                      Gerrly

                      Also, what is a clone?

                      • 8. Re: Table linking
                        BruceRobertson

                        If you look under File:Save a Copy As, you will see that one of your choices is clone.

                        A FileMaker clone file is one that saves only the structure and has zero records in any of the data tables.

                        Clones are handy for a number of reasons.

                        In this case, it could conceivably allow us to look at your data structure without seeing any of your data.

                        • 9. Re: Table linking
                          Gerrly

                          Oh okay. Well I have no data in this yet, except for sample data.

                           

                          FYI: There are a ton of extraneous fields and a few extra tables because I'm adapting one of my databases for someone else's need.

                          • 10. Re: Table linking
                            BruceRobertson
                            Huh? How can you suggest to me what I need or don't need?

                            Because you have presented a classic form of a data structure problem.

                            • 11. Re: Table linking
                              Extensitech

                              BruceRobertson has some valid concerns, some of which I alluded to in my earlier paragraph beginning "If you want them to fill out based on what's in Proband, you'll have the usual decisions: ..."

                               

                              If there are values in two or three of these tables that should always be the same (and it sounds like there may be), then you'll want to be sure you actually store them in only one of the tables, and just reference them from everywhere else. A good data structure won't require constantly making sure that multiple versions of the same data are kept "in synch". If the values start out the same, but may differ later, then it makes sense to copy initial values (with auto-entries), but don't fall into the trap of having to constantly copy your data back and forth when a good structure will allow you to store once, reference many times.

                               

                              Bruce can sometimes "push back" pretty hard in his posts, but he does it constructively and with good reasons. I can tell you from experience that you'll do well to consider what he points out, even if he ruffles your feathers a bit. As for "How can you suggest to me what I need or don't need?", the answer is a lot of experience, and a lot of cleaning up after developers who've overlooked key principles. I can testify that Bruce has both.

                               

                              Posting a clone (or just a copy of your files if the data's test data and/or not sensitive) will allow us to spot if you're about to paint yourself into a corner that could drive you crazy and make your life difficult for months or years to come.

                               

                              Chris Cain

                              Extensitech

                              • 12. Re: Table linking
                                Gerrly

                                Extensitech wrote:

                                 

                                If there are values in two or three of these tables that should always be the same (and it sounds like there may be), then you'll want to be sure you actually store them in only one of the tables, and just reference them from everywhere else. A good data structure won't require constantly making sure

                                Yes! This exactly. I just want to store the data in Proband-Variants. In the past, I typically made a serial number ID to link tables. This database (reworked from one I use) is actually for someone else and I don't really know if that will work.  Their data is coming from excel spreadsheets.

                                • 13. Re: Table linking
                                  Extensitech

                                  It would help to have a definition of terms.

                                   

                                  A "Proband" is a... what? Is it a person? Is it a test for an anonymous person (in which case there could legitimately be two records for the same person)?

                                   

                                  A "Variant" is a... genetic variant discovered in testing?

                                   

                                  A "Published Variant" is a ... record that you published the findings? It sounds as though all your variants have a published record, so this seems unlikely.

                                   

                                  Also... is it true that one Proband can have many Variants, and one Variant can have many Published?

                                   

                                  Just trying to get the lay of the land.

                                   

                                  As I mentioned, I'm a bit under the weather so I may not be around much more today, but I think these answers will help Bruce, as well.

                                   

                                  Chris Cain

                                  Extensitech

                                  • 14. Re: Table linking
                                    BruceRobertson

                                    I note:

                                    Initial Report and Final Report have exactly the same fields.

                                    So eliminate one of those and just call it Reports.

                                    I note also that one of the relationships is messed up.

                                    I have questions about the publications part.

                                     

                                    published_variants.png

                                    1 2 Previous Next