8 Replies Latest reply on Jul 29, 2013 4:17 PM by philmodjunk

    Portals and Sorting



      Portals and Sorting


           We have two layouts, Order and TT.  On the order layout,  there are 2 portals that work together with a value list of factory names. Selecting the value list allows us to create an order by displaying the models that the factory makes in Portal A and allows us to imput the quantity for each model. Portal B just displays all the information related to the order (PO) including calculations for amount (price x quantity) and sum of the size of the models(ttl cbm). We are able to select from multiple factories for each order.

           The problem is on the TT layout, which information from Portal B, we have two portals that sort based off of different fields.  One portal is suppose to display all POs and is sorted by the payment status of the orders.  Right now It only displays from one PO and duplicates the first line in the portal.

           The second portal on the TT layout is suppose to sort by factory name. We want to display every PO that is related to the selected factory.  Since each order can have different factories, we are unable to sort information properly by factory. Each line will show us all the prices from all the factories in the order when we only want the prices from one factory in the order.

           Is there any way for us to make the portals on the TT layout work?  If not, is there a way to make it so that we are only able to select one factory from the drop down value list in the Order layout so that each PO will only have one factory.


        • 1. Re: Portals and Sorting

               Am I correct that the All Records portal lists records from Order 2? (Portal setup|Show related records from shows Order 2 selecte?)

               And the problem in that portal is that your Balance field does not show a balance for each order?

               And From which Table Occurrence does the One Factory portal show records? And it has the same problem with it's balance field?

               And is your layout based on Order?

          • 2. Re: Portals and Sorting

                 The layout is based on Order.

                 All Records and One factory both list records from OrderLineItems|ByFactoryOrder.

                 All Records is only showing records from one PO and it is being affected by another portal on this layout with a selection box that filters by PO. We would like it to display all PO and to only sort by Payment Status.

                 The other problem is in each order, there could be multiple factories, so if we want to use One Factory to see all the invoice, balance and deposits from that factory alone, we can't because we could not separate it in the order sheet. 

                 I have attached a picture of our Order layout. If we select factory 2 in One Factory, we want to be able to see only the sum of two models from factory 2. Is it possible to have subtotals for each individual factory in the order portal and also have the total amount for all the factory in the portal as well? 

            • 3. Re: Portals and Sorting

                   Let's take this one part of your problem at a time and wait until it is resolved before looking at your other issues.


                        All Records is only showing records from one PO

                   That is how you have set up the underlying relationship.


                   Order::_pkOrderID = OrderLineItems|ByOrder::_fkOrderID.

                   I would guess that your "Other portal that controls this" has a button that performs a script to find a different record in the layout's table, Orders and thus a different list of order line items.

                   IF you want to see all OrderLineItems records, you'll need a different relationship such as one that uses the X operator instead of =. This may require adding another table occurrence of OrderLIneItems so that you can set up such a relationship and then set your portal to show records from this new table occurrence.

                   But what you posted was: "We would like it to display all PO and to only sort by Payment Status."

                   That leads me to this question: Do you want to list line items or orders in this portal? Either way requires a relationship based on the X instead of the = operator, but the difference will be which table, an occurrence of Orders or an Occurrence of OrderLineItems should be linked to Orders in such a relationship.

                   and to only sort by Payment Status

                   Do you really mean "sort"? Sort means to arrange the records in a specific order--such as a sort order that arranges the PO's listed into two groups, the paid and unpaid POs. Or do you mean "filter" or "search"? In which case, maybe you only want to see the unpaid PO's and want to exclude all PO's that have been paid.

              • 4. Re: Portals and Sorting

                     When I said "sort" I meant "filter". Sorry for the confusion. 

                     In All Records, we would like to be able to select from payment status, "open", "close" or "pending" , and then the portal under drop down box will show all Orders ( PO ) that's open or closed or pending depending on which one we select.  I still do not really understand how the X operator works and how to effectively use TOs.  If I add another table occurrence of OrderLineItems, will I be making a self join relationship with it and use the X operator?

                • 5. Re: Portals and Sorting

                       The "other portal" is just a normal portal from orderlineitems|byorder with a drop down box that allows us to select PO (OrderID).

                  • 6. Re: Portals and Sorting

                         The X operator is a relationship where FIleMaker (almost) totally ignores the values in the match fields. You can select any fields you wish for the match fields and any record in one table will match to all records in the other.

                         If you set up a portal using such a relationship, you can then specify a portal filter to reduce the records shown to only those for which the filter expression evaluates as True.

                         Thus, with a portal to Order 2 and you want it only to list Order records where Status = "UnPaid", you can use this filter expression:

                         Orders 2::Status = "UnPaid"

                         If you then add a field (I recommend a field with global storage specified) where the user selects a status, you can use this portal filter expression:

                         Orders 2::Status = Orders::gSelectedStatus

                         BUT, this will create an issue as the portal will not automatically update when a different status is selected in the gSelectedStatus field. To correct that, we get to the (almost) that I referred to earlier. Make your relationship between Orders and Orders 2:

                         Orders::gSelectedStatus X Orders 2::anyfield

                         And the portal will update automatically whenever the value of gSelectedStatus is changed by the user. (WIthout this relationship detail, you'd need to use a script with Refresh WIndow [Flush Cached Join Results] to get the portal to refresh and this can cause unacceptable delays waiting for your screen to update.)

                    • 7. Re: Portals and Sorting

                           Hi Phil,

                           Hope you had a nice weekend.  

                           I tried your suggestion, but the results are still not quite right, so I am not sure if I did what you said. 

                           I created gSelectPaymentStatus in Order table and Order 2 TO, linking Order 2 to Order by Order 2::paymentStatus X Order:: gSelectPaymentStatus. Then I made the portal based off of Order 2, filtering it with Order 2::paymentStatus = Order::_gSelectPaymentStatus.

                           The problem now is that, the same PO will show up more than once and only one PO will show up on the portal. I think Factory from One factory is creating the problem.

                           Here is some more information in case you need it.  My payment status used to be a calculation but since it was unstored, it did not work well with value list.  Right now my payment status in the order table is just a field with open, closed, and pending.  I hope that eventually the status will change by itself based off of a caculation.  I don't know if making payment status a calculation will interfere with these portals or not. I might just create tabs for All records and create portals with filters for each payment status. One factory and PO breakdown is supposed to work after user performs a find, and they are not suppose to interfere with All Records.

                      • 8. Re: Portals and Sorting

                             Since the portal is now based on Order 2. Make sure that you have fields in the portal from Order 2 and not Order.