Help

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

Topic Labels: Formulas
Solved
Jump to Solution
950 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Jack_P
6 - Interface Innovator
6 - Interface Innovator

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:
    Screenshot 2021-08-27 121147

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)
    Screenshot 2021-08-27 121245

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!

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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.

See Solution in Thread

2 Replies 2
Kamille_Parks
16 - Uranus
16 - Uranus

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!