Create a cost history for the items

In this example I will show how to create a history of the costs of the purchased items and in this way for each item it will be possible to analyze the progress.

Script Group

Form

Event

Post saving

This script can be inserted on the post-saving event of the item. You need to remember to change or make the fields that differ from your database and to create the Cost_items table.

cost_table = database.getsql("SELECT * FROM cost_items WHERE gguidp='" ..

dataview.getvalue("gguid") .. "' ORDER by date DESC")

We first create an cost_table object by retrieving all the rows from cost_items and check that the value of the gguidp field matches the value of the current item's gguid.

It is also possible to search for all the rows with the same item codes as the current one, it depends on how the tables were built. However, if you decide to change the code, the link to the costs will be automatically lost. On the contrary, using the gguid ensures that, by changing all the data of the item, the cost history is not lost.

It should be noted that the sql string sorts the cost rows descendingly, in this way we can be sure that the first line corresponds to the last saved cost.

vote = false

nr = cost_table.countrows()

rows = cost_table.getrows()

Let's create a vote variable that we will use to check if we need to save the cost of the item or not. We also extract the number of rows and the rows themselves from cost_table.

if nr == 0 then

vote = true

else

-- I proceed to check the current values

cost = rows[1].getvalue("cost")

if cost ~= dataview.getvalue("cost") then

vote = true

end

end

If the number of rows is equal to zero and therefore the costs have never been saved, then we set the variable vote to True.

If there are rows, the cost must be extrapolated from the first row which will contain the last saved value, since these are sorted in descending order.

If the cost is different from the current cost of the item, we will set the vote variable to True.

if vote == true then

gridcost = dataview.getatable("cost_items")

row = gridcost.adddatarow()

row.setvalue("gguidp",dataview.getvalue("gguid"))

row.setvalue("date",utility.getnow())

row.setvalue("ind",database.getind("cost_items"))

row.setvalue("cost",dataview.getvalue("cost"))

row.save()

end

If vote is equal to True, then we proceed to save the values. First we add a new row to gridcost, that is, the additional table inside the item form. Then we set the gguidp to the item's gguid to find it on the next search, select the current date, the row index and the cost value. Finally we save the row.

Having used the gguidp to maintain the relationship, it will be possible to use the cost_items table as an additional table to have all its history available in the item form.