How to pull a decimal number out of an Array OR how to use conditional lookup in combination with MAX(values) to pull a number from an Array

[Jack_P]

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!

[Kamille_Parks · Accepted solution]

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.

[Jack_P]

That’s perfect.

Thank you Kamille!