My Purchase Order code posts a changed cost from PO!ItemCost into Inventory!ItemCost (using the code below). But the next step I'm having a little trouble with conceptualization.
In my Pricing database, I may have 100 records representing the price of a particular item for 100 different clients (one client gets a 12% margin, another gets a 20% margin and so on). What I want is for a new price to be automatically calculated and posted into each Pricing record whenever there is a cost change to that ItemNum, as determined when the PO!ItemNum is placed and the new cost recorded (Item was $30.00, and now it's $31.00).
At the moment the PO line item cost is recorded, I want the code to go look in my Pricing database for all records of that ItemNum, and automatically update all of those records to new pricing, based upon the specific margin in each Pricing record.
I need to get a result set of all Pricing records for that ItemNum, post the new cost in the Pricing!ItemCost field, then pull the Pricing!Margin value and use ItemCost/(1-Margin) to get a new Price Value, which is then posted into the Pricing!ItemPrice field, then go to the next record and repeat the process. So I think I need to get the result set, then loop through it one record at a time, performing the pull/calc/post on each record, then remove that record from the set, then repeat the process on the next record, and so on. Correct? If so, Can someone get me started on setting up the loop code in conjunction with the XResultSet Search and SetValue ? Many thanks.
#include "sbasic_include.sbas" //this group of var's is for XResultSet to get Inventory current fields to post to Inventory Last fields var vLastCost as money var vLastPOQty as double var vLastPOAvail as string var vLastPONum as string var vLastPODate as date var vLastVendor as string
//this group of var's is for XResultSet to post data to Inventory record var vRS as Int var vItemCCost as Money var vItemqQty as double var vItemAvail as string var vPONum as string var vPODate as date var vVendor as string
// this group is for the writing of line items ordered to the SummaryView MultiLine field var vTemp as Int var vLine as String var vItemNum as String var vItemQty as String var vItemUoM as String var vItemDesc as String var vSumView as String var vSumView1 as String
vItemNum = ItemNum vItemQty = ItemQty vItemUoM = ItemUoM vItemDesc = ItemDesc
If ((@Mode() = 1) or (@Mode() = 0)) and (@isBlank(POLinesYN) = 0) { // this XResult section below posts line item data to Inventory record vRS = @XResultSetSearch(@FN, "Inventory", SEARCH_MODE_AND,SEARCH_SYNTAX_QA, "!ItemNum=" + @Str(ItemNum)) If vRS > -1 { //Confirm exactly one matching record If @XResultSetTotal(vRS) = 1 { //Confirm I can change the Inventory record If @XResultSetLocked(vRS) = 0 { // Get the current field info in Inventory in order to post it to the 'Last' fields in Inventory vLastCost = @XResultSetValue(vRS, "ItemCurrentCost") vLastPOQty = @XResultSetValue(vRS, "CurrentPOQty") vLastPOAvail = @XResultSetValue(vRS, "CurrentPOAvail") vLastPONum = @XResultSetValue(vRS, "CurrentPONum") vLastPODate = @XResultSetValue(vRS, "CurrentPODate") vLastVendor = @XResultSetValue(vRS, "CurrentVendor") // Get the current relevant field info from POLines to post in Current fields in Inventory vItemCCost = ItemCost vItemqQty = ItemQty vItemAvail = ItemAvail vPONum = PONum vPODate = @FormFieldValue("Purch", "PODate", 0) vVendor = @FormFieldvalue("Purch", "VendorID", 0) // Post/Set the existing PO data in Inventory from 'current' to 'last' XResultSetValue(vRS, "LastCost", vLastCost) XResultSetValue(vRS, "LastPOQty", vLastPOQty) XResultSetValue(vRS, "LastPOAvail", vLastPOAvail) XResultSetValue(vRS, "LastPONum", vLastPONum) XResultSetValue(vRS, "LastPODate", vLastPODate) XResultSetValue(vRS, "LastVendor", vLastVendor)
// Post/Set the data in POLines to the 'current' data fields in Inventory XResultSetValue(vRS, "ItemCurrentCost", vItemCCost) XResultSetValue(vRS, "CurrentPOQty", vItemqQty) XResultSetValue(vRS, "CurrentPOAvail", vItemAvail) XResultSetValue(vRS, "CurrentPONum", vPONum) XResultSetValue(vRS, "CurrentPODate", vPODate) XResultSetValue(vRS, "CurrentVendor", vVendor) } Else { @MsgBox("Error: Inventory record is locked by another user.","","") } } Else { @MsgBox("Error: There must be only one matching Inventory record.", "", "") } XResultSetClose(vRS) //Close the result set vLine = vItemNum + " ** " + vItemQty + " " + vItemUoM + " ** " + vItemDesc + @Newline() + "==========" + @Newline() POLstr = vLine QtyStillDue = ItemQty - QtyRecd0 @Save If @isBlank(ItemNum) { ThrowFocus(ItemNum) } else { ThrowFocus(ItemCost) } } Else { @MsgBox("Error: Cannot open XResultSet.", "", "") }
}
|