I am having trouble matching the names you used in your description with the names of your table occurrences. Is a PO, a record in Orders?
And what is a "container" in terms of these tables?
Sorry for the confusion.
Yes, PO is a field in Order.
Container is just a text field for the name of the containers that products will be placed into for shipping.
But what table in your screen shot has a record for each such container? Shipment?
Your portal lists records from ShipmentLineItems so I'm still trying to figure out how your portal is supposed to work.
I am a little confused by your question, but container1 is from shipmentLineItems table.
I don't know if this will help or not, but here is a screenshot of another layout that is based off of shipment.
There are 4 containers in all and there are forumlas for adding and subtracting quantity that works across the 4 container tabs.
For example, TotalQuantity - Ship1 - Ship2 - Ship3 - Ship 4 = OpenQuantity.
That still does not clear things up. The fact that you have 4 tabs for up to 4 containers does not tell me what a "container" represents in terms of tables, fields and records. If I were creating a database for this, I'd have a table where each container that makes up a shipment is one record. Do you have such a table? If so, what is it called? If not, you'll need to explain in more detail how you are managing your shipping containers.
No I don't have a table like that. I know I should have and I wanted to create one however when I started this database, but I did not know enough of how Filemaker worked and could not see a solution of the problem that will arise if I set up the database that way. The problem being that my boss wanted to be able to click on tabs for each container that was part of the same shipment. He also wanted the quantity calculations to work across the tabs. I did not know how to create tabs on one layout based on the number of containers in the same shipment so that if there were three containers in shipment A, then there will only be three tabs for containers A, B and C.
So the only thing I could do was make a more static layout. Where each shipment could have up to 4 different containers (I named them container1, container2, container3, and container4) to hold the products. The containers can hold different quantities of products and will be able to depart and arrive at different times.
These container fields are text fields that store the codename of the actual containers, they are not primary or foreign keys.
I know my setup for the container is not ideal, but I did not know and still do not know how to create a table for containers with primary keys and have it work in the way my boss wants.
So you have the immediate problem:
display the different PO in the container in the portal
As I understand it, you want to enter a "Code name" (or select it from a value list) for a container and then display some data about what is contained in that shipping container. But I'm still trying to understand what data in what table(s) you need displayed and how that data has been linked to a specific shipping container.
And then you also have the more complex issue of possibly redesigning your data model to better support the management of your shipping containers.
Focusing back on the original problem, In what table have you defined the fields: "container1, container2, container3, and container4" ?
Can one container hold items (ShipmentLineItems?) from more than one Order? (PO?)
The container 1, 2,3, 4 fields are in shipmentLineItems.
In the same record, container 1, 2, 3, and 4 cannot have the same container name in the container fields, because the same container cannot be shipped out at different times.
In two or more different records( 2 or more unique _pkShipmentID), users can enter the same container name. So if we have shipment _pkShipmentID 1 and 2
and _pkShipment ID 1 has container ABC with products from PO1 in container 1,
then _pkShipmentID 2 can have container ABC with products from PO2 in any of its container 1,2,3,or 4 fields.
I have found a way to display the data in a way that I want by using report. However I am having trouble getting the find to show fields that are in the
subsummary part. I am trying to write a script to switch it into form view so that the fields will be displayed and after using find, it will go back to list view and sort.
View As [View as Form]
Enter Find Mode [Pause]
View As [View as List ]
Sort Records [Restore]
I attached it to a button, tried it out, but it does not sort. When I click the button, it first shows the find in form view, and when I press enter it is still in find but the subsummary part is gone. I do not really understand how to write scripts. Will I have to try to break this script up and attempt to do something with script triggers?
Sorry but I still have questions here.
The container 1, 2,3, 4 fields are in shipmentLineItems.
That seems very difficult to manage. A shipmentLineItem would be one part of a shipment and apparently, it's quantity can be divided over 1, 2, 3 or even 4 shipping containers. Is that correct?
In two or more different records( 2 or more unique _pkShipmentID), users can enter the same container name
That's what confuses me. two or more SHIPMENT records? But how does that info control what a user does with each ShipmentLineItem? What prevents a user from having two different shipmentLineITems linked to the same shipment from specifying the same container? (which you appear to say is not allowed...)
Let me see if I can sum up what I think you want: You want a portal such that you can select a container identifier code--which can be entered into any one of up to 4 fields in shipment line items, and the portal then lists all shipment Line Item records that have that same value in one of the 4 shipping container fields.
If that is correct, you can set up either a relationship or a filtered portal to list all ShipmentLineItems with a specified container ID in one of the 4 fields:
Define a calculation in ShippingLineItems, cContainerLIst, like this: List ( Container 1 ; container 2 ; container 3 ; container 4 ). Define a field in a different table with a value list specifying the values in cContainerList. Define a relationship matching these two fields. Base your layout on the table where you define the field with the value list, base the portal on this relationship's occurence of ShippingLineItems.
There are also ways to set up this value list as a conditional value list to limit the values to just those that are likely to be selected for this purpose such as all shipments in a given date range or that have a specific status.