10 Replies Latest reply on Dec 22, 2011 11:26 AM by rboisjoly

    Invoice from multiple inventories


      Hi all!


      I'm working on a fairly simple Invoicing system and I'm working on the structure at this point. I'm having a hard time deciding about some of my entities though.


      There are three types of items which can be added to the invoice:

      1. Static itmes from a list of about 1000 pre-defined items. Call these General Items

      2. Personalised Items, similar in characteristics to the first, but with a custom pre-defined set with custom prices, based on the Salesperson (one-to-many from SalesPerson to CustomItems)

      3. Ad-hoc items added manually at the time of invoicing.


      I'm wondering if it is better to have three Tables, for each of the types of items, each with an ID, description and price and three join-tables to bind them to the invoices (and in the darkness bind them). Or, a single Table of all possible items, but with a "type" for each item which I could filter on to insert into the joint table when invoicing.


      I'm afraid that if I need to filter, it will interfere with my "filter as you type" script for portals...


      Am I better off using three portals, with threee inventories and three join tables, or, one portal with one join table and one inventory with "types"


      I have to add that the General Items may not be modified by the clerk, the Custom ones could, but shouldn't, so let's say they won't, and the third, the ad-hoc ones, will be entered every time and could be quite different each time.


      Thanks for any suggestions, this will orient my development quite a bit. Thanks!


      - Ren

        • 1. Re: Invoice from multiple inventories

          Hi Ren,


          Since the issues that pertain to each "type" dictate the lattitude that's allowed for invoice item entry, I think you will find it easier to have three portals, one for each type, on the invoice for data entry. That way you can impose the restrictions necessary for each type of Line Item. But this arrangement will undoubtedly make it more complicated to print each invoice from a single table of invoice items without amalgamating them in some way.


          The other question you need to consider is how you want to report on items sold. Do you really want to always have three individual reports from the separate tables of Line Items?


          The best compromise is probably to have a single master table for all the line items with separate Table Occurrences on the RG for each of the item types and use these TO's for the three portals on the invoice layout. Item type would need to be used in the relationship to act as a filter.


          I hope this helps,



          • 2. Re: Invoice from multiple inventories

            Hi John,


            First, thanks for your response, it really helps me clarify what I can do.


            My idea was to have three Tables for the different Item Types, and a single Join Table which could take all three kinds of items upon data entry.


            I was thinking, as you suggest, to have three portals for data entry with some sort of "Insert" button to insert the selected portal row's item in the common JoinTable.


            Creating three TOs seems like the best bet, and using the type as the filter seems to be the obvious choice and will avoid using Portal filtering there, so I can still use that with a field for quickly search.


            So, to be clear and ensure I understand you correctly:


            One Join Table (LineItems) which can take any kind of Item type

            Three Tables for the different Types of items

            On the Data Entry screen, three portals (could be in a Tab Control) using Filtered Relations

            A Script to insert the selected item into the LineItems portal


            I end up with a single join table, which I can print with my invoice.


            All prices in the join table will be Lookups, as usual, in case of price fluctuations.


            That's the general idea.


            Am I seeing all this correctly? Seems elegant enough...

            • 3. Re: Invoice from multiple inventories

              hi ren,


              i don't understand the advantage to breaking up your items into 3 tables.  it makes many things more complicated and some things might not be possible at all... i can't think of a way to show the items from all three tables in a single portal, for example.  it also makes finds more complicated because you don't necessarily know which table you need to search in for an item so you would need to search them all (either scripted or manually).  however, by using the "type" field in a single item table, you can still have 3 portals to display the items, one for each of the different item types.  it  also seems more easily expansible if you ever needed to add a 4th (or 5th, etc) type.  you also wouldn't need to filter the portals, you would just use relationships between a global field in invocies and the type field in items (or a global field from a global table, a filtered portal, and a very short script) to show those items you wanted to see in the portal so it should have no effect on your onobjectmodify filtering script (are you tagging the actual item records with your script or just filtering the portal?  just in case you are doing the former, i have recently learned that the latter is considerably faster and can accomplish the same thing in most situations).  as far as allowing diiferent actions on the user's part depending on the type, i think you could do this easily with script triggers (or with the privileges for their account).


              i would also take into consideration the frequency that each type of item is used.  from a ui standpoint, it is probably not a good practice to grant equal space on your layout to a portal used to enter 10 standard items on every invoice as for the ad-hoc portal which, for example, might only be used once on every tenth invoice.  come to think of it, do you even need a portal for the adhoc items?  if they are unique everytime, you don't really need a portal to display existing ad-hoc items.  clearly i don't completely understand the specifics of the problem you are trying to solve, but i think  for the things you mentioned, there is nothing that using 3 tables can accomplish, that can't be done more easily and efficiently with 1.  would you ever consider making multiple tables for different phone number categories (one for "main number", one for "work", one for "home", one for "mobile", etc)?  i'm not sure i understand why this situation would be any different?  i hope those are helpful things to consider in going forward.


              good luck!


              (another) john

              • 4. Re: Invoice from multiple inventories

                Hi Ren,


                Yes . . . I think you are on the right track.


                There are some philosophical differences in the way different developers organize their base tables.  Some would have three different Product files while others would much prefer to have every Product in one table with a field to hold the Product type. Either way is doable but the complications arise when the types change or a new type is added. It can be easier to add a new type category than to create a whole new set of tables and their TO's but, in any case, an extensive modification to your carefully crafted invoicing structure is almost inevitable.


                That will likely arise when you discover that you are only allowed one unique path from the Product TO to the LineItems TO and indeed to the invoice TO. Here's a brief mock up of one way to organise the RG.


                Screen shot 2011-12-22 at 15.16.49 PM.png

                This is only one way, there are many others. You need to try one or two more of your own to get a feel for the way each will work and behave.


                Good Luck!



                • 5. Re: Invoice from multiple inventories

                  Thanks to both Johns :-)


                  The reason I was hesitating, is that although there will be about 1000 General items which will never change, then, the Custom items, which will be repetitions of some of these 1000, but with a custom price, set pet Salesperson.


                  Each salesperson can sell any of the 1000 items as-is. But they can opt to charge a different price for some of them, and keep this list of customized items around for every invoice they need to make.


                  So, in their own record, it will be possible to move over some of the 1000 items, perhaps 20 or 30 and assign these a custom price.


                  There could be hundreds of such salespeople, and this means that if each one determines they need 40 custom prices, we end up up with a database of items with the Base 1000 items, and many items which are tied to the salespeople.


                  So each item in the inventory could be repeated as a custom item for each salesperson.


                  With a single Table of items, as salespeople add custom items, I would treat them all as separate items, with the salesperson key for custom ones and filter them that way. Something like this:


                  No     slsprsn     Description               price   


                  1                       Item description 1     10$

                  2                       Item description 2     10$


                  900                   Item description 900  20$




                  901    23           Item description 1     9$

                  902    72           Item description 1     8$

                  903    72           Item description 2     8$


                  etc. etc. So lots of repetition potentially. But perhaps it makes it easier to implement.


                  The "types" will not change over time, so I'm not too concerned about having to remodel everything later. Items will either be the base items, and perhaps they could change, but that would be easy to do, just the data would change. And the custom items could be changed by the salespeople in their own managed list.


                  Is the single Table for all items still the best option here? I had a working model with a table of items, a join table to the salespeople where a custom price is defined. But then, the description could not easily be managed. So it is preferable that they can simply import some items in their list and customize as they wish.


                  Although I had mentioned I was looking at three Tables for items, when I started building it, I decided on one with a "type" field finally. Seemed more natural. Fit my ERD better as well.


                  Finally, the problem of the AdHoc items remain... if the salesperson can add an item of their choice, improvised or modified directly in the invoice, this is not too good for consistency obviously, but is being required. I was wondering if I could add these items to the Items Table, to make it easier to build reports perhaps, or maybe it will just be fields on each invoice for situations where no item corresponds anywhere and it is a one-time thing. But I will not be able to report on these the same way and will need to build a separate report for these items I guess...


                  If I can figure out how to modify the Items only when they are ad-hoc items, maybe I'll decide to add them there anyway. I'll give this some thought.


                  Thanks to both of you, your comments have helped me tremendously and in an amazingly timely manner! Very generous of you.


                  - Ren

                  • 6. Re: Invoice from multiple inventories

                    Hi Ren,




                    Following your thread…..


                    My 2cents = Stay with the (1) table for all line items, it makes everything more straightforward. Six months ~ 2 years from now multiple tables will be very confusing and take hours to remember how it all works.




                    In your situation, I would use a type field with a compound table ( one with fields for each type of line item ).  Type 1 (Static items) is straightforward, Type 2 (Personalized items) can be straightforward as well with a child table of products tagging the salesperson’s item description and pricing, Type 3 (Ad-hoc items) can be a group of open entry fields in the line items (no product created in the products table).




                    Things to consider.


                    What if the type changes? What if different customers get a different price on the same item from the same salesperson?


                    What if the salesperson wants to create an ad-hoc item by entering the static item code?


                    Do you want to expose the actual item description on the invoice layout for editing or let the user only edit an underlying field and show the results of the edit in the actual description field?




                    To sum up: I would have a single line items table, with required fields for all types. Enforcing requirements with script triggers and editing underlying fields while displaying invoice fields populated by edits made to those underlying fields. I would maintain one products table with a child table (holding each salesperson’s customizations)also a customer field could be added to further allow customization to not only the salesperson’s preference but customer specific data could be stored and retrieved. Data entry would be scripted thru triggers and all changes would force updates to the actual line items fields.




                    Hope this helps,



                    • 7. Re: Invoice from multiple inventories

                      Hi Tim and thanks for taking some time to give your suggestions!


                      I'm working on going with a single table for all Items... but I'd like to understand your suggestion before I take my decision.


                      I'm not sure what you mean by:


                      Type 2 (Personalized items) can be straightforward as well with a child table of products tagging the salesperson’s item description and pricing, Type 3 (Ad-hoc items) can be a group of open entry fields in the line items (no product created in the products table).


                      What do you mean by Child table exactly? A second table which links the Salespeople to specific items but with customizations with its own relation? This is the part I'm confused. Perhaps because I"m French and the terms aren't quite what I'm used to...


                      thanks for clarifying

                      • 8. Re: Invoice from multiple inventories

                        hi ren,


                        to follow up on your question about how to manage access to modifying the items depending on their type, i know of 2 methods that i have used in the past.


                        1. make a very short "onobjectenter" script so that when a user attempts to enter one of the fields for an item such as the price or the description, for example, it will kick them out if it is a static item or a field in a personalized item that you don't want them to enter.  the script would essentially just be 2 steps: if item type = static (or item type = personalized and field = description), then commit record.  how do you actually create the line items?  are you typing the item code directly into the lineitems portal, or is there a button in the item portal that you click on to launch a create lineitem script?  depending on how you do this, you could also script in the ability to create a new, customized item which could be saved to the items table for future use.  i would recommend this method.


                        2. create a new privilege set (or edit the existing one) for salespeople and assign privileges such that they would deny users the ability to edit any of the fields in the static items or selected fields in personalized items.  it would rely basically on the same logic as above.  you would specify the calculation so that you would grant them access only if the item type was custom, or to certain fields in the personalized items.  this is more universal because it would apply to that field on every single layout, whereas the scripted method you have to set it up manually on every instance of that field on every layout on which it appears.  so that method is more customizable, but this method is much easier to set up.


                        also, i think i would recommend keeping the personalized items in the same table along with the main/primary/parent item.  if you didn't want to see them all listed in the portal on the invoice, you could apply a portal filter such that if the "salesperson" field in items is empty, the record will show in the portal (again, this would not have to affect your filtering script).  i am guessing you probably don't want or need to see the 40 versions of the same item for each sales person.  you would then just add a step to your script so that when the user clicked on the parent item, it would enter the main item if there was no record for this salesperson, or it would enter the record matching the current salesperson.  alternately, you could refine the portal filtering so that it would display the main item, and only the matching (personalized) items for the current user.  does that make sense?





                        • 9. Re: Invoice from multiple inventories

                          Hi Ren,


                          The concept is a modified inventory item based on the salesperson.

                          New Picture.bmp

                          A fully fleshed out model is not available, but here is the concept. The line items will get data based upon the ItemType either from the products table, the products Customizations, or it's own ad-hoc entries. This way invoice printing can be accomplished in the usual maner. Extra portals can be replaced with popup dialogs. It is as John said just one way of many.

                          Hope this helps,


                          • 10. Re: Invoice from multiple inventories

                            Again, thanks to all that posted suggestions and offered their help. This community has a lot to offer!


                            This is what I've decided to do:


                            One inventory of main General Items

                            One Join Table between salespeople and the General list, where they can customize their items' price only

                            One item in General of type "Other" in the extreme cases where something comes up (very rare).


                            Salespeople will not be able to create items, only set a price adjusted for them if need be.


                            In the invoice, they will have acces to two portals to select the items to add the the main Join Table of invoice items, using scripts the selected item will be set from one of the two portals (Custom or General) and copied to the InvoiceItems join table, with a reference to the original GEneral item, but a "type" and salesperson ID.


                            This should reduce the redundancy of items as only prices will be personalized, we thus get two join tables, but the second one is reduced in complexity and the actual items never get repeated.


                            We should be able to have a nice clean invoice this way and it seems easy to generate the reports we need from the Join table of invoiceItems.


                            I'll post back once this is done for future people interested in similar solutions and let you know how it turns out.




                            - Ren