1 2 3 Previous Next 70 Replies Latest reply on Jul 6, 2012 10:35 AM by philmodjunk

    Generate new set of record including multiple records

    JillX

      Title

      Generate new set of record including multiple records

      Post

      I have a calculation table C, where I got fields: Factory, Model, Price

      In most cases one factory produce more than one model, and I need to make new orders and keep records of orders.

      For example, in table C:

      Factory   Model

      A             aa1

      A             aa2

      B             bb1

      B             bb2

      When I make order, I will first select factory, and I need to see all models under the same factory, then I will enter quantity for each model and a total price will be calculated.

      After entering, all records will be save like following format in Table Order:

      Factory  Model  Qty  Total

      A             aa1   5       10

      A             aa2   10     30

      ....

      I tried the link Phil sent me before, http://fmforums.com/forum/showpost.php?post/309136/. In this case, one ID reflects one product. How can I see all models in one layout linked with one factory and save all records?

      Thank you!

        • 1. Re: Generate new set of record including multiple records
          philmodjunk

          You can modify that approach by including a factory ID field in two of the tables. This can be used in a relationship to products to get a conditional value list of only the products manufactured by that factory. From your example, it looks like the same model is not produced by two or more factories. Is this correct? (Multiple factories producing the same model can be handled, but it's an important detail to keep in mind.)

          Will you need to specify the factory once for each order or might you specify different factories in the same order?

          The difference is whether you link your orders table or the line items table to the products table using the new FactoryID field.

          Links on conditional value lists:

          Forum Tutorial: Custom Value List?

          Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

          Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

          Hierarchical Conditional Value lists: Conditional Value List Question

          Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Generate new set of record including multiple records
            JillX

            You are right, one model is produced by only one facotry.

            I am working on specify more than one facotry in order too! What would be your advice to that?

            Thank you so much!

            • 3. Re: Generate new set of record including multiple records
              JillX

              One facotry may have dozens models attached, I was thinking after select one factory, I can see all model linked to this factory. In order table, it may look like this in layout:

              Factory  Model  Quantity

              AA          AA1

              AA          AA2

              Then I will enter numbers in quantity field. It works like a portal after I select the factory, while portal records will be replaced after I select the same factory again and enter new Qty number.

              Conditional value list works great to select model, while If I need to place order with more than hundred models in one factory, I may have to select many times to get all models I need.

              Is it possible to show all model records under the same factory in layout?

              Many thanks Phil!!

              • 4. Re: Generate new set of record including multiple records
                philmodjunk

                Value lists are the quickest and easiest method for a new developer to set up. As the list of potential values becomes large, they are not as user friendly as other methods that can be used. The value list and conditional value lists still represent a good starting point.

                Are you able to set up a conditional value list that works for you (except for the fact that the list is so long...)?

                If so, you can set up a portal to products that uses the same relationship. Then clicking a row in this portal can perform a script that enters the ProductID of the selected product into a new record in your line items table. This can be set up to look and function like a checkbox list:

                Select Factory from value list and see list of models in portal. Click a row and it's added to your line items table.

                Your script might look like this:

                Freeze Window
                Set variable [$InvoiceID ; value: Invoices::InvoiceID]
                Set variable[$ProductID ; value: ProductsByFactory::ProductID ]
                Go to layout [LineItems (LineItems)]
                New Record/Request
                Set field [LineItems::InvoiceID ; $InvoiceID ]
                Set Field [LineItems::ProductID ; $ProductID ]
                Go to Layout [original layout]

                TO enter the quantity, you can add a show custom dialog step that uses an input field to get the quantity or you can enter that in the line items portal after clicking to select a portal from the list of models from a specified factory. If you use this second option, you can set up a sort order on your portal so that the most recently ordered item sorts to the top of the items in the portal for easy editing.

                • 5. Re: Generate new set of record including multiple records
                  JillX

                  Thank you Phil! I can set up conditional value list, thanks for the script, I will try on this.

                  On more question, how to show all records in one database on it's layout?

                  • 6. Re: Generate new set of record including multiple records
                    philmodjunk

                    In one Table? (a database can consist of many files and many tables..)

                    Show All Records will show all the records in the table--this would be for list or table view.

                    If you want to show all records in a portal, you'll need to describe what you have in mind in more detail.

                    • 7. Re: Generate new set of record including multiple records
                      JillX

                      Yes in one table.

                      For example, I have a shipping table, in which i have fields: Factory, Model, Container, ETD,ETA. They all exist in one table.

                      I was thinking to see all shipping records I stored in this table on layout, looks like this:

                      Factory   Model   Container    ETD     ETA

                      a           a1        1122          June    July

                      a           a2        1122          June    July

                      b           b1        3344          July     Aug

                      And I have portals built in in this layout, I was thinking if I click on the first record then the portal information will change accordingly.

                      • 8. Re: Generate new set of record including multiple records
                        philmodjunk

                        A list or table view can be set up with Show All records to list what you show there.

                        The portals might be placed in the header or footer of this layout. (I recommend list view instead of table view for this.)

                        • 9. Re: Generate new set of record including multiple records
                          JillX

                          I tried with List(Factory) in same table, while it only catches the according value in one record not all.

                          How can I get list of all factories?

                          • 10. Re: Generate new set of record including multiple records
                            philmodjunk

                            I didn't say anything about using the list function. I am talking about using the list VIEW--an option you can select in the View menu or specify in a script step.

                            List is an aggregate function just like, sum, count, average, etc.

                            aggregate functions use one of the following syntax:

                            List ( repeatingField ) -- produces return separated list of all repetitions that contain data
                            list ( field1 ; field2 ; field3 ) -- produces a list of these fields, omitting any that are empty
                            List (RelatedTable::Field ) -- produces a return separated list of values from all of the related records where Field is not empty.

                            • 11. Re: Generate new set of record including multiple records
                              JillX

                              Sorry I misunderstood...

                              Thank you Phil!

                              • 12. Re: Generate new set of record including multiple records
                                JillX

                                I was thinking is it possible to write a loop and read every row in portal, then save the records to fields?

                                • 13. Re: Generate new set of record including multiple records
                                  philmodjunk

                                  Yes, but why do you need to "save to fields" in this case?

                                  And why do you need such a loop?

                                  • 14. Re: Generate new set of record including multiple records
                                    JillX

                                    Because now I have a Order table with portal which I can draw all models related with the specified facotry. After I type in quantities for each model, the portal with background calculation will show me information like Total amount. So I want to save information from the portal to Order table.

                                    1 2 3 Previous Next