8 Replies Latest reply on Mar 8, 2011 11:53 AM by yomango

    Portal with repeated fields

    yomango

      Title

      Portal with repeated fields

      Post

      I hope I can explain my problem: I have an inventory file which is composed of whole purchased items and other items assembled from the purchased items. To assemble an item I had created a portal that hides or shows based on a key field. No problem so far. The portal shows four repetition fields ie, component field (value list of all the items I have typed so I can start arming the assembly), a price field (looks up the price of the item I just choosed), number field for quantity (I type it in) and a calculated field (qty*price field). It seems to work fine so far, but if I change the price of an item, the portal does not show the new price (this is half of the problem), then I have to export the file to an excel file so another software can collect the info (second problem). Since the fileds in the portal are repeating fields they do not go out as a separate row in excel, how can I do that then. So my two absic questions,how do I get the portal to show the new value when one of the component prices change and how do I export so the repating fields go into separate records or rows. Thanks

        • 1. Re: Portal with repeated fields
          philmodjunk

          but if I change the price of an item, the portal does not show the new price

          Are your item prices looked up from another table? If you change a price do you need all the records for that item to update with the new price or just certain records? If just certain records, which ones?

          how do I export so the repating fields go into separate records or rows.

          Don't use repeating fields for this in the first place. Use a table of related records where each separate related record takes the place of one repetition of the repeating field.

          • 2. Re: Portal with repeated fields
            yomango

            Thank you for answering, As always you, ready to help us the dummy ones. The table is the same table (called inventory). The portal, where the repating fileds are comes from a relaationship between inventory and inventory2 where the relationship is based on the itemID and the keyfield to show/hide the portal. And yes, I need to update every assembled item whose component had changed price. *By the way, 

            I was thinking on not using the repeating field but I still have problem updating when a new price occurs. But I do not understand when you say "use a table related records where each separate record takes place of one repetition field" meaning I can make a new record in the portal everytime I want to type a new component for the assembly?

            Again, thanks

            • 3. Re: Portal with repeated fields
              philmodjunk

              The portal, where the repating fileds are comes from a relaationship between inventory and inventory2 where the relationship is based on the itemID and the keyfield to show/hide the portal.

              I really don't see how that can possibly work for you here, but have only a very limited picture of what you have set up here. I think you should have a Products table separate from this inventory table where you have exactly one record for each item. If you place a price field in this table, then you only have to change the price in this one field.

              I can make a new record in the portal everytime I want to type a new component for the assembly?

              That's it exactly. With a portal to a table of related records, you can build a list of components for a given assembled item listing any number of different parts needed to assemble the item.

              • 4. Re: Portal with repeated fields
                yomango

                I guess you are right. So what I have to do is create almost a similar table but with fewer fields where I type all the items I purchase. Then in the inventory table I can start assembling the new items? Is that what you mean?Is therea way where I can send you an attachment with the file or a picture of the layout and the browse

                • 5. Re: Portal with repeated fields
                  philmodjunk

                  You can upload to a file sharing site and then post the download link here if you want others to look at your file or a screen shot.

                  I can't get into too many specifics here as your particular business practices will drive many of the design details. Here's the starting point for one approach

                  ProductsComponents-----<Production-----<InventoryLog
                      |
                      ^
                  ComponentList>----ProductsComponents2

                  You have one ProductsComponents record for each product that you produce and also for each part or component you use to produce those products.

                  ComponentList is used to list all the components for a given product you produce. It need not have more than a few fields, ProductID, ComponentID, Quantity are the minimum needed. It links to ProductsComponents by ProductID so a portal to ComponentList on a ProductsComponents layout will list all the components for a given product. It links to ProductsComponents2 by ComponentID. (ProductsComponents2 is a 2nd table occurrence of ProductsComponents).

                  Production is used to log the quantity and ID of each type of Product produced. Inventory Log tracks all changes in inventory. The Data in Production is combined with the data in ComponentList to generate records in InventoryLog that adds products produced to the current inventory levels and removes components consumed by production from inventory.

                  • 6. Re: Portal with repeated fields
                    yomango

                    http://dl.dropbox.com/u/15239473/Inventory%20ASSEMBLY%20ITEMS%20%283%29%20Backup.fp7 Sorry to bother you. I understand you are busy with bigger and better things, but I beg your help this time. I've drawn a blank on this one. If you see the file in dropbox, that's how I currently have it. The records that show a "YES" in the assembly item field are the ones that are composed by other products listed in the same file. The relationship between the table "Inventory" and a second inventory tablerecurrense "AssemblyHidden" is made so when there's a "YES" the portal shows in one row, several repeating records.The setup works responding to "yes" , listing the records that are currently in the file for me to choose, bringing the cost of that component, multiplying it by the quantity. But if I later change the price of any component, it does not change in the portal. And if I want to export to excel, the repeating field shows one time only. That I understand. So, I am urged to modify the design.

                    I am trying to follow your indications to redesign the file: ProductComponents as you said it, will record every product and each part used to produce a new product. The thing is that a new product is the result of assembling some products together, there are not loose parts, for instance a manequin and a rack which make the new assembled product are made of a manequin and a rack already listed. So, how will the ComponentLIst work? the ProductID will be what, the ID from the product composing the new product or the new productID? I will guess this number is the ID of the  new product. ComponentID is then what, a list of all the products ID, I guess.

                    I do not need inventory as to know how many products I have. If the file is called inventory, it should´ve being called "catalog" instead.

                    If you do not have time to answer me, I will understand. It is just that I can´t believe I drew a blank on this file.

                    Regards,

                    • 7. Re: Portal with repeated fields
                      philmodjunk

                      ProductsComponents is your "catalog" table. InventoryLog is something else. If you don't need to know how many items are on hand nor how the inventory changed as items are assembled, then that table and the Production table are not needed.

                      Say you have these ProductsComponents:

                      123 manequin
                      456 Rack
                      789 Manequin w/Rack

                      Then in Component list, you'd have these records:

                      Qty ProductID  ComponentID
                      1    789           456
                      1    789           123

                      If ProductComponent Record 789 is the current record, a portal to ComponentList will list:

                      1 456 Manequin
                      1 123 Rack

                      The field in the portal that shows "manequin" and "Rack" will be a related field from ProductsComponents 2.

                      • 8. Re: Portal with repeated fields
                        yomango

                        Well, thank you again for your time. I did something similar based on what you suggested before. So now it is corroborated. I do appreciate you patience and time. Best regards,

                        Mauricio