Reflecting price updates through app

[cah2035]

Hi all, 

I work in a research lab and I am building an app that will allow us to "build" liquid reagents using items from our vendors. To do this, I have a table of commercially available items. I then want users to be able to select multiple items and add to a collection, adjust the volumes that they will use, and create a new item that will be the final custom reagent. This will then sum the price of all the components. 

What I want to be able to do is dynamical change the price of those custom items when the price of the components is updated. For example:

Reagent A (Table: Reagents) consists of:

Item 1 (Table: Items) - $5.00

Item 2 (Table: Items) - $10.00

Item 3 (Table: Items) - $15.00

Total = $30.00

Six months later, the cost of Item 1 increases to $30.00 so I update that price in Table:Items . How can I ensure that the price for Reagent A increases by $25.00? 

The first thing I imagine is that I will need a "middle" table where I can link Reagent and Items so that I can maintain a list of the items that make up Reagent A. However, I am thinking like a database designer where records are related and can be dynamically updated with little effort, but I know powerapps does not work the same. Any suggestions would be great. Let me know if I need to provide a better example. 

[pontusofsweden]

Hi @cah2035!

I think you need to have a relation table for the BOM in between, because you need to have a link between the Reagent and the components. I made a test from my side that worked like a charm 🙂 This is what I did:

1. I created three tables (in my case Collections but this works for other sources as well); one for the Reagents, one for the Items, and one for the BOM table (Link). The Reagents just contains a ReagentId and Price. Items contains ItemId and Price. The BOMTable contains ReagentId and ItemId

2. I also created a button for the calculation. My idea is to simplify this by making a total regeneration of the Price for the Reagents, not just for the affected ones. Below you find the code for the calculation and update of the Price (OnSelect):

First, I create a Temp collection which is basically the same as the BOMTable but with an addition of the price of the component.

Second, I create a Temp collection for the Reagents. Not necessary but it´s easier when you have different column names when you filter etc.

Third; Here is where I do the calculation and update of the Reagents table. I go through each record in Reagents and update the Price based on the Sum of the Prices for components related to the Reagent item.

ClearCollect(
    TmpLink,
    AddColumns(
        BomTable,
        "PricePcs",
        LookUp(
            Items,
            ItemId = Component,
            Price
        )
    )
);
ForAll(
    Reagents,
    Collect(
        TmpReagent,
        {
            Id: ReagentId,
            Prices: Price
        }
    )
);
ForAll(
    TmpReagent,
    Patch(
        Reagents,
        LookUp(
            Reagents,
            ReagentId = Id
        ),
        {
            Price: Sum(
                Filter(
                    TmpLink,
                    ReagentItem = Id
                ),
                PricePcs
            )
        }
    )
)

Hope this might help you forward 🙂

BR

Pontus

[cah2035]

Hi @pontusofsweden ,

I am running into another issue since I made things more complicated with my app. I created a BOM table and am able to updating pricing, but the problem comes when the updates change the price of something else and I need to repeat the update again until there is nothing else to update. 

To make things easier to understand, I refer to Parent and Component, where a Component can be either  a single item with a single price or it can be made up of other components (where it can also be found as a Parent in this BOM table). This leads to a cascade effect where a Parent can contain another Parent which can contain another Parent, and so on. If I update the total cost of a Parent, I need to go back to the BOM and check if it exists as a component for another Parent. 

I can't figure out how to work around or prevent this issue. If there was a way in my Sharepoint lists to be able to use calculation fields to do a lot of this price summary work, then that would probably fix my issue but it doesn't seem like that is an option. 

Any suggestions are very much appreciated. Thanks!

[cah2035]

As I think about this more, what it seems like I need to do is, rather than use Parent1 as a component for Parent2, I need to relate all the components of Parent1 to Parent2 (and then use some key fields so that when I display the components for Parent 2, it will show Parent1 as a component, but internally the data is linked only by components). 

Pretty sure that will be the solution, but let me know if you think there is a better way. 

[pontusofsweden]

Hi!

This is a classical multi level BOM I would say. Nothing wrong about that, I actually prefer that rather that single level 🙂

You need to define the BOM level for each component where the "top items" has level 0, it´s components has level 1 etc. Since a component can have multiple levels, the lowest level is the one to use. If you have a static setup, this can be done manually just once. Otherwise you need some logic to find and calculate the level.

I haven´t figured out how to do it yet, but I think you need loop through all your components as many times as you have BOM levels. First iteration will find all top items, and set the Level = 0. Second iteration will find all components connected to a Parent with level 0, set Level = 1 etc etc. Eventually, you have set the BOM level for every component. After this, you can perform the same calculation as of today, but you need to sort the components where the components with the lowest level (highest number) is calculated first. I think that it will solve your problem 🙂

BR

Pontus