9 Replies Latest reply on Aug 25, 2016 12:11 PM by quarku

    Grouping portal rows connected with hiding doubles.

    quarku

      Hello Everybody,

       

      I'm creating order part of my solution and I'm pretty much stuck!

       

      Can anyone help me with grouping the portal rows (or filtrating using ExecuteSQL) ?

       

      Is it even possible to automatically group up same products (same product ID)? For example if someone pick up the same "Tool1" in first row and type in quantity "2" and then pick up again "Tool1" in row for example no 4 and type in "5" quantity - my dream portal should then group up "Tool1" in one single row and sum the quantity - "7".

       

      Please help.

       

      PS: My porta filtrating in such a way that it shows no rowa atm - please uncheck the filtration on the begining to show what I'm talking about.

       

      Thank you in advance.

       

      Have a nice day!

        • 1. Re: Grouping portal rows connected with hiding doubles.
          philmodjunk

          Two non SQL approaches that you can use.

           

          Let's say that you have these tables and relationships:

           

          Task----<Tools

          Task::__pkTaskID = Tools::_fkTaskID

           

          with a Layout based on Task and the portal on Tools.

           

          Add an auto-entered serial number field or text field with Get ( UUID ) to uniquely identify each record in Tools.

          Then set up this relationship:

           

          Tools::ToolType = ToolsSameType::ToolType  (These are two occurrences of the same table)

           

          Then a portal filter defined as:

           

          Tools::SerialNumberField = ToolsSameType::SerialNumberField

           

          Will filter out the duplicate entries.

           

          If the possible number of tool Types is fairly small, you can also use a table of tool types as the basis for your portal and set up what I call a "reach thru" relationship that reaches through it via a global field to match to the tools records specified for your current task:

           

          Task----X-----ToolTypes----<Tools

          Task::anyField X ToolTypes::anyField

           

          ToolTypes::ToolType = Tools::ToolType AND

          ToolTypes::GlobalTaskID = Tools::TaskID

           

          The OnRecordLoad Script trigger would be used in this last case to update GlobalTaskID:

          Set Field [ToolTypes::GlobalTaskID ; Task::__pkTaskID ]

          • 2. Re: Grouping portal rows connected with hiding doubles.
            quarku

             

            Can you send me some example more for invice -< invoice_positions >- products

            kind of example.

             

            I have tried but after filtering only one or non portal rows show for me....

             

            I would like to thank you for your support - sorry for late reply (business trip).

             

            Best regards

             

            Small translation legen:

             

            Zamowienie - order

            poz_zamowienia - order_positions

            produkty - products

            • 3. Re: Grouping portal rows connected with hiding doubles.
              philmodjunk

              Seems like I forgot to state the second "non SQL" option--to use a summary report in list view to list your portal items with a subsummary part but no body layout part. You simply sort your items by common value--such as product ID to get one row of data for each Product ID. Summary fields can then total up quantity, price etc.

               

              Fields from the parent table would be added to the layout Header or a leading grand summary part.

               

              So for your invoices, you'd set this layout by basing it on the same table occurrence that you'd normally select in portal set up as the basis for your portal and include fields from the related Invoice table in the header.

               

              Please note that such a layout will always be blank when not sorted to include the subsummary part's sort field in the current sort order.

              • 4. Re: Grouping portal rows connected with hiding doubles.
                quarku

                I use summary fields in higher list views without body - its grouping my invices by drivers and also in second layout by dates.

                 

                I dont understand this point:

                "So for your invoices, you'd set this layout by basing it on the same table occurrence that you'd normally select in portal set up as the basis for your portal and include fields from the related Invoice table in the header."

                 

                Could you please be so kind and provide me some kind of example which I can open in Filemake and see how you do that?

                 

                Thank you

                • 5. Re: Grouping portal rows connected with hiding doubles.
                  okramis

                  Here's a version working with portal filtering.

                  - added a calculated field "IloscTotal"in the positions table summing de amount by productID and invoiceID (didi with ExecuteSQL() but could be done with self relation).

                  - added a primary key "ID" to the positions table

                  - placed the "IloscTotal" over the "Ilosc" field in the portal (solid background), denied field entry and put a conditional formatting: text color = background color if t05_b_zamowienie_T05_C_POZ_ZAMOWIENIA::Ilosc = "" (as long there's no amount in a new record, the total field is invisible).

                  - finally the filter formula:

                  Let ( [

                  indexValues = List ( t05_b_zamowienie_T05_C_POZ_ZAMOWIENIA::ID ) ;

                  searchValues = List ( t05_b_zamowienie_T05_C_POZ_ZAMOWIENIA::_ProduktyIDfk ) ;

                  index = ValueCount ( Left ( indexValues ; Position ( ¶ &  indexValues  & ¶ ; ¶ & t05_b_zamowienie_T05_C_POZ_ZAMOWIENIA::ID & ¶ ; 1 ; 1 ) ) ) ;

                  prevValues = LeftValues ( searchValues ; index -1 )

                  ] ;

                  IsEmpty ( FilterValues ( t05_b_zamowienie_T05_C_POZ_ZAMOWIENIA::_ProduktyIDfk ; prevValues ) )

                  )

                   

                  best regards

                  Otmar

                  1 of 1 people found this helpful
                  • 6. Re: Grouping portal rows connected with hiding doubles.
                    quarku

                    Hi,

                     

                    Thanks for the example.

                    I've just did it by myself using this help article: The Self-Join Relationship: What it is and some examples of how to use it | FileMaker

                     

                    Which helps me to filtrate by self-join relation and set up "1" in each order and each 1st occurrence of certain product.

                    (self-relation - see attachment)

                     

                    Now I can filtrate my portal by "Dup_Spr" = 1 field and I will reach my goal.

                     

                    Your example fits perfectly here because I will need to sum up if client will put qty in two separate Ilosc fields.

                    Thank you once again.

                    • 7. Re: Grouping portal rows connected with hiding doubles.
                      quarku

                      Coming back to the topic.

                       

                      I have check all possibilities and to be honest. It would be much less confusing to my client if my value list of products will react to the Dup_Check flag from your file.

                       

                      For example if someone pick already Tool1 it will not appear on the value list in drop-down list for this order.


                      I have already made a dynamic value list which changes together with group of "Tools" for certain client which is also connected to the same group. In simple words its results with value list which fit to the certain group.

                       

                      But now additionally it should also react to the dup_check field....

                       

                      Anyone have any idea how to solve it?

                      • 8. Re: Grouping portal rows connected with hiding doubles.
                        okramis

                        I agree, filter the value list and though not allow duplicate entries would be the better solution than filter out the duplicates. I recommend, that you search for MagicValueList technic to achieve this. This technic lets you define the value list entries by the onObjectEnter script trigger. The "AntiFilterValues" custom function from here: FileMaker Custom Function:AntiFilterValues( ListA; ListB ) could help to get the list entries like this:

                        Let ( [

                        _ListA = List ( Invoice_Products_global::ProductName ) ; //list of all products

                        _ListB = List ( Invoice_Positions::ProductName ) //list of already booked products

                        ] ;

                        AntiFilterValues ( _ListA ; _ListB )

                        )

                         

                        gives you a list with not yet used products

                        • 9. Re: Grouping portal rows connected with hiding doubles.
                          quarku

                          there is also very nice alternative here:

                          Filemaker - Dwindling Value List - YouTube

                          by Guy Stevens

                           

                          Example in the youtube video description.

                           

                          Best regards.

                           

                          Thank you for your help.

                          Problem solved.