Manage Inventory prices Adjusted to Inflation

[CreateGo]

Hi airtable communicty 🙂

Here is a small challenge I face.

I created an inventory for an auto shop. 

How to show the last price added for the product? 

Example: 

Product AA - we bought for $10

Latter with $30

And after a couple of weeks price decreased to $20

Now I can make only average or max. 

Here is a screenshot how I made MAX value added for this product.

I found a formula like LAST_MODIFIED_TIME() 

But I don't know how to use, to display last price of product added

Thank you

[Karlstens]

@ScottWorldsolves this problem well with the method he discusses in this thread;

https://community.airtable.com/t5/other-questions/select-the-top-record-on-a-specific-view/m-p/64845...

Out of interest, I just tested it out myself as I can see if being overly useful, and it works well!

Here we have a list of records, each with unique prices - and importantly they're tied to a product index. Note the "Latest Price" formula field - this is key for this to work.

And the resulting Product Index table, with the correctly detailed last price entry lookup;

[Andrey_Kovalev]

Hello @CreateGo ,

I can suggest the following formula for the rollup field:

Collect all the prices into a string, then extract the latest element (price), and finally convert the result into number.

[Karlstens]

Love this! But that formula may need need tweaking as it does fail with certain values;

[Karlstens · Accepted solution]

I think I've found a better Regex expression to handle this, that will work with decimals too;

REGEX_EXTRACT(ARRAYJOIN(values), "[^,]*$") + 0

[Andrey_Kovalev]

@Karlstens , thanks for correcting my solution. 

[CreateGo]

Wow, thank you, guys! 

@Andrey_Kovalev @Karlstens 

Works amazing! You are amazing 🙂

[Karlstens]

Just keep in mind the main difference between these two techinques - the first that I copied from ScottWorld's post is more robust and should always return the value you're expecting. However the Regex solution has two risks to be wary of - the first being the regex code itself, in that it looks to be working but make sure you test thoroughly. But more importantly, be aware that the regex is simply matching the last element of a mimic'd array, an array that can actually be re-ordered through Airtable UI that will cause it to stop reporting the correct value.

Frustratingly, there's no "Keep Sorted" toggle on a Linked Record field (and I feel that there should be a sort option, or have the ability to order as per an associated View - but alas I'm still waiting for this feature).

If you're working with the regex solution, you may want to protect the Linked Record field from being manually manipulated, or write an automation that keeps it in the correct order.

[Karlstens · Accepted solution]

Further to my last post, I decided to write an automation that keeps the field sorted via date if a price change is detected.

const {recordIndexID, arrayId} = input.config();

let itemsTable = base.getTable("Fruits");

//These are the records that are potentially out of order
let targetRecords = await itemsTable.selectRecordsAsync({
    fields: ["Fruit", "Created", "Fruit Index"],
    recordIds : arrayId   
    })

console.log(targetRecords)

//The below methods grab those records, their creation date details, orders them, and then creates the correctly ordered replacement object. I've never double mapped before, so perhaps there's a better way to write this. 

let sortedRecords = targetRecords.records.map( record => ({
    id : record.id,
    date : new Date( record.getCellValue("Created"))
}))
.sort((a,b) => Date.parse(a.date) - Date.parse(b.date))
.map( record => ({id: record.id}));

console.log(sortedRecords)

let indexTable = base.getTable("Fruit Index");
await indexTable.updateRecordAsync(recordIndexID, {
    "Fruits" : sortedRecords
})

My script was a little more convoluted than I anticipated, due to Airtable selectRecordsAsync not sorting the manually entered field array (this annoyed me to the point that I've actually just raised a bug fix... they probably know about this - but the limitation could at least be mentioned in the documentation).

Anyway, that's my morning coffee done, time to get on with some real work! ☕

[CreateGo]

I see... Interesting )))
this is the next level for me ))

Thank you for investing your morning coffee time to teach me, I truly appreciate it. 

May God bless you and your work.