3 Replies Latest reply on Nov 30, 2010 10:09 AM by FentonJones

    adding "packages" to my database



      adding "packages" to my database


      Hi There, 

      Im a relatively new user of filemaker and have found it a great and rewarding software and have built a fairly robust database from scratch to run my boss's business. I have a relatively simple setup of tables that from parent to child go:

      customers -> Orders -> Invoices -> line items <- products

      so the jobs is basically a hub for everything going on with a customer order (artwork requests, follw up calls etc.) the invoice is where the products get chosen that get added to the line items table. The invoice is basically a populated portal. My boss was happy with this and i explained why it is a good, but now he wants package deals to be added, so a collection of products. do i figure out how to auto-populate the portal or do i make something between line items and invoices that has every product, but also different package possibilities too? the other thing is tht package deals are only ever really a template as products need to be added or subtracted from the invoice, so it really is just an easy way to populate the portal with a number of different products for a number of different package options.

      I dont need my hand held on this one and dont want to put anyone out, just any ideas to help me on the way would be more than appreciated


        • 1. Re: adding "packages" to my database

          Yes, this is a further extension of the database. It requires another table, a "join" table, we'll call it Packages. It has (at least) 2 IDs. The values in BOTH are "from" the ProductID field Products table; they are both "foreign" keys. One holds the "parent Product ID value" of this "package",* and the other is the "Product ID value of this particular item".

          Neither of them is an auto-enter serial. You may also want a unique serial ID for the Package table, but it is not automatically required (in my opinion).

          One of the biggest problems with this setup is telling the 2 IDs apart; so name them so that is obvious.

          *A "package" becomes a NEW record in the Products table. It has its own name, its own ID (unique, serial or UUID, same as any other Product), and its own Price. Its ProductID becomes the "parent ID" of the Packages join table. A "package" Product is composed of its package's products. Its price can either be just a calculation, combining the prices of its items, or independent of their prices (or a calculation based on a percentage discount, which is the easiest method for computers, but silly humans generally want to mess with that :-).

          The items for a "package" Product (which is a real record in Products) can be added via a portal to the Packages table, based on the package product's Product ID, to the Package::ProductID_parent (my name). The relationship has [x] Allow creation of related record, and likely [x] Delete related records (both on Packages side ONLY). By chosing any other product, for the Package::ProductID_self field (my name), a package item is created for that "package" Product.

          So a "package" Product is just another product, for most purposes, tied to its product package items. This becomes critical if you need to do real Inventory later.

          • 2. Re: adding "packages" to my database

            thanks for the reply, but i think it over complicates what i am trying to achieve which i think is a result of my bad initial post.

            ill try to explain it and give my current idea.

            at the moment i have: invoice -< line items >- products

            like most examples, you add line items to the invoice through a portal. i have set up the relationship so that in the portal, you choose a product category, then the next pop up menu fills with only products relating only to that category. this isnt good enough for the sales staff, they want to have predefined 'packages' that will never have the same items in them.

            so basically they will need a button or some way for the portal on the invoice to automatically populate with a set list of line items, but be able to change what some of those products are without affecting the package for the next person who wants to use it.

            so effectively it is almost a template.

            my idea would be a scripted button that automatically generates a number of specific line items from the menu and they then automatically populate the portal on the invoice. so its not really a package, just something that adds a several line items to the invoice making up the base for the order. is this possible for me to achieve? 

            im guessing its something to do with adding a button that adds a line item to the portal, fills out a quantity and then moves to the next line and creates another record with a set quantity and so forth. i don't think i need another joining table, just a way to generate and add predefined items to an invoice.

            i hope this makes sense. let me know if any futher explanation is needed. 

            thanks in advance!

            • 3. Re: adding "packages" to my database

              Ah, I didn't really notice the "template" aspect. But it doesn't change much of the structure. Except a "package" Product no longer needs a price. There is a big difference however in what happens when you choose a package Product. Instead of just entering itself, a script would need to run. The script would go to (or gather) the IDs of the products referenced in the Package table, then add them as Line Items, one at a time.

              The interface may be a little odd, if the ProductID is a drop-down or popup line (not a portal). You'd choose a "package" Product, and suddenly several lines would appear instead. This would require FileMaker 10, as it would need to run via a Script Trigger. But if that's what they expect, there's no real problem. A filtered portal for Product choice may be a better interface.

              You still need the same relational structure, because you need a place for the package items, even though they're just a template. It is tempting to try and do things with not enough tables or structure, but it doesn't work. (It is also possible to bloat a database with redundant structure, but that's not the case here.)

              If a Product could appear in multiple Packages, you'd need to decide what to do abouts duplicate line items on an invoice. You could: 1. Not add duplicates, but increment the Qty on the existing, or, 2. Warn the person, and let them add Qty, or create 2, or 3. Warn them, and let them deal with it, or, 4. Just create 2, and let the chips fall where they may :-|