1 2 3 4 Previous Next 45 Replies Latest reply on Nov 18, 2013 10:38 AM by Xantrias

    Auto-Enter Id from related table based on matching field text

    Xantrias

      Title

      Auto-Enter Id from related table based on matching field text

      Post

           Hi guys

           I've tried to find a solution via the search option, but I couldn't find any help. Maybe I entered the wrong search string...

           So I have two related tables as follows:

           - Documents: Id (auto serial number), DocumentId, DocumentName
           - Labels: Id (auto serial number), DocumentsIdFk, Labels_DocumentId, Labels_DocumentName

           All Labels_Documents are also in the table Documents. So there has to be a match between those.

           I wanted to write a script for the table "Labels" that should do the following:

             
      1.           Go to first record in table "Labels"
      2.      
      3.           Loop
      4.      
      5.           Check if DocumentId=Labels_DocumentId
      6.      
      7.           If true, auto enter the Id from the table "Documents" into "DocumentsIdFk"
      8.      
      9.           Go to next record in table Documents, exit after last.
      10.      
      11.           End Loop

           Something like that. How do I get the script to get the current Id value from the matching field on the table "Documents"?

           Thanks in advance for any help I can get!

           Greetings Xantrias

        • 1. Re: Auto-Enter Id from related table based on matching field text
          philmodjunk

               To start, you can define this relationship:

               Documents::Documentid = Labels::Labels_Documentid

               Then, instead of a looping script, all you need is a replace field contents script step with the calculation option:


               Replace Field Contents [no dialog; Labels::DocumentsidFk; Documents::id ]

               If you have an existing relationship between the two tables with different match fields, you may need to use a new Tutorial: What are Table Occurrences? of Documents to do this without altering existing relationships.

                

          • 2. Re: Auto-Enter Id from related table based on matching field text
            Xantrias

                 Thank you very much for your input once again. I don't seem to grasp entirely the difference between relating to tables via Id = IdFk... I thought that linking the Ids would also work.

                 Greetings

            • 3. Re: Auto-Enter Id from related table based on matching field text
              philmodjunk

                   Both your id fields are auto-entered serial numbers so there is nothing in place here to link the id in one table to the id of the other as the values will not, except by sheer accident, match.

                   Normally you match the pk of one table to the fk of another:

                   ParentTable::__pkParentTableID = ChildTable::_fkParentTableID

                   where the __pk field is defined to auto-enter either a serial number or Get (UUID ). And the _fk field is a simple data field of the same data type as the __pk field. (Usually, auto-entered serial numbers are entered into number fields and UUID are entered into text fields.)

                   This establishes a relationship where one record in ParentTable can link to any number of records in ChildTable by putting a value in the _fk field of each linked child record that is exactly the same as the value in the unique value in the __pk field of the parent record.

              • 4. Re: Auto-Enter Id from related table based on matching field text
                Xantrias

                     Ok, but I HAD linked a ParentTableID to a ChildTableIdFk. It's just that my ChildTable also has a auto serial Id, but I didn't link anything to it.

                     Let me check if I've understood your explanation:

                     Let's assume I have a ParentTable with contacts and a ChildTable with phone numbers and that I've linked the ParentTableId (auto-serial) to the ChildTableIdFk (no auto-serial).

                     If a contact with the Id=21 has 5 phone numbers, I can link them to it by assigning the same value (21) to each of the phone number?

                     I guess tutorials, youtube videos and manuals of instructions can only get me so far...

                     Thank you again.

                • 5. Re: Auto-Enter Id from related table based on matching field text
                  philmodjunk

                       You are correct and there are ways to set up FileMaker so that the matching value is entered for you.

                       Example:

                       If you set up a portal to that table of phone numbers and you enable "allow creation of records via this relationship" in the relationship settings, you can enter data directly in the portal and the pk value is automatically copied into the define fk match field of the new record thus created in the portal's table.

                  • 6. Re: Auto-Enter Id from related table based on matching field text
                    Xantrias

                         I have another, more specific question, hope you or anyone else can give me some ideas.

                         That's the scenario:

                         I have

                           
                    •           a list of 36 products
                    •      
                    •           a list of 180 materials
                    •      
                    •           a list of 1800 documents
                    •      
                    •           a list of 110 equipments
                    •      
                    •           a list of 8 testing methods

                         What I need is to link everything in a complex way, so that I get all the documentation to each product according to this process:

                         A product has documentation of its own, and is made of materials, which have documentation as well. A product is made with some kind of equipment, which has also documentation, and the product is tested in specific ways, according to the documentation.

                         What would be a good way to link them all together, so that for each product a list is shown, where u can see every documentation of every step in that process?

                         I already have the lists on an excel-sheet, so it would be about importing data and linking it one to another.

                         I hope I've made myself abundantly clear. If not, just tell me and I will try to explain it further.

                         Thank you very much

                    • 7. Re: Auto-Enter Id from related table based on matching field text
                      philmodjunk

                           Can the same document be linked to more than one Product, material, or equipment item?

                           Am I correct that this is in support of a manufacturing process and you need to manage the specifications for each product manufactured and each material used in the manufacturing process?

                           If so, this is familiar territory as I have previously managed such specs in a relational database for a manufacturing plant.

                      • 8. Re: Auto-Enter Id from related table based on matching field text
                        Xantrias

                             Yes it can. And the same equipment is also used for more than one product, consequently, the same type of material can be used in more than one product, but that's kinda obvious. A testing method can also be applied to various products.


                             And you are assuming correctly. At the end, I need to have a "sheet" or let's say layout, where every equipment, material and document is shown that is related to the product itself or to the manufacturing process. There is actually no need to list the equipment or material, but the documents related to them.

                        • 9. Re: Auto-Enter Id from related table based on matching field text
                          philmodjunk

                               What you have is the need to implement a number of many to many relationships. One of the most basic, is to set up a Bill of Materials (BOM) for each item that you manufacture in order to list each material used to manufacture that item along with the quantities of each material used to manufacture one product or one unit of product. The BOM serves as a "join" table linking any given product to many different materials and any given material to many different products.

                               I realize that you don't want to "list the equipment or material" but without that relationship, you can't link your documents to different materials and yet produce your combined set of documents for all materials used to manufacture a given product.

                               So your initial relationships to which many more would be added are:

                               Products------<BOM>-------Materials
                               Products::__pkProductID = BOM::_fkProductID
                               Materials::__pkMaterialID = BOM::_fkMaterialID

                               And in many such systems, Products and Materials are really the same table. In FileMaker, we'd do that by making them two Tutorial: What are Table Occurrences? with the same data source table and __pkProductID and __pkMaterialID then become the same field from the same table.

                               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                               To extend this concept, you can set up a similar join table to link a product to a list of all the equipment used to manufacture it. And a single table of Documents can, if needed be linked via join tables to table occurrences of products, equipment and materials.

                          • 10. Re: Auto-Enter Id from related table based on matching field text
                            Xantrias

                                 I have here a screenshot of my relationship view. I don't know if that's gonna work out, but then again, I may be lucky after all. =)

                                 Red are the table occurences for all my products, therefore named ProdPool.
                                 Green are those for all my equipments, hence EQ_Pool.
                                 etc etc.

                                 Gray are the table occurences which don't have any primary values and which are only there to connect my pools to each other, therefore x_blabla. They only store values from other tables, normally as IdFks.
                                 Around the gray table occurences you will find those two/three pools which need to be connected. I thought it best to create multiple table occurences.


                                 I hope it's a little clearer now. It's obviously not finished yet, but it may help you understand, what I have and what I need. And of course, point out any mistake you can find.

                                 Best regards
                                 Xantrias
                                  

                            • 11. Re: Auto-Enter Id from related table based on matching field text
                              Xantrias

                                   FYI, I didn't see your post before posting mine.

                                    

                              • 12. Re: Auto-Enter Id from related table based on matching field text
                                Xantrias

                                     Hello Guys

                                     I've started to import my data to the database via Excel-Files, which worked very well.

                                     As you can see in the screenshot, every QC_method (blue) there is one or many documents out of the DokPool (orange). I've made the connections via the grey table x_QCDok.
                                     Previously I've made a similar connection between the product (red) and the QC-method (blue) via the grey table x_ProdQC.

                                     What I want now is to being able base a layout on the ProdPool and show every related document via the QC_methods. But even though the grey connection tables are fine, something isn't quite working out. I've created this layout and made a portal to the occurence "DokPool QCDok". I've been able to create this portal, so I think some relations must be working, otherwise I couldn't have chosen the "DokPool QVDok" as the target for my portal. And even though I specify which fields of the occurence I want to have shown, there are no entries.

                                     Does anybody understand my problem (did I explain it well enough?) and has some useful input for me? That would be more than great!
                                     If you need more input from me, pls tell me so.

                                     Thank you and best regards
                                     Xantrias

                                • 13. Re: Auto-Enter Id from related table based on matching field text
                                  philmodjunk

                                       As you can see in the screenshot,

                                       Don't see any screen shot unless you are referring to the one you uploaded nearly a month ago...

                                  • 14. Re: Auto-Enter Id from related table based on matching field text
                                    Xantrias

                                         Yes, I was referring to that one. But I'll upload another one right now. This one is up to date, too.

                                    1 2 3 4 Previous Next