I don't understand the need for entering the container number into a field in line items if all lineitems for a given order will have the same container number. The field in Order should be all that you need.
Oh, I see
What if I may break one order into serveral containers to ship?
Then you need the field, but then entering the number after the fact in the order table won't tell FileMaker which line items from the order will be placed in that container anyway.
You can script such a process so that entering/selcting a container number assigns it to multiple line items, but you'll need to figure out what method works best for the user for selecting line items in order to assign them to that container.
Does filemaker pro has script library website where I can find examples for kinds of scripts?
Not here at FileMaker Inc's site. I don't know what you might find if you do a web search for one.
Methods that could be set up for a user so that they can select lineitems in order to assign the same container number to them:
1) put a check box or a button in each portal row. Click the checkbox or button for each item to select them.
2) Click the first line items of a block to be selected, then shift click the row for the line item of the Last item in the block of line items.
If I use a checkbox set, the value will be stored right?
In this case, how can I define a value list ? Becasue I have records listed like:
OrderNumber Model Container1 Container2 Container3 Container4
1 a1 aa bb cc dd
2 b1 qq ww ee rr
I have no idea how to define a workable container value list for each OrderNumber, please help me on this problem.
Don't get too sold on check boxes. What I meant by that was that you could enter a container number into a field and then clicking different lineitem records causes the current value of that field to be entered into a field in that line item record. You'd actually be clicking a button, but it's possible to use conditional formatting to make it look like a check box and clicking the item a second time can clear the container number field in the lineitem record and then your conditional format changes things so that the apparent check box is now clear. This produces a check box look and feel, but isn't really a check box.
I see no reason for using fields named container1, container2, container3, etc in your orders record. A single global field is what I would use when you want to assign this value to multiple lineitem records at some point after they were first added to your order.
Note that shift clicking to assign a contiguous block of line items to a single container is simply a more sophisticated implementation of the first method as clicking individual rows without holding down the shift key produces the same result as our "check boxes" example and the same method can be used to show which records in the portal have the same container number as the value in the global field.
I have 4 container fields in Lineitems because in Order table, 1 OrderNumber only take 1 record, there's no detail of what models are included in that Order. When I pull out shipping information, I want to see what models are shipped in one container, and only in Lineitems table I have the detailed information stored.
This is basically why I think I need to create the stored auto-entered calculation to fit every record in Lineitems table to maximum 4 container numbers, so that I can filter detailed information through relationship.
What would be your suggestion to make a auto-entered calculation in Lineitems table, after I entered container in Order table?
(I think because Lineitems already existed before Container Number entered in Order table,I tired with look-up function, it does not work here.)
I don't understand why you need 4 fields for container numbers in a lineitem record. I can see the need for 1 but not 4. Why do you need more than one for a given lineitem?
I had assumed that you were splitting an order up so that different groups of line items were shipped in different containers. It appears that the same lineitem can be shipped in more than one container in the same order.
Is that correct? And it could be up to 4 different containers?
You are right, I may split an order. If I order 100 pieces for one model, but 100 pieces will not fit in one container, then I need to split this 100 pieces into, for example, 25; 25;25;25 in order to fit a container.
It take up to 4 containers.
And what will you do if the qty/size of your lineitem requires 5 containers?
And how do you intend to document the quantity distributed to each container?
And could more than one line item ship in the same container?
One the reasons that I ask these questions because your current approach designs in an upper limit beyond which you cannot go without changing the design of your databas. It may be that you rarely need 3 containers and using 4 is virtually unheard of, but this is still a "designed in" limit that need not exist here.
Two approaches would avoid having any limits on the number of containers needed to ship an order:
1) Define two related tables: Containers, LineItem_Container.
Make one record for each container in the containers table, but use LineItem_Container to list each lineItem that ships in it along with the quantity shipped in that container.
2) Divide your lineitems up so that you have multiple records for a given model ordered--one for each shipping container used. The quantity field in LineItems then becomes the quantity of that product shipped in that container.
With either approach, you can get a list of what models and quantities shipped in each container for a given order.
For apporach 1>
You mean I shall enter container information from Container table instead of Order Table?
If I have records of each container, how can I build valid relationship between Lineitems and LineItem_Container? Or Container to filter informtaion?
For apporach 2>
After breakdown my records, If I still input container information from Order, how to build the auto-enter calculation for each Lineitem and allow filter ?
LineItems::__pkLineItemID = LineItem_container::_fkLineItemID
Containers::__pkContainerID = LineItem_container::_fkContainerID
2) There wouldn't be any auto enter calculation. You'd manually or in a script assign each line item entry to a container by entering a container number. The main change here is the need to "split" lineitems when the same model ships in more than one container. This can be scripted such that you click a button on the line item's row and a dialog pops up where you enter the container number and the quantity. The system then updates the existing line item record to show an adjusted quantity and then creates a new record that duplicates the first except the new record shows the quantity and container number you entered into the dialog.
2) You mean add a button inside of a portal of Line items?
If the button is out of the portal, and if I add a button to every row of the portal, how can the botton recognize which row it should read ?