13 Replies Latest reply on Oct 9, 2012 1:48 PM by philmodjunk

    Advice on direction and script

    SueFrost

      Title

      Advice on direction and script

      Post

           I'm building a database to track the Inventory and Invoices for my small business making bath and body products.  Before I start the next item on my list, I'd really appreciate some direction - I don't feel like it's beginner work.

           I make various items - bar soaps, creams, sugar scrubs, etc. They are sold separately and are bundled into gift packs. For example, a "Pamper Pack" contains a bar soap, a jar of cream, a bottle of body wash, a jar of sugar scrub and a pack of bath salts.

           For the purposes of sales, I need to track how many packages I've sold. For inventory purposes, I need to know how many of each item has been sold.

           I have a field on the Inventory table ("Contained In") that lists all my packages. Each indivdiual product (ie. bar soap) has the appropriate packages ticked off. I will also identify the specific fragrance ("Item").

           When I sell a package, I want to show the package sale on the invoice. Behind the scenes, I want to create an entry for each individual item sold so that the inventory is updated properly.

           Clear as mud?

           I'm seeing some scripting in my future but what is the best way to structure a solution? I don't need a completely customizeable solution - there can certainly be some hard-coding of values. 

           The script should be triggered upon sale of a package. I need to grab the package type and flavour and use those values to identify all the Invetory items that are contained in the package. Then I need to create an Invoice Data record ( or should it be a separate table? ) to show the sale of each indivdiual item.

           All thoughts and advice are much appreciated.

        • 1. Re: Advice on direction and script
          davidanders

               You have Invoices that is a Table.

               You have Invoice LineItems that is a Table

               You may want reports that suggests a Join Table between Invoices and Invoice LineItems

               You have Packages that is a Table

               You have Items that is a Table

               You have Clients that is a Table    [many clients have many Invoices] [many Invoices have many packages] [many packages have many Items]

               You have Suppliers that is a Table

                


          http://www.filemaker.com/11help/html/create_db.8.2.html#1027557
          Home > Designing and creating databases > Creating a database > About planning a database
                         A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.                
                              Follow these general steps to plan a database:
          <SNIP>

          http://help.filemaker.com/app/answers/detail/a_id/3248/related/1
          Relational Database Design 101 (part 1 of 3): Designing a Flat File Database

          http://help.filemaker.com/app/answers/detail/a_id/3247/related/1
          Relational Database Design 101 (part 2 of 3)

          http://help.filemaker.com/app/answers/detail/a_id/3246/related/1
          Relational Database Design 101 (part 3 of 3)



          MY STANDARD LIST OF FILEMAKER LINKS

          The White Paper for FMP Novices is useful  - 
                                                       http://foundationdbs.com/downloads.html
                                                       
                                                       Filemaker Free - Listing of free resources  - 
                                                       http://filemakerfree.com/


          Free unlocked templates
                                                       By Richard Carlton Consulting
                                                       Starting Point -  Contacts | Accounts | Calendar | Estimates |Invoices | Projects |Products | Staff etc.
                                                       http://fmstartingpoint.com/
                                                       Donations -  Accounts | Contacts | Donations | Reporting | Prefs
                                                       http://filemakerdonations.com/
                                                       Recruiter -
                                                       http://rccrecruiter.com/alt-index.html/
                                                       Data4Life -  Contacts | Calendar | Projects | Prospects | Journal | Goals
                                                       http://data4life.net/

          RCC Blog  - 
                                                       http://filemakerbits.wordpress.com/
                                                       
                                                       By yzysoft.com  Contacts | Products | Invoices | Letters
                                                       Sample Database -
                                                       http://yzysoft.com/printouts/yzysoft_SampleDatabase.html

                                                       
                                                       A free calendar is available and can be integrated into your database
                                                       Seedcode Calendar Free -
                                                       http://seedcode.com/cp-app/prod/calendarfree10/
                                                       
                                                       Filemaker's  Online Help Pages
                                                       FMP 12  http://www.filemaker.com/12help/index.html
                                                       FMP 11  http://www.filemaker.com/11help/index.html
                                                       FMP 9    http://www.filemaker.com/9help/index.html


          FMP business database demos - some are fully useable
                                                       The Excelisys Business Tracker V3.0  - 
                                                       http://excelisys.com/exbiztracker3.php/
                                                       
                                                       Filemaker released a FMP runtime database in 2007 for students called "Campus Productivity Kit" and is available online, notably at CNet
                                                       Google "Campus Productivity Kit"    https://www.google.com/search?q="Campus+Productivity+Kit"

          WIN -  http://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577579.html/
                                                       Machttp://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577567.html/

                                                       You can rename the database from a .usr file to a .fp7 file and open in FMP 7 thru 11

          Lynda dot com has Filemaker Videos (portions are free)
                                                       http://lynda.com/FileMaker-training-tutorials/116-0.html
                                                       
                                                       YouTube - Filemaker Videos

          FMTutorials Channel
                                                       http://www.youtube.com/user/FmTutorials/
                                                       
                                                       UK FileMaker Channel
                                                       http://youtube.com/user/UKFileMaker/
                                                       
                                                       FileMaker, Inc. Channel
                                                       http://youtube.com/user/filemakerinc/
                                                       
                                                       Lynda.com Channel - Filemaker videos
                                                       http://youtube.com/user/lyndapodcast/videos?query=filemaker
                                                       
                                                       Dwayn Wright's Channel
                                                       http://youtube.com/user/FileMakerThoughts/


                                                       Soliant Consulting Channel
          https://youtube.com/user/SoliantConsultingTV/

          Filemaker Academy Channel
                                                       https://youtube.com/user/filemakeracademy/
                                                       
                                                       Skeleton Key Channel
                                                       http://youtube.com/user/SkeletonKeySTL/


                                                       Accelerate Computer Training Channel
          http://youtube.com/user/AccelerateTraining/
                                                       
                                                       Filemaker News

          Scoop.it is an information curation site - free option available.
                                                       Filemaker Info  -  http://scoop.it/t/filemaker-info
                                                       Filemaker News  - http://www.scoop.it/t/fm-news

          Filemaker Wikipedia  - 
                                                       http://en.wikipedia.org/wiki/FileMaker
                                                       
                                                       Filemaker - Version By Version  - 
                                                       http://tokerud.com/filemaker/


          Search specific Filemaker sites with this Custom Google Search
          http://www.google.com/cse/home?cx=001044389222327874554:vi8it1bulm8
                                                        

                

          • 2. Re: Advice on direction and script
            philmodjunk

                 How are you tracking inventory now? (There are two basic approaches I see here in the forum: 1) A field in your inventory table is updated each time products are sold, received, produced, etc. and 2) a Transaction log is used where a new record is added each time something happens to change the inventory.

                 Are you using the typical invoicing setup that looks a bit like this?

                 Customers----<Invoices-----<LineItems>-----Products (in your case, I'd guess that Products and Inventory are the same table.)

            • 3. Re: Advice on direction and script
              SueFrost

                   Phil - yes, that's it.

                   I had started with the Starter Solutions for Inventory and Invoices and I've combined those.

                   Products and Inventory are the same table (Inventory Details, specifically).

                    

              • 4. Re: Advice on direction and script
                philmodjunk

                     When I list two choices and you say "that's it", I don't know which "it" it is. wink

                     How are you tracking inventory now? (There are two basic approaches I see here in the forum: 1) A field in your inventory table is updated each time products are sold, received, produced, etc. and 2) a Transaction log is used where a new record is added each time something happens to change the inventory.

                     The inventory solution uses a transaction log to update inventory levels. After merging your two solutions, how are you tracking changes in inventory? Still using the transactions log? How do you use info from Invoice Data to update the transaction log now? (That's the part of the process that ill need to take into account any "package deal" or "kit" items listed in Invoice Data.)

                • 5. Re: Advice on direction and script
                  SueFrost

                       Sorry - I'm in Canada and apparently suffering from turkey brain (it's our Thanksgiving this weekend).

                       I'm using #2 - a transaction log (Stock Transaction table for production and Invoice Data for sales) is updated for each production run or sale.

                       The Invoice Data table has an entry for each item sold. It's related to the Invoices table. Invoices Data references Inventory for data lists. Here's my relationship table:

                        

                        

                  • 6. Re: Advice on direction and script
                    philmodjunk

                         a transaction log (Stock Transaction table for production and Invoice Data for sales) is updated for each production run or sale.

                         yes but HOW are you doing that? Manually updating the transaction log? using a script. In some systems, the invoice data and transaction log tables can actually be replaced by a single table. That assumes that items are removed from inventory at the moment they are sold (as apposed to when they are shipped) and would require your invoice to list both the "package" and the items that make up that package--which is not what you've asked for here.

                         A script can loop through the items listed in Invoice data and generate matching entries in the transaction log. When it encounters a "package" product, it can then reference the list of items that make up that package to generate a transaction log for each item that makes up the package.

                         I'd also use a related table to list the items (and quantities) that make up a given "package". This allows you to use a portal to set up the list of items and quantities and provides a ready made list for the script to use in generating transaction log entries.

                    • 7. Re: Advice on direction and script
                      SueFrost

                           I'm manually updating the Invoice Data table with each item sold (I'll get away from 'transaction log' which feels more generic and I don't want you to think I have two tables on the go).

                           Yes, I would assume that items are removed from inventory at the moment tehy are sold (vs. shipping).

                           "A script can loop through the items listed in Invoice data and generate matching entries in the transaction log. When it encounters a "package" product, it can then reference the list of items that make up that package to generate a transaction log for each item that makes up the package."

                      This is what I was trying to say smiley

                      I'd also use a related table to list the items (and quantities) that make up a given "package". This allows you to use a portal to set up the list of items and quantities and provides a ready made list for the script to use in generating transaction log entries.

                      I was wondering if it would make more sense to have a table for Packages rather than trying to cram everything into one bit of script. I like the idea; let's go with that.

                      OK - one Packages table with these fields:
                           - Package Name
                           - portal to Inventory with item name and quantity
                           It's related to Inventory and ... Invoice Data? The Package names would need to be included in my list of Items for sale. 

                           That makes sense.

                           Do you have thoughts for the script? When would it trigger? 

                      • 8. Re: Advice on direction and script
                        philmodjunk

                             I would create a table for Package Items, not the packages. Each record in the table identifies one item in a given package and the quantity of that item used to make up one package.

                             Inventory-------<PackageItems>-----ItemINVENTORY     (itemINVENTORY is a new occurrence of the Inventory table)

                             Inventory::Item = PackageItems::PackageItem
                             PackageItems::ItemItem = ItemINVENTORY::Item

                             PackageItems would have at least 3 fields:

                             PackageItem, ItemItem, and Quantity

                             The script will be fairly complex with at least one loop nested inside of the other. You would perform the script at the moment the order or sale is finalized--in a brick and mortar store, that's the moment you print a copy of the invoice to hand to the customer.

                              

                        • 9. Re: Advice on direction and script
                          SueFrost

                               You rock!

                               I've got the Package Items table built - that makes sense. 

                               I'm thinking I'd like the script to execute when the sale is marked paid - that's as close as I get. Let me have a think about it and write some pseudo-code. (I've done complex code in my day job in Lotus Notes development.) 

                               Would you be able to review it for me?  

                          • 10. Re: Advice on direction and script
                            SueFrost

                                 In thinking about this, I was wondering if it would make more sense to make a copy of Invoice Data for the script to write to (where the line items for each individual item in a package would reside.)

                                 The only place where I need to see the entries is the Inventory details. I should be able to set up a portal to show those records separately - or modify the current portal that displays Invoice Data and have it show both tables.

                                 Thoughts?

                                  

                            • 11. Re: Advice on direction and script
                              philmodjunk

                                   One of the reasons it sometimes works to use the Invoice Data (called a "lineitems" table everywhere but in the starter solutiions) as the "Transaction log" for inventory control is that many of the fields are the same and every line item record in your table has a one to one correspondance to a record in the transaction table.

                                   I don't know what you mean by a "copy of the invoice data table". A new occurrence of that table in Manage | Database | Relationships or an actual, new table created on the Tables tab by clicking the duplicate button? In either case, what does that do to make this any simpler?

                                   One thought your question inspires is that it might still be possible to merge the transaction and Invoice Data (lineitems) tables into a single table and if you don't want the individual items that make up a package to appear on the invoice, use a portal filter (on the invoice data entry layout) or find criteria (on the invoice print layout) to hide them from the customer.

                                   With all of these approaches, you'll still have the same basic outline of your script, to be executed once invoice is "finalized" (paid for in your case).

                                   For each record in Invoice Data of current invoice
                                     Detect that added item is a "package" item.
                                     Use Go To Related Records to pull up list of items in package
                                     For Each item...
                                         Make a new record to log inventory change. compute quantity of item as quantity from PackageItem table times Quantity for package entered in

                                               Invoice.
                                          Go to next item in package list
                                     End Loop
                                     Go to next Invoice data record
                                   End Loop
                                    

                              • 12. Re: Advice on direction and script
                                SueFrost

                                "One thought your question inspires is that it might still be possible to merge the transaction and Invoice Data (lineitems) tables into a single table ..."

                                     I think I do just have the one table - it's Invoice Data. I don't have anything else that tracks items sold.

                                "if you don't want the individual items that make up a package to appear on the invoice, use a portal filter (on the invoice data entry layout) or find criteria (on the invoice print layout) to hide them from the customer."

                                     That sounds good. To make this happen, I feel like I should add a field to Invoice Data that identifies if the line item is created as the result of a package transaction. That will give me the field to filter my portal on.

                                     Your pseudo code is what I was thinking as well.  I'm off to give it a shot.

                                     Thanks for all your help. It is very much appreciated.

                                      

                                • 13. Re: Advice on direction and script
                                  philmodjunk
                                       

                                            I think I do just have the one table - it's Invoice Data. I don't have anything else that tracks items sold.

                                       That's not quite what I am talking about. You have one table, Invoice Data, that tracks items sold. You have a second table, Stock Transactions that logs each change in inventory. What I am describing is to use one table for both purposes.

                                       

                                            That sounds good....

                                       That's exactly what I had in mind. A portal filter can then exclude them from the portal and if you print your invoice from a layout based on Invoice Data, the find that pulls up the records or a constrain found set (you can use this option if Go to related record is used to bring up the records on the print layout) can filter them out of the found set for printing purposes as well.

                                       With regards to the psuedo code, what I did not specify was in what new table you would create that record. If you use a combined table for both tracking sales and inventory changes, this script creates new records in that table. If you use your current design, this script would need to create new records in the Stock Transactions table and you don't need to create any new records in Invoice Data and thus won't have to hide them from the invoice. Thus, there are pros and cons with either approach.