If you want your question to reappear at the top of the list, just reply to your own question. Duplicate posts lead to confusion when different people reply to different copies of the same question.
How does your solution track inventory changes and levels?
We need to know that in order to discuss ways to do what you want.
And is is it exactly the same formula for each product or might the intervals for each product differ?
Inventory tracking is done manually and the stock reorder level also is being set manually.
Yes, it is exactly the same formula for each product.
How does that work in terms of your database?
What do you do with the database itself when something happens that's going to change the inventory level?
Such as selling or shipping product?
Receiving a new order?
Your best bet is to keep track of the date when a product is re-stocked (so, not the date the order is placed but the date the product arrives and is stored in the warehouse). You can then have a calculation that references the current date and subtracts the re-stocked date to generate the appropriate classification:
rsdate - Get ( CurrentDate ) < 31 ; "Best-Seller" ;
rsdate - Get ( CurrentDate ) < 62 ; "Fast" ;
NB: if you set Item Movement to be a calculated field you will need to set it to be an UNSTORED calculation so that the Get ( CurrentDate ) value will evaluate correctly. This has flow-on effects - unstored fields cannot be indexed and thus are slow to search and have a number of other drawbacks. The better method is to have the Item Movement be a text field with an auto-enter calculated value and the "Do not replace existing value of field (if any)" box UNCHECKED. Whenever the rsdate field is modified, this auto-enter calculation will fire and update the value in the Item Movement field.
I will try it and revert back. Thank you Daniel
I am unable to attach my solution on this discussion.
Can you help me on this.
Sorry, I don't know how to achieve that. Perhaps upload the file to DropBox or some other file sharing site, and then post the link?
The post editor should have an 'Advanced' link. That advanced editor display offers an 'Attach' link.
I have attached a link of my solution and i have the following problem.
When i create a new product the Item Movement set to Best Seller and it should be a New Product instead.
Secondly, the Item Movement status doesn't change when the date is being changed within the set value.
If a product was in best seller and the is no more available will the status remain the same?
Thank for your help so far.
Your file has the Item Movement being set in the portal, which I assume is meant to track stock coming in. The Item Movement should instead be tracked in the Stock Entry record, which is the current state of the item. There's nothing wrong with tracking it in the Stock Portal as well, but you evaluate the Item Movement in the Stock Entry first and then have that value auto-enter into the Stock Portal record when it is created.
In the Stock Management table, the auto-enter calculation should refer to the most recent creation timestamp from the Stock Portal record - use the Max() function to achieve this. Something like:
_mostRecentRestock = Max ( Stock_Portal::Date_Time Created ) ;
GetAsDate ( _mostRecentRestock ) - Get ( CurrentDate ) < 31 ; "Best-Seller" ;
GetAsDate ( _mostRecentRestock ) - Get ( CurrentDate ) < 62 ; "Fast" ;
In the Stock_Portal table, have the Item Movement field lookup the value from Stock Management::Item Movement
The _mostRecentRestock is a field in the stock entry which will act as stock movement?
Should i remove the stock movement field in the portal?