Update product availability field thru portal calculation
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 ?