Skip to main content

Hi everyone,



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!

If:





  • You have records in Table 1 linked to one or more records in Table 2,


  • You have {Field A} in Table 2 that can be compared with MAX(values) or MIN(values) (i.e. “what is the most recent date?”, “what is the smallest number?”)


  • You want Table 1 to pull in not just what is the max (or min) of every linked record’s {Field A}, but the value of {Field B} associated with whatever record has the max (or min) value for {Field A}





Then follow along in this episode of BuiltOnAir (Season 8, Episode 2, @ 27:38). This question is asked so often I think I may end up doing a standalone tutorial video.


If:





  • You have records in Table 1 linked to one or more records in Table 2,


  • You have {Field A} in Table 2 that can be compared with MAX(values) or MIN(values) (i.e. “what is the most recent date?”, “what is the smallest number?”)


  • You want Table 1 to pull in not just what is the max (or min) of every linked record’s {Field A}, but the value of {Field B} associated with whatever record has the max (or min) value for {Field A}





Then follow along in this episode of BuiltOnAir (Season 8, Episode 2, @ 27:38). This question is asked so often I think I may end up doing a standalone tutorial video.




That’s perfect.



Thank you Kamille!


Reply