Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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
1834 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!