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!