1 2 3 4 Previous Next 46 Replies Latest reply on Apr 12, 2013 8:43 AM by philmodjunk

    Multiple Records at Once creation

    henryhu91

      Title

      Multiple Records at Once creation

      Post

            

           Hello FM Users, 

           what would be a good apporach for multiple items to order at once?
           I've Product Table (stored Product Info) and Transaction table (keep track of quantity in and out).
           I would like to have users able to create multiple records at once (proabably in list view) for Transaction table and each record need fields like Supplier Name, Quantity, Inventory to (value list with US or China). 

           Users will need to input any numbers of records that they desire on that layout. it could be 100 or 10 records. after they fill in the values in those fields, those records will not be created yet until they are ready to create by clicking a scripted button to create all those records. I'm thinking of global fields and don't really know how repetitive fields will work.

           any suggestion or comment please? what should I include in script?

           what would be a good apporach? 

           Thank you

        • 1. Re: Multiple Records at Once creation
          philmodjunk

               You don't want to use repeating fields for this.

               Can you give an example?

               I'm imagining several different scenarios here and the differences in those make for fairly different scripts.

          • 2. Re: Multiple Records at Once creation
            henryhu91

                 Thanks Phil for the Response.

                 I've an idea. firstly, users will be on  a product layout and there will be a script to ask them how many items would they be interested in creating for the order. just like say 20 records that they want to create and they will be leaded to a layout there they can be able to input value for Product ID, Supplier Name, Quantity, Inventory to (value list with US or China). all these fields are will be on Transaction table. 

            For example, they will select Product ID 001, Product name will auto fill in from Product table. 

            they can adjust and modify the values of multiple records that they are inputting at that moment until they are satisfied with that, a button to click for committing records.

            • 3. Re: Multiple Records at Once creation
              philmodjunk

                   What would be the value in creating these records in advance?

                   Wouldn't it be simpler to use a portal to the transactions table much like a portal to a LineItems table used on an Invoice?

                   All the customer specific information would be entered once in the invoice record and only quantities/product ID's would be entered in the individual portal rows.

              • 4. Re: Multiple Records at Once creation
                henryhu91

                     Actually, all I need so far right now is just a plain layout with list view or table view with a user desired amount of fields for them to input information for specific parts order. 

                     Let just say i have 10 records to create with different Product ID numbers and those product Ids may or may not have same information for Suppliers, price, inventory to and so on. I think portal set up  commit  right away when user go on to next record to create. 

                      

                • 5. Re: Multiple Records at Once creation
                  philmodjunk
                       

                            and those product Ids may or may not have same information for Suppliers, price, inventory to and so on. I think portal set up  commit  right away when user go on to next record to create.

                       Yet that would appear to be the same issue with your list view layout as well.

                       Or do you want to create these records temporarily and add them to transactions only when "complete"?

                  • 6. Re: Multiple Records at Once creation
                    henryhu91
                         

                    do you want to create these records temporarily and add them to transactions only when "complete"?

                    Yes I would like to create those records temporarily, and add them to transactions only when click "Order". before that, they can modify and make changes. 

                    Should I create a field called Batch number for future tracking?

                    • 7. Re: Multiple Records at Once creation
                      philmodjunk

                           I'd definitely use an Order or Invoice table with a portal to the line items where you add each record for the order.

                           These could be records in a separate table and a script can import the data into transactions when the time is right, but you can also use a status field in the transaction table to mark these fields as "pending" so that reports, etc do not include them. Then a status change (such as clicking your button) on the order record can perform a script that updates the status field in transactions so that they are no longer pending.

                      • 8. Re: Multiple Records at Once creation
                        henryhu91

                             Earlier discussion in Transaction post, you suggested to keep one table for Transaction and Expense/Order. now this situation I need Order table itself ? 

                             what do you mean by a portal to the line items where you can add each record for the order? any example?

                             Thanks Phil 

                        • 9. Re: Multiple Records at Once creation
                          philmodjunk

                               Take a look at the invoices starter solutoin that came with your copy of FileMaker.

                               You have Invoices----<Invoice Data>-----products

                               What I call "line items", the creator of this template called "Invoice Data". Note how a portal is used to fill in the details of a given order.

                               The concept here is that you have one record (Invoices) to record the details about a given sales transaction (who is buying the product, the total cost, the date....) and a related table lists the items purchased (The product ID, cost, Quantity....) in that transaction.

                               My Inventory Ledger concept then adapts this approach to using the inventory transactions table in place of the Invoice Data or "LineItems" table.

                          • 10. Re: Multiple Records at Once creation
                            henryhu91

                                 hi Phil.

                                 I've looked at the invoice. actually quiet not fit with what I need. but It's really impressive way of doing invoice and I will need it for future use.

                                 the way they set it up is all of the different products but from only one company. 

                                 In fact, Order list here is not for our customers to order parts from us. We're ordering products from our suppliers. 
                                 it could be from same or different suppliers for each products that users inputting in. 

                                 It will be great if I can also find a way to create a value list to choose suppliers that users previous input for specific product.

                                 can you please guide me what to do?

                                  

                            • 11. Re: Multiple Records at Once creation
                              philmodjunk

                                   The basic setup for Purchase Orders is the same as for invoices. It's just that you are documenting purchases instead of sales. The fact that you are purchasing from multiple suppliers instead of selling to multiple customers makes very little, if any difference to how you set up your tables and relationships.

                                   And you can certainly link the records in a products table to one or more suppliers. Conditional value lists can then be set up so that when you create a new Purchase Order record and select a supplier, your value list in the portal only lists products linked to that Supplier record.

                              • 12. Re: Multiple Records at Once creation
                                henryhu91

                                     Hello Phil, In this case, now I've Order Batch (just like Invoice), Order (just like Invoice Detail), Transaction and Production. 

                                     One question that I have is I would like to have calculated quantities on products that users confirm to order. 

                                     Even though now with Add New Related Record for Order through Portal set up on Order Batch, what should I do to calculate the quantities when users complete inputting the value in Order? Right now the records on Invoice Details are being committed right away. I don't want to create records in transaction table for quantities until my staffs are ready to order. Should I go by individual product IDs or the whole batch?

                                     what knd of relationship Do I need for multiple Companies? we usually have default supplier we buy the item from and I would like to auto fill in for specific product ID. how can I do that? 

                                • 13. Re: Multiple Records at Once creation
                                  philmodjunk

                                       Actually, I would recommend that you enter these records directly into Transactions with transactions set up as the "line items" table for a givne order record. A status field can be used to identify these records as "ordered", not yet received and various relationships, scripts, calculations can take the status of a given item into consideration when computing inventory quantities.

                                       If a given product may be purchased from multiple companies, then you need a many to many relationship between a table of "Suppliers" (Vendors) and your table of products since a given product can then be ordered from many suppliers and a given supplier can supply many products:

                                       Products-----<Product_Supplier>-------Suppliers

                                       Products::__pkProductID = Product_Supplier::_fkProductID

                                       Suppliers::__pkSupplierID = Product_Supplier::_fkSupplierID

                                       And if you need to designate specific suppliers of a specific product as "preferred" you can use a field in Product_Supplier to identify them on a product by product basis.

                                  • 14. Re: Multiple Records at Once creation
                                    henryhu91
                                         

                                    Actually, I would recommend that you enter these records directly into Transactions with transactions set up as the "line items" table for a givne order record. A status field can be used to identify these records as "ordered", not yet received and various relationships, scripts, calculations can take the status of a given item into consideration when computing inventory quantities.

                                    Do I still need Order Batch table to create similar portal set up as Invoice Starting Database? 
                                         I have a field called Inventory in Transaction Table that determine either the quantity is in US or Shanghai, If I use calculation field, If (test1, result1, result 2:) have limitation of one condition? how can I calculate it?

                                          

                                         

                                              Products-----<Product_Supplier>-------Suppliers

                                         

                                              Products::__pkProductID = Product_Supplier::_fkProductID

                                         

                                              Suppliers::__pkSupplierID = Product_Supplier::_fkSupplierID

                                         I dont' really understand this step. 
                                         I've ID_Product as my unique key and ID_Supplier for Primary key in Supplier table.

                                          

                                          

                                    1 2 3 4 Previous Next