1 2 3 4 Previous Next 47 Replies Latest reply on Jan 6, 2013 2:45 AM by PeterMontague

    How to get FileMaker pro to recognise when an item has been previously added to the inventory

    PeterMontague

      Title

      How to get FileMaker pro to recognise when an item has been previously added to the inventory

      Post

           I want to add new items to my inventory. I have my sku set to increase by one increment for every title that is added to the inventory. 

           I want to make this process to run more efficiently:

           If the ISBN and the condition of a book are the same as a previously entered title I want FileMaker pro to make the sku the same as the previously entered book and record the location of the book and update the quantity. 

           Is there a way to do this? 

           Secondly I want to make a system where each single book gets its own child sku. If it has the same ISBN and condition of another book then I want it to have the same parent sku. Is this the right way to inventory my books?

           This would make it easy to add new inventory, All the data entry person has to do is to scan the enter the ISBN, condition and loation. No counting will be required. But if there are multiple copies of the same book I want to be able to enter the quantity FileMaker pro to make multiple child skus.

           When I create reports of the inventory for feeding marketplaces like amazon I want only the parent sku to be sent to these websites. They will receive updates on the quantity and conditions etc. of my inventory. 

           I want to Have item notes defined as positive and negative. Item notes will be generic and set up as check boxes for data entry. Positive ones could be "signed by the author". A negative one could be "water damaged". I want the positive item notes of the child sku to be given in the reports. But the books which have th negative item notes not to appear in reports until they are the only remaining copies. 

           So to sum up I want:

           Skus to increment automatically when necessary;

           Parent and child skus

           Item notes which will attract buyers to appear so that they will see the best of our stock;

           Item notes which may turn customers away to appear in reports only when all the best copies are unavailable. 

           Can anyone advise me? 

        • 1. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
          philmodjunk
               

                    If the ISBN and the condition of a book are the same as a previously entered title I want FileMaker pro to make the sku the same as the previously entered book and record the location of the book and update the quantity.

               If you define this relationship:

               YourTable::ISBN = TableByISBNCond::ISBN and
               YourTable::Condition = TableByISBNCond::Condition

               Then you can set the SKU field in YourTable to lookup the SKU from TableByISBNCond. TableByISBNCond would be an occurrence of whatever table generates your SKUs an auto-entered serial number but should not be the same table as "YourTable".

               Much of what you request here depends on the structure of your tables and relationships--information not present in this thread so far. Thus the above suggestion may or may not work for you.

               I usually prefer to track inventory in a manner similar to that found in the Inventory Starter Solution for FileMaker 12. Where you have one table of inventory items (your SKUs) and a "transactions" table where one record represents an inventory increase or descrease for a specific item in the first table. Then aggregate functions and/or summary fields can compute a current inventory from the sum of all increases - the sum of all decreases for that item in the transactions table.

          • 2. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
            PeterMontague

                 Should I set up the first set of tables and relationships which you've outlined above in addition to the second set which can be found in Filemaker 12? I have a system in place for taking away from my inventory. I have a sales table which is attached to a line items table which is attached to the inventory. Can all of these tables and relationships work together? I'll send you a photo of the relationships set out in my database later.

            • 3. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
              philmodjunk

                   I'll wait for that screen shot. I need a better understanding of what you currently have and what you want to do before I can comment further.

              • 5. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
                philmodjunk

                     Am I correct that you are adding new records in Stack Transactions to update the inventory totals for each SKU?

                     So in which table do you need to create a new record in order to generate the new SKU? (Which ideally should be based on an auto-entered serial number fiel.) It looks like this would be "Table by ISBN" but I want to be sure .

                     And what is the difference (in terms of function) between ISBN and product_id? (They both seem to identify a specific book title while sku then identifies them more precisely by ISBN and Condition.)

                     Once I have those details confirmed, this looks like something that can be set up with a few global fields, a new occurence of one of your existing tables and a script that uses a relationshp that will either create a new record with a new SKU or lookup an existing record.

                • 6. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
                  PeterMontague

                       I had set up the table "this" to add new records. But following your advice I set up the other tables And relationships. If what you mean by stacked transactions is adding multiple copies of the same book then yes that's what I want to do. But I want the default way of adding the titles to be to add them one at a time because that's the more usual scenario. 

                       Would you mind if I sent you a link to a clone of my database in the morning do you can have a proper look at how it works and advise me accordingly?

                       Thanks do much for your help do far.

                  • 7. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
                    philmodjunk

                         I don't see any table occurrence labeled "This" in your screen shot. Perhaps it's name is hidden by the oddly named occurrence, 49712067784 conv...?

                         Keep in mind that my original suggestion was very general in nature and made with out any real understanding of your database. I'd rather see a screen shot of Manage | Database | Relationships made BEFORE you tried following any of my suggestions to make it work. That identifies for me your starting point so that I can better identify which tables are already in place and working and where either a new table or a new occurrence of an existing table may be added to do what you need.

                         What will one record in THIS represent? One book in your inventory or possibly a group of books all with the same ISBN and condition?

                         PS. Is this for a used book store? (One of my favorite places to shop wink)

                    • 8. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
                      PeterMontague

                           I'll send you some more details in the morning. We sell out of print and antique books in brand new conition. We're based in Dublin. Where are you based? And how are you able to devote so much time to giving advice? 

                            

                      • 9. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
                        philmodjunk

                             My job in large part resembles a Fire extinguisher. "In case of emegency break glass". If I don't get a call for help with the database or the computer hardware, I don't have a lot to do. For that reason, I do keep my resume up to date in a number of places hoping for a better opportunity. (Family health insurance needs make it difficult for me to not work for a compnay with a health plan.)

                             Other times I watch TV and mess around with the forum at the same time. And many posts of mine take less thatn a minute to create as I use a database that contains answers to a number of commonly asked questions and can often just copy and paste an answer in order to help someone out.

                             If you mean Dublin near the CA bay area, I'm just about an hour and a half East of you in the Central Valley. If you happen to come visit the DigFM user group meeting that meets once a month at Filemaker Inc. in Santa Clara, you might just bump into me.

                             Week

                        • 10. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
                          PeterMontague

                               Well there is no better resume than your work here on the forum. Are you working freelance or for one particular company? I'm a part time High school teacher of History, Geography and ICT and part time bookseller in a small business with my 80 year old Dad in Dublin, Ireland. We have a huge inventory of about but only have a fraction of them up for sale. I'd love to get some training in how to script. FileMaker is so powerful. How did you learn? There are developers conferences but they all seem to be in London. I read FMP11 the missing manual and that brought me on a bit. but I'm hungry for more.

                          • 11. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
                            philmodjunk

                                 There are a number of training resources but I don't know enough about them at this time to make any recommendations. You can research some books on a site like Amazon and check out the reader reviews to try to get a feel for which may be a good "fit" for you.

                                 I work for a company (the name is abbreviated in my forum name) that doesn't have much for me to do on a day to day basis, hence the job search. Free lance as a sole source of income isn't a good fit for me as I really can't get by without an employer supplied health plan here in the states.

                                 I started with a degree in Computer Science, went into teaching, then discovered FileMaker as a "bundle" that was included with the first Mac that I bought. It was FileMaker Pro 2.5 and a flat file system a mere shadow of today's systems. I taught myself from there applying the computer programming concepts that I learned in college--so my experience isn't typical for many FileMaker users, but then FileMaker, IMO, isn't typical either way, they market the same product to folks such as yourself and also to people with my kind of technical background.

                            • 12. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
                              PeterMontague

                                   Ok I set up a table called Table by ISBN. I put product-id and item-condition into it. I made these form a relationship with their matching counterparts in my oddly named 4971206784conv... Which fields do I need to make global?

                                   This might sound like a stupid question but I suppose I need to populate this table with occurrences of all the skus that I've already used so that it can tell me which items have the same item conditions and product-ids.

                                   Do I also need to make sku in my 4971206784conv.. table look up the sku in the Table by ISBN?

                              • 13. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
                                PeterMontague

                                     I've set up a separate table with the fields sku, product-id and item-condition.

                                     Sku is now a calculation:

                                     Case ( Table by ISBN::${product-id} & Table by ISBN::${item-condition} = ${product-id} & ${item-condition}; Table by ISBN::sku ; SerialIncrement ( Table by ISBN::sku ; 1 ) )

                                     Will this work?

                                     Peter.

                                • 14. Re: How to get FileMaker pro to recognise when an item has been previously added to the inventory
                                  philmodjunk

                                       Can you upload a new screen shot of manage | Database | Relationships?

                                       One with all table occurrence names visible and table occurrences you had added prematurely removed?

                                       I still don't have a clear picture of what you have in place here. You may not have needed to add those additional tables.

                                       A briefe description of the purpose for each table would also be helpful.

                                  1 2 3 4 Previous Next