12 Replies Latest reply on Jan 26, 2011 5:58 AM by Sorbsbuster

    Values from another table field!

    AmreGular

      Title

      Values from another table field!

      Post

      Here is the situation: I have 2 tables. (table1 and table2)

      table1 have 2 fields (id and description) and in table 2, a field gets values from table1.

      there is no issue while i getting values from another table. but for example in future (ex. after 1000 records entered) when i want to update table1 "description" field values, i want to also change all entered records in table2, in a relation with "id" field. is it possible with filemaker?

        • 1. Re: Values from another table field!
          Sorbsbuster

          If the Table 2 contains only the field ID, and uses that to 'see' the description from Table 1 (ie: the description field shown is by relationship to Table 1), then you don't have to do anything.  As soon as you change the description in Table 1 then every record in Table 2 with that ID will now display the new description.

          Note that this is not always desireable - think of invoices having their prices all changed retrospectively when you update the price list.

          If the Table 2 has two fields, ID and Description, where the Description is data copied from Table 1 by look-up, then you would find all the records in Table 2, click into the ID field, and choose Records | Relookup Field Contents.  Note: a 're-lookup' cannot be undone.

          • 2. Re: Values from another table field!
            AmreGular

            thank you for qucik response. but i dont know how to do it (your first paragraph). how to made "see the description from table 1".

            i made it work in a diffrent way. i created a second field which is a caltulation of the related field and returns the ID's description.

            • 3. Re: Values from another table field!
              Sorbsbuster

              Okay - then it will work the same way - but you didn't need to do that.

              On the Table 2 layout, add a new field.  At the top of the Specify Field dialogue box select the Table 1 (I assume you have only the 2 Table Occurances).  Select the field 'Description'.  You will see that it displays exactly the same data as your calculation - you could remove it now.

              If you go back into Table 1 now and change the description of that ID you will see that the description in Table 2 has changed for every record with that ID.

              • 4. Re: Values from another table field!
                AmreGular

                table1 =

                id description
                1 ABC
                2 DEF
                3 GHI

                 table2

                id field (from table1) (by drop-down list)
                1 ABC (it stores here correctly, but when changing ABC in table1 to XYZ, it does not affect/change this value normally)
                2  
                3  

                i can make it using php/mysql by calling php script in server side but im kinda stranger to filemaker. i guess i miss something and need a bridge to see this in same screen. (i did it by addind a calculating field in table2 which returns the description of id. but i have to hide the other field like masking.) is there another way, or i did it right?

                 

                • 5. Re: Values from another table field!
                  AmreGular

                  thank you for second reply. while i was wiriting my answer you already post your second :) ty

                  • 6. Re: Values from another table field!
                    Sorbsbuster

                    What version of FM are you using?

                    • 7. Re: Values from another table field!
                      AmreGular

                      filemaker pro advanced 11

                      • 8. Re: Values from another table field!
                        Sorbsbuster

                        You should have the tables linked by a relationship by ID.  You  should really get into the good practice of NOT linking a base table to a  base table, but create another 'Table Occurance' of the second table  and link to that.

                        Meanwhile lets assume that you have linked the  two tables directly:

                        Table1 -- by ID --< Table2

                        Table 2  Layout should have two fields:

                        Table1:ID and Table2::Description

                        Note  that the Description field has been picked directly from Table2, using  the relationship linked by ID.

                        This will produce the same effect  as creating a second calculation field in Table 2:

                        Description =  Table2::Description

                        --------------

                        As you change the value  ABC in Table 1 is will change the value displayed in Table 2

                        • 9. Re: Values from another table field!
                          Sorbsbuster

                          You would normally be recommended to have your relationship graph something like this:

                          TABLE1  <no direct relationships at this stage>

                          TABLE2  ---- linked by ID  --<  table1ByID

                          ...where 'table1ByID' is another Table Occurance of Table 1.  (Click TABLE1 in the Relationship graph and duplicate it with the green double-plus button, 3rd from left on the bottom toolbar.  Rename that TO as 'table1ByID' and then define a link from the TO 'TABLE2' to the TO 'table1ByID' by linking the two ID fields.)

                          The field you drop on to the TABLE2 layout would be called from the relationship 'table1ByID', and would be formally named as 'table1ByID::Description'.

                          • 10. Re: Values from another table field!
                            AmreGular

                            can you send me fp7 file which contains this explanation? i guess it will be easiest way for me and i will be grateful :) 

                            • 11. Re: Values from another table field!
                              Sorbsbuster

                              You could e-mail me your file to sorbsbuster at hot mail dot com (I have typed it that way to avoid spam - I would suggest you remove your e-mail address from your previous post...)

                              • 12. Re: Values from another table field!
                                Sorbsbuster

                                You needed to create a relationship between the two tables.  You had set the Table 2 ID field with a pop-up list of all the descriptions available in Table 1.  You were simply setting the contents of that ID field to be text from any of the description fields in Table 1.

                                I created another Table Occurance of the table and linked it by ID, and showed the Description field from it on the layout.

                                I changed the pop-up to offer a list of all IDs available from Table 1.