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?
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?
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.
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?
The "other portal" is just a normal portal from orderlineitems|byorder with a drop down box that allows us to select PO (OrderID).
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.)
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.
Since the portal is now based on Order 2. Make sure that you have fields in the portal from Order 2 and not Order.