Filter Products in Order Select List Based on Stock and Usage Date

1

I need help with the following project.
I have 3 tables:

  • WarehouseMovement
  • Products
  • Order

I would like the Product column in Order to be a select list of products, but not show those whose stock is less than one. To calculate the stock, you would need to filter in WarehouseMovement those records where the date matches Order.date and stock-use is greater than 0 (considering that on the same day, there may be several rows for the same product that reduce the stock).

image
image1221×790 45.1 KB

For the case shown for the date 14/11/2024, all products should appear except Atomic_LED_Strobe because it has 4 in stock, but 2 + 2 are used, and Barra_Sub_Millenium because it has 2 in stock and 2 in use.
I’ve tried doing this in many ways and I can’t get it right. I would really appreciate your help. Thank you.

2

Hi @exeinformatica_microsshop ,

I updated your data model.

The key changes are:

  • Initial stock is not hardcoded as a column in the product table, but transfered as a movement with a positive quantity in the movements table. In this way you can also model stock replenishments.
  • Consumptions should be movements with negative quantities
  • Current stock can be easily calculated for each product suming all the movements for the given product

Hope this helps,
Pablo

2 Likes
3

Thanks for your reply, but that’s not exactly what I need. Stock is something that depends on each day. For the 14th, a product is not available that is available on the 15th because it is only used on that day. Therefore, a stock total for each product cannot be made. It should be made by product and day.

4

Ahhh, so you’re not selling the items but returning them back to the warehouse at the end of the day?

5

Yes, that’s right. Sorry for not explaining myself well at the beginning. They are not products that are sold, they are devices that are used during the day and returned the same day.

6

Ok then I’ve added an option filter to products in the order table that prevents from showing any item if a date has not been selected and otherwise shows only those with enough stock for that date

Products.Filter(
  CurrentValue.WithName(currentProduct,
    thisRow.Date.IsNotBlank() AND
    currentProduct.Amount-WarehouseMovements.Filter(
      Product=currentProduct AND Date=thisRow.Date
    ).Use.sum()>0
  )
)

As an extra I added some additional validation with a button

1 Like
7

Thank you very much for your solution @Pablo_DV . I have a similar system with the same client, but in this case, the idea is that the products that are out of stock do not appear in the select list. A button option is not valid since this select list needs to appear in a form. I’ve tried a thousand things and I’m not able to make the select list not show items that are out of stock for that day. thank you very much for your help

8

That logic is already implemented, the button is just an extra

1 Like
9

You’re absolutely right, I hadn’t noticed that. You’re a genius! Thanks a lot for your help.

1 Like
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.