11 Replies Latest reply on Jun 1, 2012 4:38 PM by philmodjunk

    Update product availability field thru portal calculation

    luisprat

      Title

      Update product availability field thru portal calculation

      Post

      Hi, I´m making a database to handle product rentals and I need to know who and when rents an item and the availability of it depending on if it´s on rent. I´m already doing a calculation based on the initial inventory of the items (lets say I have 5 bikes for rent) and when someone rents a bike depending of the quantity they rent, the item availability drecreases but once the item is returned, the availability field has to be updated depending of the quantity returned (it should always be the same). The problem is that I need to keep a record of the rentals an I do it thru a portal, much like it would be done for invoices, where you keep a separate table with all the line items but when I tried to do a calculation for the product availability based on the returned date(fecha_devolucion) field, it only operates on the first line record, not with the other line items... what I´m doing is a calculation on the field "Availability" (disponible) as follows: 

      If(IsEmpty(T_LineItems_Inventa_Central::LI_solic_fecha_devolucion);cant_inicial-Sum(T_LineItems_Inventa_Central::LI_solic_cant_solicitada);cant_inicial-Sum ( T_LineItems_Inventa_Central::LI_solic_cant_solicitada )+T_LineItems_Inventa_Central::LI_solic_cant_solicitada)

      It checks to see if the field that has the returned date (fecha_devolucion) is empty or not and depending of the quantity solicited it takes it off from the "initial value" field (cantidad_inicial) so once the item is returned when you put the date in the field, it has to put it back to the available quantity but it only works with the first line item, not the other ones. So if I type the returned date for the other line items, it doesn´t calculates anything. I have an initial quantity of 10 items, and I rent 4 items and the availability field should show 6 items available, but once the item is returned, based on the date of returned field, the availability should be restored based on the qunatity of items rented from that person.

      As you can see form my screenshot below, I have indicated that 2 line items have a return date, but only the first line item is been validated for been empty.

      Do anyone knows why it only operates on the first line item ?

      screen_shot_rental_app.jpg

        • 1. Re: Update product availability field thru portal calculation
          philmodjunk

          Can you describe the tables and relationships that you have? In line two, I see a date and a quantity, but no field to identify what item was returned. (are you renting only one type of item or do you rent many types of items?)

          • 2. Re: Update product availability field thru portal calculation
            luisprat

            Hi, I attach an image showing the DB, fields, tables, etc. so you can see all (sorry is in spanish, but I thing you can get it..) We actually rent/sell barcoding equipment and supplies and sometimes we lend equipment to customers and we need to know who has what and when it was given to them. In the case of certain supplies like labels, we have one item description for a bulk of them and we need to take away from the initial quantity the units that the customer is ordering or renting/selling so we can display the actuall quantity available for each item.

            The idea is that once an item has been brought back from either from a rental or lend, the quantity displayed is updated... if it was sold, there´s no need to update the available quantity, thou.

            As always, thanks for your support !!!

            • 3. Re: Update product availability field thru portal calculation
              philmodjunk

              I think you have these tables and relationships:

              Clients--<Invoices----<lineItems

              What I don't see is a table for the equipment|Supplies|etc being selected in the line item records:

              Clients--<Invoices----<lineItems>----Products

              I would think you'd need that table as well here.

              And you never responded to my observation that line two of your original screen shot shows a quantity and a return date, but does not identify what exactly is being returned...

              • 4. Re: Update product availability field thru portal calculation
                luisprat

                if you see, the actual item is made by all the data fields to the left... i´m actually using an inventory file and changing it to what we need, so the Products table is the one that says T_Inventa_Central (which is the central inventory database) and there is no "invoices" DB because that actually is done with another DB altoghether that is not related in any way to this (not yet, but would do it). So this DB is only for inventory management and the Clients DB is indeed linked just to show the customers list on a valuelist on the field "cliente" (customer) on the Portal...

                Now, the Quantity and return date is supposed to be from data for the current record or "item" on the database where the line items are being added.

                I hope I explain myself it a little better this time ´cause it´s lkind of confusing...

                • 5. Re: Update product availability field thru portal calculation
                  philmodjunk

                  Am I correct that your original screen shot shows a layout based on T_Inventa_Central? With a portal to T_LineItems_Invent...?

                  That would explain why the portal row does not have a field for identifying the item.

                  In order to compute current inventory for a given item, you need a relationship that matches to all items of the same type in LineItems and a second that matches to all items in LineItems that have a return date.

                  Add this calculation field, cNonReturnedItemID, to LineItems:

                  If ( IsEmpty ( dateReturned ) ; LI_Num_Registro_Item )

                  In Manage | Database | relationships, make a new table occurrence of T_LineItems_Invent... by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as T_LineItems_SameProductNonReturned.

                  We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                  Add this relationship:

                  T_LineItems_Invent...::LI_Num_Registro_Item = T_LineItems_SameProductNonReturned::cNonReturnedItemID

                  TotalReceived - Sum ( T_LineItems_SameProductNonReturned::Quantity )

                  Will then compute the current inventory level for a given record in inventory. There are a number of ways to put a number in Total Received--it depends on your inventory process. One way is to increment this field by 1 each time you receive a new item of that type to sell/rent to your clients.

                   

                  • 6. Re: Update product availability field thru portal calculation
                    luisprat

                    I got all to the point of adding the relationship  "T_LineItems_Invent...::LI_Num_Registro_Item = T_LineItems_SameProductNonReturned::cNonReturnedItemID" but I don´t get it where that other field I should put it, (in which table) you are telling me: TotalReceived - Sum ( T_LineItems_SameProductNonReturned::Quantity )  or is it the field I have as DISPONIBLE or "availability" from the Inventario_Central table ?? because I did it like this, but a ? sign appears on the field.

                    Here is a screenshot of what I understood you...

                    • 7. Re: Update product availability field thru portal calculation
                      philmodjunk

                      What you have is a recursive calculation and that won't work here.

                      The field can't be named "solic_DISPONIBLE" and then use the same name in the calculation.

                      The first field should be either a number field or a calculation field that returns the total number of that item that you have received for sale. If a number field, you add to it each time you get more of that item to sell or rent. If you use a calculation field, then you need a related table where you add a record each time you receive more product from your suppliers and this field then sums the related quantities in that field.

                      • 8. Re: Update product availability field thru portal calculation
                        luisprat

                        I have changed the field on the calculation to: INI- Sum (T_LineItems_SameProductNonReturned::cNonReturnedItemID) where "cant_inicial" is the initial quantity of items ordered on the Inventory table and now it displays the same number, like it didn´t do anything... I apologize for the confusion now, but there is something going on with the fields on the SUM calculation. I have now translated a couple of fields that are supposed to be the ones we are using so maybe you can figure it out easier what is wrong.

                        In the PORTAL I need to show the records of how many times/who has bought/rented an item and based on the Quantity but as you can see, the Quantity Available field from the INVENTORY table still shows 10 items even thou I have rented 1 but because the field DATE RETURNED is empty, it shouldn´t show that I have 10 items available.

                        As always, thanks a million for your support !!!

                        • 9. Re: Update product availability field thru portal calculation
                          philmodjunk

                          Let's try it this way.

                          link the blue table occurrence (...sameProduct)

                          Directly to the inventory table occurrence (grey) instead of the green line items table occurrence.

                          • 10. Re: Update product availability field thru portal calculation
                            luisprat

                            link it, but nothing happened...

                            • 11. Re: Update product availability field thru portal calculation
                              philmodjunk

                              But now you have a simpler relationship--which may help.

                              Check the value returned by the sum expression here. Do you get a value?

                              Put a portal to the "same product" table occurrence on your inventory layout as a test. Do you see any records in the portal.

                              May need to check and see if the calcualtion field, cNonReturnedItemID, is returning the correct value to match in the relationship to the inventory table.