8 Replies Latest reply on May 1, 2014 10:19 AM by ZoltanOrban_1

    Updating a Single Row from Multiple Records

    ZoltanOrban_1

      Title

      Updating a Single Row from Multiple Records

      Post

           Hello,

            

           I am having issues to getting unique values from multiple records from  related tables.

           Table1:

           item#
           location
           qty

           Table2:

           item#
           qty_at_location1
           qty_at_location2
           qty_at_location3
           qty_at_location4
           qty_at_location5
           totalQty

            

           Relationship:

           Table1:item# = Table2:item#

           My question is: what is the easiest way to get the qty of each location from Table1 to Table2 for the same item to the right column? The total was very easy, but I am only getting the value of the first related record from Table1.

           Thanks for the help! 

            

            

        • 1. Re: Updating a Single Row from Multiple Records
          philmodjunk

               Option 1:

               qty_at_location1
               qty_at_location2
               qty_at_location3
               qty_at_location4
               qty_at_location5

               Could be calculation fields that use ExecuteSQl to refer to the values from table 1 for up to 5 different locations.

               Option 2:

               Delete those fields, you don't need them. Replace them with 1 row filtered portals to table 1. Set up a different filter expression on each portal to filter for only one location.

          • 2. Re: Updating a Single Row from Multiple Records
            ZoltanOrban_1

                 Thanks for your post!

                 Option 2 is not an option as need those fields.

                 Option 1:

                 I am running FM Pro 11A, and I don't think ExecuteSQL works. Although that would solve the issue.

                  

            • 3. Re: Updating a Single Row from Multiple Records
              philmodjunk

                   And why do you need those fields?

                   I need that info before I suggest an alternative to option 2 that will work with FileMaker 11. (Please let people know what version of FIleMaker you are using. It saves us from suggesting solutions that don't work in your version.)

              • 4. Re: Updating a Single Row from Multiple Records
                ZoltanOrban_1

                     Thanks again for your reply!

                     We have 10000+ items. So I don't see how it would work here. I also going to need to export Table2 in excel and show the qty in each location (if any) and the total. 

                • 5. Re: Updating a Single Row from Multiple Records
                  philmodjunk
                       

                            We have 10000+ items. So I don't see how it would work here.

                       It would work to display the data just fine.

                       

                            I also going to need to export Table2 in excel

                       But you'll need the fields for the export to excel.

                       I'm going to describe how to do this for two locations named "Alpha" and "Beta". I'll leave it up to you to repeat the steps a sufficient number of times to get each of your different fields.

                       Define a calculation field in Table 2 named constAlpha. Define it with this expression: "Alpha". Define a second field, constBeta as: "Beta". In both calculation fields, select "text" as the result type.

                       Define these relationships:

                       Table 2::Item# = Table2|Alpha::Item# AND
                       Table 2::constAlpha = Table2|Alpha::Location

                       Table 2::Item# = Table2|Beta::Item# AND
                       Table 2::constBeta = Table2|Beta::Location

                       Now you can place Table2|Alpha::qty and Table2|Beta::qty on your layout to show the location specific values of qty on your layout. You can also select these fields from related records when you export this data to excel.

                       An alternative approach:

                       Replace the two calculation fields with global text fields in this design. Then assign the values of "alpha" to one and "beta" to the other. This option allows you to change what locations are specified in each column without having to redesign the database, but also requires that you take extra steps to keep the correct values in these global fields--especially if you are sharing this system over a network.

                       Note: This all assumes that you have just one record in table 1 for a given location and item #. If you have multiple records with the same two values this method will need to be revised to handle that issue.

                  • 6. Re: Updating a Single Row from Multiple Records
                    ZoltanOrban_1

                         Thanks again for your reply.

                         Unfortunately I have trouble understanding what this all mean.

                         I created the fields, and put "Alpha" and Beta" and  in the "specify calculation" and set it as text, but I don't see where I can put those relationships. I can only select fields under "Manage Database ...." --> Relationships, and also don't know how to place these fields on a layout: Table2|Alpha::qty  Table2|Beta::qty.

                    • 7. Re: Updating a Single Row from Multiple Records
                      philmodjunk

                           Open Manage | Database | Relationships

                           Click Table2 to select it.

                           Click the Duplicate button.

                           You now have a new Tutorial: What are Table Occurrences? of Table2.

                           Double click it and you'll get a dialog box where you can rename the table occurrence as I've shown above to get names such as Table2|Alpha.

                           Then drag from fields in Table 1 to fields in Table2|Alpha to set up the relationship. You can drag from Item# to Item# and then drag from cosntAlpha to Alpha. Or you can double click a relationship line to open a dialog box where you can add or change the match fields used in the relationship.

                           To add Table2|Beta::qty to your layout:

                      Enter Layout Mode

                      Drag and drop from the Field Tool in the status tool bar to add a new field to your layout.

                      The Specify Fields Dialog box opens.

                      Select Table2|Alpha from the drop down at the top of the dialog box. Then click qty in the list of fields from that table occurrence to select it for your layout.

                      • 8. Re: Updating a Single Row from Multiple Records
                        ZoltanOrban_1

                             Works Great! Thanks for your help!