5 Replies Latest reply on Sep 26, 2016 6:28 PM by akhlaq38

    Selecting source of data table then enter in portal row.

    akhlaq38

      Hi dears!

      Our products and sales are little different and a bit challenging. It is aluminum bar to build doors and windows. In preparing bill we choose customer name, sold date, gate pass number etc and then enter a number of different products in the portal rows. User choose product ID and in next field all related information appear. He put the quantity of lengths and discount. All the other calculated fields filled up by formulas. Normally we sell in standard lengths (18,16 and 14 feet). But if customer demands a piece of length we have to cut a length to required feet. The remaining piece will stay in store and create problem for inventory counting of full lengths. It is manageable using MOD function but what if more than one piece of same item accumulated. So we created an other store "Piece Store".

      Our requirement is that the first field of portal row should be for choosing store location. By default it should be Main Store and next field of Product ID will populate the list of main store items and third with available stock. But when user select Piece Store, next field should show the date from Piece store, the populated list and available quantity should appear from that store only.

      How it could be achieved? If a sample file could be refered will be adaptable easily. Thanks

        • 1. Re: Selecting source of data table then enter in portal row.
          philmodjunk

          What do you mean by "store"?  Is each 'store' a group of records in the same table? Or do you have a different table for each 'store'?

          • 2. Re: Selecting source of data table then enter in portal row.
            akhlaq38

            Thanks Phil

            There was one storage before and one table "Products" with all our products, but now we created an other storage to store pieces and the table name is "Product_Pieces".

            I have tried to create a new table "Locations" and it is parent of above both tables. Both above tables have one linked field "Location ID" which is a global field. But next how to connect it with Transactions table to appear it in portal where we can select "Location ID".

            One other idea is coming in mind as you mentioned to create two tables as child of same "Products" tables for quantities of these two stores. You can guide better which option will work more efficiently and how"

            • 3. Re: Selecting source of data table then enter in portal row.
              philmodjunk

              I haven't shared any ideas with you at all as I am still trying to confirm what design you have. I am still guessing a bit, but it sound like by the word "store" you mean "table". If so, then this is not the best design option to use and is the source of the trouble you are having. I see no reason not to put all of the data, both the pre-cut lengths and the remnants produced by custom sizes into the same table. You can use a category field to tell one group of records from the other for reports and for data entry tasks such as this one.

               

              A conditional value list can then be used to produce a list of just the values from one category or the other once the records are stored in the same table.

              • 4. Re: Selecting source of data table then enter in portal row.
                David Moyer

                Hi,

                agreeing with Phil, I think that you should just use the Products table.

                I would add a number field to Products, called flag_CutOff, and set it to be zero for your standard lengths and 1 if it's a cut-off.  In the parent table, you could create a matching global number field and use it to filter your portal (or in a relationship).

                • 5. Re: Selecting source of data table then enter in portal row.
                  akhlaq38

                  Hi Phil and David

                  I did not say that it is your idea but actually your question "Is each 'store' a group of records in the same table?"

                  was turning me to think that way. Actually I am not a IT person but a simple maintenance technician working in a big oil company where we use an enterprise Oracle database. There we use variety of ways to putting records. That's why it comes in mind to do this database I am creating might have this and that way.

                  Thanks for your suggestions. I will create new records for cut-off pieces in the same table and they would have a distinguished ID and also a field showing that they belong to which group.