6 Replies Latest reply on Jun 16, 2016 5:27 AM by Mike_Mitchell

    Calculated Portal

    kht

      I haver a table called installations. It has a status Open and Closed. These are manually set. I have a layout called Home and on this is a portal showing all Open Installations. Simple so far. I then have another table, Purchase Orders. I wish to show all open purchase orders within a portal. The problem is that the Status (Open/Closed) is based on a calculation. The calculation is

       

      If (Isempty(DeliveryDate),"Open";"Closed")

      In other words, if the item has been delivered, the record is marked as closed.

       

      Any suggestions on the best way of achieving this? I am aware that calculation fields cannot be used for relationships as they are not indexed.

       

      Thanks.

        • 1. Re: Calculated Portal
          bigtom

          Use a portal filter:

          DeliveryDate = "Open"

           

          By default it shows all and you use the filter to show only open orders. Filtering based on an unstored calc may not be the fastest way and it may take a few seconds for FM to build the list for you.

          • 2. Re: Calculated Portal
            kht

            Not sure why i didn't think of that! Case of not seeing the Wood for the Trees!

             

            Many thanks.

            • 3. Re: Calculated Portal
              Mike_Mitchell

              kht wrote:

               

              I am aware that calculation fields cannot be used for relationships as they are not indexed.

               

               

              This is only true if the calculation is unstored, and only if it's on the child side of the relationship. Stored calculations can be used anywhere, and unstored fields can be used for one-way relationships from the parent to the child.

               

              That said, bigtom's suggestion of a portal filter will work. Another way to do it is to put a calculation field that always resolves to "Open" in the parent table, and make that part of the relationship. The advantage is it'll be much faster than a portal filter in most cases, and, should you need to refer to the related set of only open records in a context other than the portal (like in a script or calculation), it will resolve properly (since the calculation engine ignores portal filters). The disadvantage is you have another field in your table that serves no other purpose than this particular relationship. So it'll depend on your use case. Do you need to address the related records other than through the portal? If so, consider using a dedicated relationship instead of a filter. However, if you have a lot of these kinds of relationships for different portals, and you use them for nothing else, and the expected data set is relatively small, then a filter is probably a better choice to avoid cluttering the Relationships Graph.

               

              HTH

               

              Mike

              • 4. Re: Calculated Portal
                bigtom

                Mike makes good points about filtering with the relationships. If your portals get too slow with the filters please look into the other options.

                • 5. Re: Calculated Portal
                  kht

                  Thanks Mike. Bigtom's suggestion was great and it does solve the issue. however, as you both point out, it is slow.

                   

                  I am not sure what you mean about :

                   

                  "..put a calculation field that always resolves to "Open" in the parent table, and make that part of the relationship."

                   

                  My current status in the purchase order table is a calculation which results in Open, Closed or Partial. I cannot relate to this as it is an unstirred calculation.

                   

                  My idea relationship would be:

                   

                  PO Table                    ->                                          Home Table

                  Status (Open, Closed, Partial calculation) ->       Status (radio button set to choose to list Open, Closed or Partial POs

                   

                  How could I achieve this? By parent table, you mean the Home table?

                   

                  Thanks.

                  • 6. Re: Calculated Portal
                    Mike_Mitchell

                    The parent table is the one that's the reference point for the relationship. It's where you're sitting - the TO on which that layout is based. The layout where the portal appears.

                     

                    Why is the status an unstored calculation? The calc you showed earlier doesn't require it.