10 Replies Latest reply on Aug 11, 2016 2:22 PM by TonyDehnke

    Creating my first FM db - relating tables and connecting  data

    tommcat

      Hi everybody, I am new FM user and I have the following questions:

      1.

      I have 2 tables Contacts and Machines.  In Machine Browse Layout where I input all the info about every Machine I want to put a field Supplier Contact Id and Supplier Contact Name to show that this machine is at location of this Contact.

       

      Tried to make such fields in Machines and relate them with matching fields in Contacts but it's not working.

       

      2. I'd like to print records or make pdfs from Machines  in 2 language versions. I can make layout with 2 tabs - 1 tab for each language and fields labels will be describing fields in correct language.  The question is - can I automate some translations wherever I use not edit box but f.ex. lists of values? For example make a list of values in both languages then on 1st tab will show 1 version , on 2nd tab 2nd version?

       

      Thanks in advance

        • 1. Re: Creating my first FM db - relating tables and connecting  data
          philipHPG

          1). Be sure you have a Supplier Contact ID field in your Machines table. In your database relationship graph ensure the relationship between Machines and Contacts is based on Machines::Supplier Contact ID and Contacts::ID. (Contact Name should be a field in Contacts, not in Machines). If you have verified this and are still having problems, please send us a screenshot of your relationship graph.

           

          2). Yes, this is possible. This might help you: http://www.modularfilemaker.org/module/multilingual-valuelists/.

          1 of 1 people found this helpful
          • 2. Re: Creating my first FM db - relating tables and connecting  data
            tommcat

            Thanks Philip, I changed my relation because it was "from" Contact table "to" machines.  Now it look like this: fmtr.tiff

            Anyway in Layout where I input Machines data there is nothing in related fields: Supplier Id and Name.  I can input data manually in these fields but it makes no sense because they are not related nor "linked" with Contact table.

            Or maybe am I using wrong relation operator?

            • 3. Re: Creating my first FM db - relating tables and connecting  data
              philipHPG

              Unless I'm misunderstanding something, I suggest you do not have the Supplier name field in MaszynyTabela. Simply use the Supplier Id number field and use only that in a relationship with V_Kontakty::Id field. Once the relationship is working correctly, you'll be able to use V_Kontakty::Company to display the Supplier name.

              • 4. Re: Creating my first FM db - relating tables and connecting  data
                tommcat

                - I want to use Machine Form Layout to input data about Machines technical specs but also some other data like Contact where the machine is located. 

                - So while this input process I come to a field "Supplier Id" and then I don't want to check all my Contact to look for Id that I need.  I need something like search field where I start typing a Contact's name or Company name and the data  will be filled automatically from V_COntacts table

                 

                - just name field is enough at this point, ID can be helpful if more contacts have the same name

                • 5. Re: Creating my first FM db - relating tables and connecting  data
                  philipHPG

                  This is a common situation with FileMaker. You want to display fields from multiple tables (Machines and Contacts) on the same layout. You just need to be sure the relationship is set up correctly between Machines and Contacts and then you will be able to display fields from both table occurrences in the same layout (without having to create contact fields, such as Supplier Name, in Machines).

                   

                  I strongly suggest that you use an ID for Contacts [either an Auto-Enter Serial number or an Auto-Enter Calculation of Get ( UUID ) ] and use that as your primary key and the relationship between Machines and Contacts. As you said there could be duplicate names or one day a contact name could change, so using name as your primary key is not reliable.

                   

                  Obviously, you don't expect your users to know all the IDs and so, like you said, you want your users to be able to search by name. This can be done fairly easily using a drop-down or popup value list. Of course if you want them to be able to search by contact name or company name it becomes a little more complicated: you could use two drop-down or popup value lists, or use a picker window or use virtual value lists.

                  1 of 1 people found this helpful
                  • 6. Re: Creating my first FM db - relating tables and connecting  data
                    tommcat

                    Thanks Philip, I'll work on that

                    • 7. Re: Creating my first FM db - relating tables and connecting  data
                      philipHPG

                      If you have time, you might want to check out the FileMaker Training Series | FileMaker . There is a lot of good material there, and it is free!

                      • 8. Re: Creating my first FM db - relating tables and connecting  data
                        TonyDehnke

                        I had a hard time with this as well. I totally understood the concept of the keys and connecting the relationships.  What the Basic and Advanced guides are not great at explaining is the part about having to have that ID on the related layout, or referenced somewhere. I'm not sure of a great way to explain it, but I think with 3-4 pages of examples of tables and layouts that will and won't work for some basic 2 table and 3 table samples it could be better explained.  Right now with the training and the use of so many pre-made, pre-populated solutions FM Training kind of leapfrogs this very crucial thing to understand (I just started using FM a couple weeks ago).

                         

                        Also as some feedback for the FM team if they read these.  As a new user it really seems that primary keys and foreign keys should be added as a field type - like Text, Number, Calculation etc in the drop down on the bottom rate of the Database Field setup window - would save a bunch of time "formatting" keys when setting up a database.

                        • 9. Re: Creating my first FM db - relating tables and connecting  data
                          philipHPG

                          I agree that some training programs do better than others on the basics, TonyDehnke. Several training programs I've seen jump too quickly (in my opinion) into layouts and making things happen, without explaining some of the basic, underlying principles.

                           

                          I understand what you are saying about setting up Primary and Foreign Keys as field types, but I don't think it's completely practical - there isn't simply one type of key. You have your standard, serial number field which is used in many situations. However, in some cases it is preferable (even perhaps essential) to use a UUID (which may be text or, in some cases, numeric) for the key. And then there are cases where there is a multi-key. I think it is best for FileMaker to continue to allow the developer freedom to use the types of keys that are necessary for their situation.

                          • 10. Re: Creating my first FM db - relating tables and connecting  data
                            TonyDehnke

                            I can see the point about key's, that said to have a couple basic standards set for pK and fK, advanced developers would still easily have the ability to do UUID, and other methods as they do now pretty easily.. 

                             

                            Again this coming from someone who's been using FM for about 2 weeks now, with no programming background at all.