1 2 Previous Next 15 Replies Latest reply on Jul 16, 2012 9:56 AM by philmodjunk

    Problem about Stored Auto-entered calculation

    JillX

      Title

      Problem about Stored Auto-entered calculation

      Post

      Now I have a table Order, where I enter a container number under each OrderNumber.

      There are tables Lineitems and Shipping, and they have relationship:

      Lineitems::OrderNumber>----Order::OrderNumber

      Order2::OrderNumber-----Shipping::OrderNumber

      Now I have a portal in table Shipping from table Lineitems, from which I can see container breakdown to find what's in side of one container. And this relationship and filter need me to build a stored calculation for container number for every records in table Lineitems.

      I enter container number in table Order, and I want to build a stored auto-entered calculation on table Lineitems to allow correct container number being assigned to every records under the same OrderNumber, after I entered container number in table Order. Because Lineitem records already exit before I enter container number, the look-up function is not working here. What could be a solution to this attempt?

      Thank you!

       

        • 1. Re: Problem about Stored Auto-entered calculation
          philmodjunk

          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.

          • 2. Re: Problem about Stored Auto-entered calculation
            JillX

            Oh, I see

            What if I may break one order into serveral containers to ship?

            • 3. Re: Problem about Stored Auto-entered calculation
              philmodjunk

              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.

              • 4. Re: Problem about Stored Auto-entered calculation
                JillX

                Does filemaker pro has script library website where I can find examples for kinds of scripts?

                • 5. Re: Problem about Stored Auto-entered calculation
                  philmodjunk

                  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.

                  • 6. Re: Problem about Stored Auto-entered calculation
                    JillX

                    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.

                    Thank you!

                         

                    • 7. Re: Problem about Stored Auto-entered calculation
                      philmodjunk

                      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.

                      • 8. Re: Problem about Stored Auto-entered calculation
                        JillX

                        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.)

                        • 9. Re: Problem about Stored Auto-entered calculation
                          philmodjunk

                          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?

                          • 10. Re: Problem about Stored Auto-entered calculation
                            JillX

                            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.

                            • 11. Re: Problem about Stored Auto-entered calculation
                              philmodjunk

                              I gather that you aren't ordering shoes. Wink

                              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.

                              Line_Items---<LineItem_Container>----Containers

                              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.

                              • 12. Re: Problem about Stored Auto-entered calculation
                                JillX

                                Technically, we may break shoes into: Left, Right, Lace and accessaries..........Cool

                                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 ?

                                • 13. Re: Problem about Stored Auto-entered calculation
                                  philmodjunk

                                  Yes, but if you are only putting 25 to a shipping container, they'd be very large shoes. Wink

                                  1)

                                  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.

                                  • 14. Re: Problem about Stored Auto-entered calculation
                                    JillX

                                    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 ?

                                    1 2 Previous Next