1 2 3 Previous Next 40 Replies Latest reply on Dec 12, 2011 12:40 PM by KMG

    Help linking two files together



      Help linking two files together



      Hello I am very new at using FMP; I have only been using it for a few months. I am working to create a database for a large number (thousands) of samples. I have set the following up; one file was created to house the background of each sample, which includes fields such as picture, size, description, etc. Then second file I created was a table that is used to enter in analysis for each sample. What I would like to be able to do is:

      1) Have the “initial sample #” form the background file show up in the analysis file.

      2) Be able to have multiple analysis records for the same sample # (i.e.: XY00-12 may need two records from two different types of analysis). The trouble is I don’t want to the multiple records to show up again in the original background file.

      Is this at all possible with the manage database option avalible in the software, or will I have to create some sort of script to do this? Initially, I have just imported the “record #” from the background file; however it would be great if this would happen automatically and for the analysis file to be able to check to see if additional files have been added to the background file… Any advice would be greatly appreciated!!

        • 1. Re: Help linking two files together

          I suggest using a single file with two tables and you can use Manage | Database  to set this up. Use the Tables tab to create and name the two tables. Use the fields tab to define fields for each. Use the Relationships tab to link them in a relationship.

          We'll call those two tables Samples, SampleAnalysis.

          In Samples, define a number field, __pk_SampleID and use field options to set it up as an auto-entered serial number.

          In SampleAnalysis, define a number field, _fk_SampleID and DO NOT set it up as an auto-etnered serial number.

          Click the Relationships tab and drag from one sampleID field to the other to link them in a relationship.

          If you double click this relationship line and enable "allow creation of records via this relationship" for SampleData, you can place a portal to SampleAnalysis on your Samples layout and use it to create as many SampleAnalysis records as you need for a given sample. This is only one of several options--much depends one what form your data in a SampleAnalysis will take. You can also create SampleAnalysis records on a layout based on the SampleAnalysis table and select the Matching Sample record in a drop down list or pop up menu.

          If you have a different sample identification label that you need for other purposes such as the "XY00-12" example you gave, define a text field in Samples for that purpose. Given the above relationship, you can include this field and any others you need from Samples on a layout based on SampleAnalysis.

          • 2. Re: Help linking two files together

            Thank you, great. The files are automatically being updated in each table, however in the analysis table under the view as table view, is there a way to have the two analysis ID show up as two separate record but not change(i.e. not add) the additional analysis ID number to the original database?

            • 3. Re: Help linking two files together

              A table consists of records, not files. So I assume the records are automatically being updated in each table or the tables are being updated in each file. Wink

              is there a way to have the two analysis ID show up as two separate record but not change(i.e. not add) the additional analysis ID number to the original database?

              I'm afraid that I don't understand the question. Can you give an example?

              • 4. Re: Help linking two files together

                I am sorry, I miss spoke, you are correct the record in each table are updating automatically. Thank you!

                However what I would like to have happen is when a new sample id number is entered into the “sample table”, I would like it to automatically show up in the “sample analysis table” (which it does!). However what I would then like to have happen is for the multiple records to be created using the same sample id number only in the “sample analysis table”, and not automatically show up again in the “sample table”.

                For example, I enter in SP95-1-001 for the sample Id number in the “sample table,” it then automatically shows up in the “sample analysis table.”  Next I would like to create a new/duplicate record using the same SP95-1-001 number in the “sample analysis table” for the sample id number, however I do NOT want this duplicate SP95-1-001 record to show up/be automatically update into the in the original “sample table.”

                I hope this clarifies things more. Please let me know if it still needs more explanations. Thanks again for all your help!

                • 5. Re: Help linking two files together

                  I don't think we are quite on the same page yet.

                  I wouldn't use SP95-1-001 as the sample ID that links these two tables. If you read my initial post again, I recommended using an auto-entered serial number field for this purpose as FileMaker can add this value automatically each time you create a new record in the Samples table For such a "Meaning added" ID code, define a text field only in the Samples table so you can enter it once for each sample.

                  Since this is now a value entered once in the Samples table, you do not have to ever enter it into the SampleAnalysis Table. On any layout that is based on the SampleAnalysis table, you can add this extra field from the Samples table to your layout and you'll see it listed on your layout (can be put in the header or in other layout parts instead of the body) due to the relationship linking your two tables.

                  • 6. Re: Help linking two files together


                    I understand the concept of the auto-enter serial number, however the sample id I am working with were sample id names that were automatically imported by folder using these names already assigned to each file. I have been able to link the tables together so that when new samples are imported to the SampleTable they automatically show up in the SampleAnalysis Table.

                    I also get the idea of just having two section in each sample analysis layout for the audience to view two sets of analytical data, however is it possible NOT have the this double layout show up on each records ( see image titled, form view analysis table)? And it looks like from the why I have set it up it would be pulling the same data from the same table am I going to have to create a new table for those analysis that have duplicate analyses(see image titled, layout analysis table)? I have included so images maybe this will clarify a few things…

                    • 7. Re: Help linking two files together
                      /files/b317bf50bd/Form_View_Analysis_Table.jpg 628x259
                      • 8. Re: Help linking two files together
                        /files/952ef3186c/Layout_Analysis_Table.jpg 627x259
                        • 9. Re: Help linking two files together

                          Ok, I follow your logic for using the externally produced SampleID field. I still recommend implementing the other field for linking your two tables, but let's shelve that for now as we'd have to set up some post import scripting to handle that.

                          I don't see any portal here. I recommended a portal because this is the simplest way to set this up for new users.

                          If you are going to navigate directly to the sample analysis layout, then you will need to use some method that limits the found set of records to just the sample you want to examine.

                          1. From the Sample layout, you can find the sample you want and then click a button that either performs a find or uses the Go TO Related records step to switch to the sample analyis table and display only those records with a matching ID value.
                          2. From the Sample Analysis layout we can expose the header (assuming you stay with table view), and put controls in the header that enable you to select a sample and then a script finds only those sample analysis records for the sample you select.
                          3. You can even do this manually, by entering find mode, typing or pasting the anlysis ID into that field on the sample analysis layout and then you perform the find.


                          Let me know which method you'd like to implement and I'll respond with a more detailed description of that specific method.

                          With regards to IDs: I still recommend implementing the serial ID key field to link these tables because it's conceivable that your externally supplied ID could be subject to a data entry error. It's also possible that the powers that be could decree. "we will now ID these samples with this new ID code...." Dealing with both issues can be tricky if the field is used to link tables in relationships. Exactly matching changes have to be made in both tables or the link is broken. If the field is just a data field in the Samples table, making such corrections/modifications is very simple.

                          The way to implement this is to temporarily link the two tables by the external sampleID, but use a script immediately after importing the data which uses this link to update the Sample Analysis records with the correct matching serial number. But as I have already stated, that's something you can think about implementing later after you get your basic design working here.

                          • 10. Re: Help linking two files together


                            As far as the sample Id goes I think I understand now that it may be more beneficial linking using an auto id and then just hiding that column in the table view.

                            I think option number one looks the best for what I am trying to do. However what will happen if you wanted to search for all sample that lets say is greater the 12, O Wt%? Would you still be able to do that using this using this option?

                            • 11. Re: Help linking two files together

                              Assuming this relationship (works for either ID field we've discussed):

                              a single script step can bring up any existing related records on the SampleAnalysis layout:

                              Go To Related Record [Show only related records; From table: SampleAnalysis ; Using layout: "SampleAnalysis" (SampleAnalysis)]

                              A longer script can do the same by performing a find, but also opens the door to specifying additional criteria as well:

                              Set Variable [$ID ; value: Samples::SampleID ]
                              Go To Layout ["Sample Analysis" (SampleAnalysis)]
                              Enter Find mode [] // clear the pause check box
                              Set field [SampleAnalysis::SampleID ; $ID ]
                              Set Error Capture [on]
                              Perform Find []

                              You can add a ann additional step Sort [Restore ; no dialog] if you want to make sure that the analysis records are sorted in a specific order.

                              To include additional criteria, can be done by adding additioan set field steps to the above scripts. You first have to decide how that criteria will be selected by the user. If it's additiona criteria that will always be used, you can just add a set field step for each and use it to enter the literal value or and expression such as "< 12".

                              If it's criteria the user may choose to specify, you can add some fields with global storage specified to your Samples layout. The user enters/selects values in these fields then clicks the butto to run the script. The script then uses the criteria entered in these fields to add more criteria in the find request(s).

                              Say you want all analysis records for the current sample where you have a value greater than 12 in the Wt% field:

                              Set Variable [$ID ; value: Samples::SampleID ]
                              Go To Layout ["Sample Analysis" (SampleAnalysis)]
                              Enter Find mode [] // clear the pause check box
                              Set field [SampleAnalysis::SampleID ; $ID ]
                              Set field [SampleAnalysis::Wt% ; ">" & Samples::gWt%]
                              Set Error Capture [on]
                              Perform Find []

                              The user would enter 12 into the Samples::gWt% field before running the script. This field must be a field where you have selected global storage as its field option on the storage tab or this won't work. Global fields, by the way, can be defined in any table not just samples, and this will still work. Often, such global fields are all defined in a single "globals" table to make them a bit easier to keep track of.

                              • 12. Re: Help linking two files together

                                Thanks again for all your help. Those scripts seem to work just fine, however I found this script for removing duplicate records in the database.

                                Show All Records
                                Sort Records [Restore, No Dialog] Sort by Client ID in Ascending order
                                Go to Record/Request/Page [First]
                                Replace Field Contents [No dialog, 'Mark', " "]
                                Set Field['Global','ClientID']
                                Go to Record/Request/Page [Next, Exit after last]
                                If ['Global = ClientID']
                                Set Field ['Mark', '"X"']
                                Set Field ['Global','ClientID']
                                End If
                                End Loop

                                Perform Find [Restore] Find records when Mark = X

                                Delete All Records


                                 The problem with this is that it deletes the record in both of the layouts. Is there any way to modify the script such that the duplicate records are only deleted in the background layout and not in the analysis layout, or as you said before should I just create to separate portals for each duplicate sample in the analysis layout? THANKS

                                • 13. Re: Help linking two files together

                                  The script does not delete anything. It marks duplicate records and then performs a find to pull them up in a found set. If you then delete this found set, you delete it from the record's table, not a layout. Once deleted from the table, it's permanently gone and won't be visible on any layout.

                                  Why do you need to delete duplicate values? (There may be a better way.)

                                  Do you need to delete them permanently or just hide them from view?

                                  Where do the duplicates come from? (If they are present in the imported data, we can filter out the duplicates during import)

                                  • 14. Re: Help linking two files together

                                     I need to have duplicate values/records in the analysis layout because there are multiple analysis for some records.

                                    I would like to have the duplicates that show up in the background layout be deleted permanently so the database does not take up more space than it has too.

                                    The duplicates are coming from having to enter in two records (or duplicate records) in the analysis layout that are both used for data in that layout. However the duplicate that shows up in the background layout will not be used for anything, is a distraction and should be deleted. They would not be present during the importing process, they would be created for the several of the records that have multiple analysis in the analysis layout only.

                                    1 2 3 Previous Next