7 Replies Latest reply on Oct 17, 2016 12:15 PM by multisonic52

    Set Status by Portal with ALL records in a table?

    multisonic52

      Hi there:

       

      I've run into a bit of a wall and am hoping someone out there can help me out with this problem.

       

      I have two tables, PEOPLE and PRODUCTS, each with about 100 records. For every PERSON, each of the individual PRODUCTS needs to have a status (three set values: "for sale", "pending", "not available"), plus a notes field. Each PERSON will have different statuses and notes for each of the individual PRODUCTS.

       

      Normally, I would simply use a portal and join table to select the product, set the status, and the note.

       

      I'm hoping there is an easier way to do this. If I'm in a layout based on the PEOPLE table, is there a way to have a portal that lists all of the PRODUCTS with a radio set for the status and a notes field? Upon selecting a status, can it create the join record?

       

      Is there an easier way to do this? I'm looking to avoid the user having to select each product when creating its status/notes.

       

      Thanks so much for any help you can offer!

        • 1. Re: Set Status by Portal with ALL records in a table?
          Mike_Mitchell

          multisonic52 wrote:

           

          I have two tables, PEOPLE and PRODUCTS, each with about 100 records. For every PERSON, each of the individual PRODUCTS needs to have a status (three set values: "for sale", "pending", "not available"), plus a notes field. Each PERSON will have different statuses and notes for each of the individual PRODUCTS.

           

          Normally, I would simply use a portal and join table to select the product, set the status, and the note.

           

          So would I.

           


          I'm hoping there is an easier way to do this. If I'm in a layout based on the PEOPLE table, is there a way to have a portal that lists all of the PRODUCTS with a radio set for the status and a notes field? Upon selecting a status, can it create the join record?

           

          You're confusing data model / structure with interface. Don't compromise the data model for the sake of user experience. Make a good user experience work with a proper data model.

           

          So the short answer is yes, you can script anything you want when a user takes whatever action you want with a combination of buttons and Script Triggers. I almost never allow users to create a join record without some level of automated assistance.

           

          The process is pretty basic: Just store the IDs of the two parent tables (and any other data you might need) in variables, create the join record, and set the appropriate fields.

           

          HTH

           

          Mike

          • 2. Re: Set Status by Portal with ALL records in a table?
            beverly

            you can quickly create a "join" with ExecuteSQL()

             

            SELECT PEOPLE.personID, PRODUCTS.productID

            FROM PEOPLE, PRODUCTS

             

            this is an implicit cartesian join. substitute your field names, of course.

             

            what you get (in a text field) is tab-delimited and can be saved to a txt file to be imported into a new JOIN table (people_products). relate back to both original files, add the status field to the new join table. then display the table in a portal ready to select the status (value list?)

             

            for example:

            a,b,c in table1 and 1,2,3 in table2 yields:

            a1

            a2

            a3

            b1

            b2

            b3

            c1

            c2

            c3

             

            if your tables have 100 records each, the cartesian would yield 100x100 records.

             

            beverly

            • 3. Re: Set Status by Portal with ALL records in a table?
              philmodjunk

              If I read this correctly, you want, for a given person, to see a list of all products, but add notes that are specific to both the person and the selected product on that list.

               

              If so, I do it this way:

               

              Set up this relationship:

               

              People----X----<Products-------<Notes

              People::anyfield X Products::AnyField

               

              Products::gSelectedPeopleID = Notes::_fkPeopleID AND

              Products::__pkProductID = Notes::_fkProductID

               

              gSelectedPeopleID is a global field. You use the OnRecordLoad trigger to set the global to the current People Record's primary key:

               

              Set Field [Products::gPeopleID ; People::__pkPeopleID ]

               

              I can then set up a portal to Products on the PeopleLayout and include Fields from Notes in the portal row. (enable the create option for Products to Notes in the above relationship). You can then go to a given record in People and type notes, status changes etc directly into the fields in the portal to make notes for each product. Switch to a different people record and the note fields change to show the notes relevant to this different people record.

              1 of 1 people found this helpful
              • 4. Re: Set Status by Portal with ALL records in a table?
                scottworld

                Instead of creating a simple 2-table relationship, you're going to need to create a 3-table relationship.

                 

                You'll need to have 3 tables on your relationship graph: People, Products, and "PeopleProductsJoin" (or whatever you want to call the 3rd table).

                 

                This 3rd table will be a "join" table that acts as an intermediary in between your People and your Products. That's how you can keep track of different products for different people, and different people for different products.

                 

                I believe that this "join table" technique might be covered in the FileMaker Training Series Advanced Guide, but I'm not 100% sure.

                • 5. Re: Set Status by Portal with ALL records in a table?
                  multisonic52

                  Thank you for all the help!  philmodjunk, your answer is the closest to what I'm hoping to do but I'm hoping it would be a two way street (ie see the status and notes for each product from the perspective of an individual person and also see the status and notes of each person from the perspective of an individual product).

                   

                  I currently have it set up as such:

                   

                  People----X----<Products_All-------<PeopleProductsJoin-----<PeopleProductsJoin_People

                  PeopleID X ProductsID

                   

                  I can get a portal showing records from Products_All to show all the products, as well as display a radio set from PeoplesProductsJoin::Status to create and set the status, plus set the ProductID based on a script trigger.  As I move from People record to people record, the status for an individual product stays the same for each person because of the X relationship.  Is there a way to do what I'm trying to do? 

                   

                  I'm trying to utilize the radio set because there are so many people and products that need relationships set that it will be the quickest way.  I'm trying to avoid the double click necessary to set a popup or dropdown.

                   

                  Thank you so much for your help thus far and any advice you can offer.

                   

                  Thanks,

                  Mike

                  • 6. Re: Set Status by Portal with ALL records in a table?
                    philmodjunk
                    As I move from People record to people record, the status for an individual product stays the same for each person because of the X relationship.

                    Sounds like you missed some details in my original post:

                    Products::gSelectedPeopleID = Notes::_fkPeopleID AND

                    Products::__pkProductID = Notes::_fkProductID

                     

                    gSelectedPeopleID is a global field. You use the OnRecordLoad trigger to set the global to the current People Record's primary key:

                     

                    Set Field [Products::gPeopleID ; People::__pkPeopleID ]

                     

                    While the relationship from people to products_all is by cartesian join, the link from products_all to notes is not and uses a global field to specify the PeopleID value. The OnRecordLoad triggered script then updates the global variable gSelectedPeopleID in order to link to just the Notes record for that person and product. Thus moving from record to record should automatically bring up a different status and note for each listed product.

                     

                    You can also modify the original data model that I recommended to include another instance of People like this:

                    People----X----<Products-------<Notes>----People 2

                    People 2::__pkPeopleID = Notes::_fkPeopleID

                     

                    A layout based on People 2 can use a portal to Notes to list all products and notes for that person. (Fields from Products can be included in this portal to Notes.) From that context, no script is needed to update the link from People to products.

                     

                    Edit Note: Keep in mind however, that from People 2, you won't see any products for which a note or status has not been entered for that product. This could be useful or a problem depending on what you need.

                    • 7. Re: Set Status by Portal with ALL records in a table?
                      multisonic52

                      Thank you, philmodjunk.  That was the perfect solution!