Help

Re: Manage Inventory prices Adjusted to Inflation

Solved
Jump to Solution
1700 0
cancel
Showing results for 
Search instead for 
Did you mean: 
CreateGo
5 - Automation Enthusiast
5 - Automation Enthusiast

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

CreateGo_0-1670959473991.png

 

Now I can make only average or max. 

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

CreateGo_1-1670959650996.png

CreateGo_2-1670959673163.png

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

 

 

2 Solutions

Accepted Solutions

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

Karlstens_0-1670975895150.png

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

See Solution in Thread

Karlstens
11 - Venus
11 - Venus

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!

See Solution in Thread

9 Replies 9

@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.

Karlstens_0-1670964414602.png

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

Karlstens_1-1670964459036.png

 

Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

Hello @CreateGo ,

I can suggest the following formula for the rollup field:

Andrey_Kovalev_1-1670968387062.png

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

 

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

Karlstens_0-1670970540978.png

 

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

Karlstens_0-1670975895150.png

REGEX_EXTRACT(ARRAYJOIN(values), "[^,]*$") + 0
Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

@Karlstens , thanks for correcting my solution. 

CreateGo
5 - Automation Enthusiast
5 - Automation Enthusiast

Wow, thank you, guys! 

@Andrey_Kovalev @Karlstens 

Works amazing! You are amazing 🙂

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.

Karlstens_0-1671047840669.png

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
11 - Venus
11 - Venus

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!

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.