1 2 Previous Next 17 Replies Latest reply on Jan 18, 2013 3:06 PM by philmodjunk

    Batch entry of new inventory items

    PeterMontague

      Title

      Batch entry of new inventory items

      Post

           I have my database set up so that one sku matches one title. I want to set it up so that I have a parent sku - for the title - and a child sku for each individual copy. I want to set it up so that if I enter one hundred copies of the same title that I only have to enter the details about one title and that it is duplicated 100 times to make one hundred new records - each one with a different child sku but the same parent sku.

           Should I make the child sku be a unique number each time? Or could this lead to huge numbers?

           How can I get the database to make one hundred records for each individual copy of the title?

        • 1. Re: Batch entry of new inventory items
          philmodjunk
               

                    Should I make the child sku be a unique number each time?

               That would be simplest to implement in FileMaker

               

                    Or could this lead to huge numbers?

               It could over time depending on the number of records you create, but why would this be a problem?

               

                    How can I get the database to make one hundred records for each individual copy of the title?

               Use a looping script. Enter/select the Parent Record in one global field, specify the number of copies in another and then perform a looping script to use those two values to generate the required number of inventory records--each with an auto-entered sku.

          • 2. Re: Batch entry of new inventory items
            PeterMontague

                 Thanks Phil. I'll try that out. 

            • 3. Re: Batch entry of new inventory items
              PeterMontague

                   I've tried that out. I have made new fields called sku and sku child and gSku.

                   I've also made a field called quantity for sale parent.

                   The script will loop the same amount of times as the the quantity in this field.

                   I am duplicating each record in the script. But will this allow me, if each sku has to be unique, to avoid duplication of skus?

                   Also how do I instruct Filemaker pro to stop the loop after, say, 90 times?

                   Peter.

                    

              • 4. Re: Batch entry of new inventory items
                philmodjunk

                     If the sku is an auto-entered serial number, each time you use duplicate record to produce a new record, a new, unique serial number will be auto-entered. The record will be duplicate in the other fields, but not that one.

                     Loop
                        Set Variable [ $K ; Value: $K + 1 ]
                        Exit Loop If [ $K > YourTable::QuantityField ]
                        Duplicate Record
                     End Loop

                • 5. Re: Batch entry of new inventory items
                  PeterMontague

                       Thanks Phil.

                       Just a couple of questions: 

                       how to adjust the parent sku. Currently I have it set to be not empty and always a unique value increasing by an incrment of 1. I presume I need to uncheck the setting for "unique value". But how do I get Filemaker pro to make a new sku? Will it know to make a new sku every time I add a brand new book title?

                       how to make changes to all of the child records: My idea for doing this is perform a find of the parent sku and then use replace field contents. Is there a better way?

                       I want to feed only the parent sku to websites like amazon.co.uk. How do I make a report that doesn't include duplicates of the parent sku?

                  • 6. Re: Batch entry of new inventory items
                    philmodjunk

                         The Parent SKU should not be generated in this table. It should be generated in a related table where it can remain unique an and auto-entered serial number. It should then relate in a one to many relationship by Parent Sku to the table where you have one record for each book in inventory.

                    • 7. Re: Batch entry of new inventory items
                      PeterMontague

                           Hi Phil,

                           I think I have the idea. I set up a duplicate table of my inventory, called inventory 3, and I set up sku in inventory to not be a normal number, not serial, and to look up the serial number in the inventory. 

                           My script looks like this.

                           Go to layout [ "inventory 3" (Inventory 3)]

                           Set Field [Inventory 3::sku; Inventory::sku]

                           Copy [Select; Inventory::Quantity for Sale parent]

                           Loop

                           Set Variable [$K; Value:$K + 1]

                           Exit Loop If [$K > Inventory::Quantity for Sale parent]

                           Duplicate Record/Request

                           End Loop

                           How do I duplicate one record from inventory into inventory 3?

                      • 8. Re: Batch entry of new inventory items
                        philmodjunk

                             Is inventory 3 an occurrence of inventory or a new data source table?

                             On the tables tab, I am recommending that you have two data source tables--one that has one record for every parent SKU and one, inventory, that has one record for every individual book. (BTW, those books did arrive--I forget if I told you that before. My two teenagers are obsessed with Dr. Who so they should get a real kick out of the books come Christmas.)

                             You'd use one method or another to select or create a record in the ParentSKU table and then, from that context, create a new record in the inventory table for as many books of that title you are adding to your inventory.

                             And I strongly recommend against using Copy as a script step if you can possibly avoid doing so. Use Set Variable/Set Field in place of Copy/Paste. This way, if a user has copied data to the clipboard and then they run this script, their copied data is not lost.

                        • 9. Re: Batch entry of new inventory items
                          PeterMontague

                               I'm on holidays now. But I'll try that. I wish I could try it out now though. I don't have much time for database development during the normal working day. 

                               MY hope your children are enjoying the Dr. Who books. I was a big fan when I was younger. I watched a few episodes from the '70s recently. The special effects have aged a lot. But the storylines were good. 

                               Happy Christmas and have a healthy, peaceful and prosperous 2013. 

                          • 10. Re: Batch entry of new inventory items
                            PeterMontague

                                  

                                 

                                      Loop

                                 

                                      Set Variable [$K; Value:$K + 1]

                                 

                                      Exit Loop If [$K > Child sku::Quantity for Sale parent]

                                 

                                      Duplicate Record/Request

                                 

                                      End Loop

                                 I tried that script and it works quite well. Two questions: I had the quantity for Sale parent field set to automatically enter "1" into this field because the majority of books entered are singles. But if I want the quantity for sale parent to be "3". It is "3" in the first record and "1" in all of the others. I've had to get rid of the automatic entry of "1". But it means extra keystrokes for the goods inwards staff. Is there a smart way to get around this?

                                 Also I want to populate all of the records for each individual title. So I want to loop this looping script again.

                                 Something like this is on the right track. But I don't think it is quite right yet. 

                                  

                                 Loop

                                 Loop

                                 Set Variable [$K; Value:$K + 1]

                                 Exit Loop If [$K > Child sku::Quantity for Sale parent]

                                 Duplicate Record/Request

                                 End Loop

                                 Go to Record/Request/Page[Child sku::sku + 1]

                                 End Loop

                                  

                                  

                            • 11. Re: Batch entry of new inventory items
                              philmodjunk

                                   Quantity for Sale parent sounds like a field that should not be in Child sku as you have one common value over multiple records. A global field or a field in a parent record--such as inventory, would seem to make more sense here.

                                   Your looping script can be modified to do what you want, but I'm puzzled by your Duplicate Record step. How do you make sure that there is a record there to duplicate in the first place?

                              • 12. Re: Batch entry of new inventory items
                                PeterMontague

                                      

                                     I think this should work: ill try it in the morning before the kids wake up. 
                                     Loop
                                     Go to layout [child sku]
                                      
                                     Loop
                                      
                                     Set Variable [$K; Value:$K + 1]
                                      
                                     Exit Loop If [$K >  inventory::Quantity for Sale parent]
                                      
                                     Duplicate Record/Request
                                      
                                     End Loop
                                      
                                     Go to Layout [ inventory ]
                                      
                                     Go to Record/Request/Page[next (exit after last)]
                                      
                                     End Loop
                                      
                                • 13. Re: Batch entry of new inventory items
                                  philmodjunk

                                       The value of $K needs to be reset to 0 at the start of each loop.

                                       And that duplicate record step will produce disastrous results for you here. There's nothing in your script that brings up the correct matching record in Child Sku for the current record in Inventory.

                                  • 14. Re: Batch entry of new inventory items
                                    PeterMontague
                                         

                                              but I'm puzzled by your Duplicate Record step. How do you make sure that there is a record there to duplicate in the first place?

                                         I imported the records in Inventory into child sku. So there is one of each record there now. 

                                    1 2 Previous Next