7 Replies Latest reply on Oct 3, 2011 9:21 AM by GuyStevens

    Picking products from a list and Marking the picked ones



      Picking products from a list and Marking the picked ones


      I have a Coditional formatting problem I can't seem to be able to wrap my head around.

      I have an invoice layout where I can add products. But because you can't really sort in a value list I made a list view that opens up in a new window where the products are sorted properly. I put a button in front of every product that adds the product to the line items table.

      So you can go trough the list and add items untill you are done. The list appears in a window so you can still see your invoice and see the items get added there.

      There are only 13 portal rows so after 13 items you can't see them anymore without scrolling the portal in the invoice and thus losing the products list window.

      I came up with the idea to use conditional formatting to give the items that where already picked a background color. That way you can immediatly see in the products list what products you have already put on your invoice.

      But It's not really working.

      The Products in the ProductsList can only get a background color if they are

      - present in the OrderDetails Table

      - For the current InvoiceId

      I've tried to set the current InvoiceId as a global field in the ProductsTable but tthat didn't seem to work.

      I also tried making a List of all the InvoiceId's in the Products Table But I'm getting very erratic behavior from the conditional formatting.

      Anybody have a fresh look on this problem?

        • 1. Re: Picking products from a list and Marking the picked ones

          The global field sounds to me like the right way to go, but you need a relationship to use with it in order to check to see if a given product has been added to the OrderDetails table for that Invoice.

          Define this relationship:

          Products::gCurrentOrderID = SelectedOrderDetails::OrderID AND
          Products::ProductID = SelectedOrderDetails::ProductID

          SelectedOrderDetails is an additional occurrence of OrderDetails that you create in Manage | Database | relationships by selecting OrderDetails, then clicking the button with two plus signs to duplicate it. You can doubleclick OrderDetails 2 to bring up a dialog box where you can rename it to match my example.

          The script you use to open the Product List window can also use Set Field to copy the current order's OrderID into the global field. (gCurrentOrderID, must be defined in Products and must have global storage selected in field options.)

          Your conditional format expression can then be:


          Another, similar approach you may want to think about is to list the products in a portal on your Orders layout. That way you can include a search field above the portal and use scripting to filter the portal by the text entered into the search field. This can be a useful way to search out products by a keyword in their description field. (The list can still be sorted to your specifications and the conditional formatting discussed here still works.)

          • 2. Re: Picking products from a list and Marking the picked ones

            Hi PhilModJunk,


            I'm Soo sorry I didn't reply any sooner.


            These mails arrive in my hotmail and I rarely ever look in there.

            I missed this one and I had almost given up on this problem.


            I implemented your solution and it workd really great.

            I only have a small imperfection now.

            My products list opens up in a new window so you can see your invouce in the background.

            The idea is you can add products and see them as they are added on the invoice.

            Only the portal is not updating as I add the products.

            I have to manually go back to the invoice and click in it somewhere to update it and see the products pop up in the portal.

            I would like to find a way to update that automatically.

            I would be tempted to ude "refresh window" and "Flush cashed join results"

            But I recently read an article that talked about using a cartesion join instead of a cashe flush.

            Because it would work better and faster on a network.


            Any thoughts?

            P.S. Thanks again for your help!

            • 3. Re: Picking products from a list and Marking the picked ones

              As a test I've added some "Refresh Window, Flush cashed join results" steps in my two scripts.

              - One script to open the new product list window.

              - one script to add a new item

              Now when I add a product in the new window list I see it pop up in the portal in the background immediatly. So that problem is solved.


              Only if I delete an item from my portal. And i go back to my new window products list that item is still highlighted.

              When I check in the Orderdetails table the recrds are correctly removed. But it seems like the change doesn't take effect immediatly.

              Anyone have any thoughts?

              • 4. Re: Picking products from a list and Marking the picked ones

                Using two windows here does complicate the interface. I had imagined both portals on the same layout.

                What conditional format expression are you using to highlight the selected items?

                • 5. Re: Picking products from a list and Marking the picked ones

                  Actually there is no reason to use a new window when the highlighting works. So Maybe it's indeed smarter to just let all of this happen in the same window.

                  The conditional formatting expression I use is simple:

                  TblProducts::ProductId = SelectedOrderDetails::ProductID


                  The reason however I'm using a new window is because my invoice layout has a few tabs.

                  When I use a new window the user doesn't lose the current tab.

                  (I haven't found a script step yet that allows you to open a layout and open a certain tab.

                  • 6. Re: Picking products from a list and Marking the picked ones

                    Not saying you shouldn't use a floating window for this, it's just that this is a detail that requires some extra care when using so you have to design your scripts accordingly.

                    BTW, if you given each tab panel an object name, go to object can be used to bring a specific tab panel to the front.

                    With your current setup, you delete an item from a portal in the order window and the selection window doesn't update to show the change?

                    You may need to script the delete portal row button to run a script that deletes the portal row, then includes this code to update the other window:

                    Commit Record
                    Select Window [//select the window of products]
                    Refresh Window [//use flush cached join results if you need it, but I don't think you do here]

                    • 7. Re: Picking products from a list and Marking the picked ones

                      I just tried this and it works like PERFECTLY :)

                      How did you become such a FM genious?


                      And thanks for the trick on using GoToObject. That's an interesting one :)   Will use that for sure :)

                      I didn't select "Flush Cashed Join Results" and it looks like it works this way.

                      I am a little hesitant to use it since I read an article on using a cartesion join to update calculations etc. The author points out that flushing does have an impact on databases served on a network.