Update inventory prices but not touch price of old purchase orders

Hi all,
I wanted to ask for help, I have an app which lets user create purchase orders, he or she places an order and in the app there is a purchase orders detail screen.
I created a screen where user can change the price of all products but when he/she does that, the prices in the old purchase orders gets changed also and I need to know how to prevent my app from doing that, I only need the new prices to be available for new purchase orders, not the old ones to be changed.

Items table

Update prices screen

Purchase order detail screen

1 Like

Hi @Pabl-2603 have you linked the purchase orders table with the product table? In that case, if you change the price in the product table, it will change on the purchase orders table.

Hi Joel,
Thx for helping, how could I resolve that, actually it is linked because when creating each order, system takes the info from the items table.

HI @Pabl-2603, in your orders table you may want to include another column which would be the original purchase price of the item. Then base your purchase calculation on that new column, instead of the item/product table price. That way any future changes to the price in the item/product table would not affect the purchases that have already taken place.

Hey aj,
Let me give it a try, I will let you know, thx in advance.

Good day aj,
I'm struggling to get it done but seem not to work, could you help pls?

I have my order entries table

I originally based my calculation in the cost column which carries the actual item cost from the items table to the OrderEntries table, so when user updates prices, new price appears here ( sadly changing all my orders ), now, I followed your advice and created another column called "Original Cost" but from there I dont know what to do.

Hi @Pabl-2603, sure, I'll expand a bit. Here is the approach I'm thinking of:

First, a new column to your OrderEntries table. Name it something related to the original item cost. In my example it will be just that Original Item Cost. Then in your Cost column instead of referencing the [Item][Cost] column just reference the new column that you add. For example:

Second, in order for this to work though you'll also need to modify the automation that adds orders to this OrderEntries table. Specifically, you'll want to add the item cost to the new column we just added above.

This ultimately allows you to change the source item costs at mass ([Item][Cost] specifically), and yet the Cost in the OrderEntries table will not be impacted because it is referencing a static item cost (the new Original Item Cost column).

Hope this makes sense, but let us know if you are still stuck.

Thx so much aj,

I went ahead and created another column in my "OrderEntries" table and named it:
Original Item Cost

And referenced it as this: =[Original Item Cost]*[Quantity]

Then in the app I edited the automation that writtes in the order information to the "OrderEntries" table, the change I made was as follows:

But I cant get the blue dot, something is wrong but dont know what.

This is what my ordering screen looks like when placing a new order:

Hi @Pabl-2603, seems like you are almost there! For the =$[Cost column data] variable, I believe you'll want to use FINDROW to find the cost of the item from the Items table. Something like this: =FINDROW(Items, "Items[Item Name]=%", <Insert your item variable name here>)[Cost]. This should find the matching item in the Items table and then retrieve the cost of it (that's where the [Cost] comes into play), and then insert it into the OrderEntries table as the Original Item Cost.