My goal here is to allow my master inventory list to have a dynamic price based on the most recent purchased price of an item.
So if an item cost $10.00 in August, but costs $15.00 in September, my inventory will initially show a cost of $10.00, and when the September-priced item is purchased it will update to $15.00
I could get this done with an Automation but i’m looking for a cleaner way to do it without having to add formula columns and update their results to a text field in order to use the Find Records functionality of Automations
I’m using a Rollup field to pull the most recent Linked Record by using the MAX(Values) condition shown below:
This allows me to grab the most recent record in my purchasing table that’s linked to the inventory item.
I would then like to pull the associated price from that Linked Record from my Purchasing table into my Inventory Table
I’m trying to use the RIGHT() Function (with and without turning it into a string) but i’m losing the decimal places from the price and i require that accuracy - shown below (Calculation 2 field returns the result of 2030, instead of 30.00)
I’ve looked into using the ARRAY() functions but to no avail.
I’m thinking if i use the RIGHT(Calculation,5), or some alternative that produces similar results, it will give me the latest price to 2 decimal places. But without being able to pull the decimals over i’m left with rounding to the nearest dollar consistently, which isn’t ideal from an inventory perspective.
I think my difficulty lies in pulling a decimal from an array, but i’ve googled and have yet to come up with anything.
Appreciate anyone’s efforts and suggestions on this!