Update the prices of the bill of materials

In this example we will see how to recalculate the prices of the items starting from their bill of materials In practice, by changing the price of an items we will be able to see in which bill of materials it is located and from there we will recalculate the price of the item father.

Script Group

Form

Event

Post saving

So let's see how to use a recursive function, that is, a function that calls itself to be able to calculate all the levels of the bill of materials of each single items passed.

The script will be linked to the post saving of the items.

function calculate(gfather)

- extract the bill of materials and recover the

prices and the costs

tfather = database.getsql("SELECT * FROM

billmaterials_dn WHERE gguidp='" .. gfather .."'")

nrowsp = tfather.countrows()

rowsp = tfather.getrows()

finalprice = 0

finalcost = 0

for i = 1,nrowsp do

finalprice = finalprice + rowsp[i].getvalue("total_price")

finalcost = finalcost + rowsp[i].getvalue("total_cost")

end

database.setsql("UPDATE items SET update=1,cost=" .. utility.formatnum(finalcost,2) ..",price=" .. utility.formatnum(finalprice,2) .. " WHERE gguid='" .. gfather .. "'")

database.setsql("UPDATE items SET tid=" .. tostring(utility.tid()) .. " WHERE gguid='" .. gfather .. "'")

database.addsyncbox("items",gfather)

- check if this is not a child of someone else, if you then start at calculate the other fathers

tfathers = database.getsql("SELECT * FROM billmaterials_dn WHERE gguid_code_dn='" .. gfather .. "'")

nrowsfathers = tfathers.countrows()

rowsfathers = tfathers.getrows()

- Update the prices and costs if the object is used as a son on other lists

sprice = utility.formatnum(finalprice,2)

scost = utility.formatnum(finalcost,2)

gguidfathers = {}

for i = 1,nrowsfathers do

database.setsql("UPDATE billmaterials_dn SET cost=" .. scost .. ",price=" .. sprice .. " WHERE gguid='" .. rowsfathers[i].getvalue("gguid") .. "'")

database.setsql("UPDATE billmaterials_dn SET total_cost=cost * qty_dn,total_price=price * qty_dn WHERE gguid='" .. rowsfathers[i].getvalue("gguid") .. "'")

database.setsql("UPDATE billmaterials_dn SET tid=" ..

tostring(utility.tid()) .. " WHERE gguid='" .. rowsfathers[i].getvalue("gguid") .. "'")

database.addsyncbox("billmaterials_dn", rowsfathers[i].getvalue("gguid"))

-- extract the fathers' gguids

table.insert(gguidfathers, rowsfathers[i].getvalue("gguidp"))

end

-- I proceed to recursively process the items

for i,gp in pairs(gguidfathers) do

calculate(gp)

end

end

First we write the recursive function that considers the gguid of the item to be updated as an input parameter. In this way the table of the bill of materials is retrieved, its price recalculated and updated. During the update we retrieve the gguids of the item that take it back into their bill of materials and, as you can see, we call the same function thus creating recursion.

In practice, the system updates the item from the lower and lower level (always given by the item that we modified at the start) and then backwards recalculates all its fathers.

price = dataview.getvalue("price")

cost = dataview.getvalue("cost")

gguid = dataview.getvalue("gguid")

sprice = utility.formatnum(price,2)

scost = utility.formatnum(cost,2)

- extrapol where the article is used and

update the price and the cost

tlist = database.getsql("SELECT * FROM billmaterials_dn WHERE gguid_code_dn='" .. gguid .."'")

nrows = tlist.countrows()

rows = tlist.getrows()

gguidfathers = {}

for i = 1,nrows do

database.setsql("UPDATE billmaterials_dn SET cost=" .. scost .. ",price=" .. sprice .. " WHERE gguid='" .. rows[i].getvalue("gguid") .. "'")

database.setsql("UPDATE billmaterials_dn SET total_cost=cost * qty_dn,total_price=price * qty_dn WHERE gguid='" .. rows[i].getvalue("gguid") ..

"'")

database.setsql("UPDATE billmaterials_dn SET tid=" .. tostring(utility.tid()) .. " WHERE gguid='" .. rows[i].getvalue("gguid") .. "'")

database.addsyncbox("billmaterials_dn", rows[i].getvalue("gguid"))

-- extract the fathers' gguids

table.insert(gguidfathers,rows[i].getvalue("gguidp"))

end

-- I proceed to recursively process the itemsfor i,gp in pairs(gguidfathers) do

calcolate(gp)

end

program.refreshsection("items")

In this part of the script, the one actually launched by the program, we retrieve the item information and update the price and cost where the latter is called up within the bill of materials. In this way it will be possible to retrieve the fathers' gguids which will then be passed to the Calcolate function. From there, for each father, the function will go up the chain of item ensuring that the modification made by the initial item is implemented by all the bill of materials and therefore by the price and cost of the items that directly or indirectly recall it.