Mark,
thanks for your comments. I have a very clear vision of the business processes I'm wanting to address, but precise programming planning and implementation is an area that I'm a rookie. When building complex conditionals, I have a problem of getting lost in a sea of "{"s.
But what I've roughed in this weekend is popping my buttons (in a good way).
-Sales order ...
-enter item number ...
-look for existing Pricing record (for this Customer/Item combo) ...
-if found, pull appropo data from Pricing record into SalesOrder Lines record ...
-if no Pricing record found, then pull appropo data in from Inventory record (with a msg telling user to manually enter a Price and program will auto-generate a Pricing record) ...
-if no inventory record, then msg telling user to fill in all appropo fields and an Inventory and also a Pricing record will be auto-generated when line item is saved ...
- when 'line item saved button is pushed', code goes out and looks for Inventory record ...
- if not found, it creates an Inventory record, then
- it looks for a pricing record ...
- if found it compares the Price in Sales Order Lines record with the Price in Pricing record ... if different it then updates the Pricing record ...
- if no Pricing record found, it then creates a Pricing record and populates it with the data from the Sales Order Lines record.
Currently we are inputting the orders in QA Orders database/screen, and it pulls info from Inventory and Pricing. But any changes and updates to Inventory and Pricing must be manually pulled up and edited ... a manual labor black hole of time and energy (and too often neglected). This platform (when I get it polished and put in production) is going to be such a time save for us.
Code for the two fields I described above (if anyone sees holes in it, I'm very happy to get suggestions. Like I say, I just roughed it out this weekend, and am a businessman not a programmer, so I don't have ego in my code, just a desire to be more efficient and effective).
ItemNum:: on Element Change ...
// A- find the matching Pricing record and pull in ItemDesc/UoM/Cost/Price; if no Pricing record, then
// B- find the matching Inventory record and pull in ItemDesc/UoM/Cost, and tell user to enter price manually
#include "sbasic_include.sbas"
// first group is for vRS1
var v1ItemNum as string
var v1ClientID as String
var v1ICPid as String
var v1ClientPrice as double
var v1ItemDesc as string
var v1Cost as double // 12:45am Pull in Pricing data
var v1UoM as string
var v1ItemCat as string
var v1ClientName as string
// second group is for vRS2
var v2ItemDesc as string
var v2ItemCost as double
var v2ItemUoM as string
var v2ItemCat as string
var v2ItemNum as string
var v2ClientID as string
var v2ICPid as string
var v2ClientPrice as double
var v2ClientName as string
// third group is for vRS3
var v3ItemNum as string
var v3ClientID as string
var vRS1 as int
var vRS2 as int
var vRS3 as int // handle to verify that ItemNum exists in Inventory, and if not, tells the user to fill in all the appropriate fields and
// the Inventory record will be created when they save this line item
vRS1 = 0
vRS2 = 0
vRS3 = 0
If @Mode() = 0
{
SONum = @FORMFIELDVALUE("Orders","SONum",0) // this first section fills in SONum, SODate, ClientID, ItemCustPriceID
SODate = @FormFieldValue("Orders", "SODate", 0)
ClientID = @FormFieldValue("Orders", "ClientID", 0)
ClientName = @FormFieldValue("Orders", "ClientName", 0)
v3ItemNum = ItemNum
v3ClientID = ClientID
ItemCustPriceID = (v3ClientID + "+" + v3ItemNum)
v1ItemNum = ItemNum
v1ClientID = ClientID
v1ClientName = ClientName
ItemCustPriceID = (v1ClientID + "+" + v1ItemNum)
v1ICPid = ItemCustPriceID
// **** vRS1 **** This section will find the matching ItemCustPriceID in Pricing and plug that
// ClientPrice into the SOLines!Price field
vRS1 = @XResultSetSearch(@FN, "Pricing", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!ItemCustPriceID=" + v1ICPid)
If vRS1 > -1
{
// Confirm exactly one matching record
If @XResultSetTotal(vRS1) = 1
{
// Confirm that the Pricing record is not locked
If @XResultSetLocked(vRS1) = 0
{
// Get the ItemDesc, UoM, Cost, and ClientPrice to plug into this SOLines record
v1ItemDesc = @XResultSetValue(vRS1, "ItemDesc")
v1UoM = @XResultSetValue(vRS1, "UoM")
v1Cost = @XResultSetValue(vRS1, "Cost")
v1ItemCat = @XResultSetValue(vRS1, "ItemCat")
v1ClientPrice = @XResultSetValue(vRS1, "ClientPrice")
FORMFIELDVALUE("SOLines","ItemDesc", 0, v1ItemDesc)
FORMFIELDVALUE("SOLines","ItemUoM", 0, v1UoM)
FORMFIELDVALUE("SOLines","ItemCost", 0, v1Cost)
FORMFIELDVALUE("SOLines","ItemCat", 0, v1ItemCat)
FORMFIELDVALUE("SOLines","ItemPrice", 0, v1ClientPrice)
Margin = (1-(v1Cost/v1ClientPrice))
ThrowFocus(ItemQty)
}
Else
{
@Msgbox("PricingRecord is locked","enter data manually","") // If @XResultSetTotal(vRS1) = 0, then vRS2 runs next
}
}
If @XResultSetTotal(vRS1) = 0 // if there is not a Pricing record, then pull item data from Inventory!ItemNum record
{
v2ItemNum = ItemNum
vRS2 = @XResultSetSearch(@FN, "Inventory", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!ItemNum=" + v2ItemNum) // **** vRS2 **** starts here
If vRS2 > -1
{
// Confirm exactly one matching record
If @XresultSetTotal(vRS2) = 1
{
// Confirm that the Inventory record is not locked
If @XResultSetLocked(vRS2) = 0
{
// Get the ItemDesc, Cost, UoM and plug into the appropo fields in this SOLines record
v2ItemDesc = @XResultSetValue(vRS2, "ItemDesc")
v2ItemUom = @XResultSetValue(vRS2, "ItemUoM")
v2ItemCost = @XResultSetValue(vRS2, "ItemCurrentCost")
v2ItemCat = @XResultSetValue(vRS2, "ItemCat")
FormFieldValue("SOLines", "ItemDesc", 0, v2ItemDesc)
FormFieldValue("SOLines", "ItemUoM", 0, v2ItemUoM)
FormFieldValue("SOLines", "ItemCost", 0, v2ItemCost)
FormFieldValue("SOLines", "ItemCat", 0, v2ItemCat)
ThrowFocus(ItemPrice)
@Msgbox("Not an existing Pricing record.","Enter Price manually, and ","a new Price record will be created when you press +Item button")
}
Else
{
@Msgbox("The Inventory Record is locked","Try again","")
}
}
If @XresultSetTotal(vRS2) = 0
{
@Msgbox("there is no Inventory record.","Enter all required data for this line item and +Item button will then build Inventory Record as line item is saved","")
}
}
XResultSetClose(vRS2) // closes Result Set 2 that was pulling in Inventory info because there wasn't a Pricing record
}
XResultSetClose(vRS1) // closes Result Set 1 that was pulling in Pricing record info
}
}
******************************************************
and AddThisLineItem:
n element entry ...
// C- this command will update Existing, or create a new, Pricing record, and then save the SOLines line item and create a new SOLines record.
// It will use XResult to find and update the existing Pricing record, or use XResult to create a new Pricing record and plug
// in all relevant data (ItemCustPriceID, ClientID, ItemNum/ItemDesc/ItemPrice/ItemCost/Margin/PorM/PriceDate
#include "sbasic_include.sbas"
var vTemp as Int // this will be used to @createNewRecord at the end
var vRS4 as int // handle to verify that ItemNum exists in Inventory
var vRS5 as int // handle for RecordSet to update existing or make new Pricing record
// this group below is variables for vRS4 in SOLines
var v4ItemNum as string
var v4ItemCat as string
var v4ItemDesc as string
var v4ItemUoM as string
var v4ItemPrice as double
var v4ItemCost as double
var v4ItemMargin as double
var v4ItemCustPriceID as string
var v4ClientID as string
var v4SODate as date
var v4ClientName as string
// this group below is for vRS5
var v5ItemNum as string
var v5ItemCat as string
var v5ItemDesc as string
var v5ItemUoM as string
var v5ItemPrice as double
var v5ItemCost as double
var v5ItemMargin as double
var v5ItemCustPriceID as string
var v5ClientID as string
var v5SODate as date
var v5ClientName as string
var v5PorM as string
var vp5ItemPrice as double
If (@Mode() = 0) // and (@IsBlank(ItemNum) = 0)
{
v4ItemNum = @FormFieldValue("SOLines", "ItemNum", 0)
vRS4 = @XResultSetSearch(@FN, "Inventory", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!ItemNum=" + v4ItemNum)
If vRS4 > -1 // if there is not an inventory record, then make one
{
If @XResultSetTotal(vRS4) = 0
{
v4ItemNum = @FormFieldValue("SOLines", "ItemNum", 0)
v4ItemCat = @FormFieldValue("SOLines", "ItemCat", 0)
v4ItemDesc = @FormFieldValue("SOLines", "ItemDesc", 0)
v4ItemUoM = @FormFieldValue("SOLines", "ItemUoM", 0)
v4ItemPrice = @FormFieldValue("SOLines", "ItemPrice", 0)
v4ItemCost = @FormFieldValue("SOLines", "ItemCost", 0)
v4ItemMargin = @FormFieldValue("SOLines", "Margin", 0)
v4ClientID = @FormFieldValue("SOLines", "ClientID", 0)
v4ClientName = @FormFieldValue("SOLines", "ClientName", 0)
v4ItemCustPriceID = (v4ClientID + "+" + v4ItemNum)
FormFieldValue("SOLines", "ItemCustPriceID", 0, v4ItemCustPriceID)
XResultSetCreateNewRecord(vRS4)
XResultSetValue(vRS4, "ItemNum", v4ItemNum)
XResultSetValue(vRS4, "ItemCat", v4ItemCat)
XResultSetValue(vRS4, "ItemDesc", v4ItemDesc)
XResultSetValue(vRS4, "ItemUoM", v4ItemUoM)
XResultSetValue(vRS4, "ItemCurrentCost", v4ItemCost)
}
}
XResultSetClose(vRS4)
}
If (@Mode() = 0)
{
vp5ItemPrice = @XResultSetValue(vRS5, "ClientPrice") // compare SOLines Price to Pricing Price
v5ItemPrice = @FormFieldValue("SOLines", "ItemPrice", 0)
v5ItemNum = @FormFieldValue("SOLines", "ItemNum", 0)
v5ItemCat = @FormFieldValue("SOLines", "ItemCat", 0)
v5ItemDesc = @FormFieldValue("SOLines", "ItemDesc", 0)
v5ItemUoM = @FormFieldValue("SOLines", "ItemUoM", 0)
v5ItemCost = @FormFieldValue("SOLines", "ItemCost", 0)
v5ItemMargin = @FormFieldValue("SOLines", "Margin", 0)
v5SODate = @FormFieldValue("SOLines", "SODate", 0)
v5ItemCustPriceID = @FormFieldValue("SOLines", "ItemCustPriceID", 0)
v5ClientID = @FormFieldValue("SOLines", "ClientID", 0)
v5ClientName = @FormFieldValue("SOLines", "ClientName", 0)
v5PorM = @FormFieldValue("Orders", "PorM", 0)
vRS5 = @XResultSetSearch(@FN, "Pricing", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!ItemCustPriceID=" + v5ItemCustPriceID)
If vRS5 > -1 // if there IS a matching Pricing record and the SOLine Price doesn't match the Pricing Price, then plug
// the SOLines price and cost and marg and date into Pricing record
{
If @XResultSetTotal(vRS5) = 1
{
If v5ItemPrice <> vp5ItemPrice // if SOLines!Price <> Pricing!Price, then update Pricing record with SOLines data
{
XResultSetValue(vRS5, "ClientPrice", v5ItemPrice)
XResultSetValue(vRS5, "Cost", v5ItemCost)
XResultSetValue(vRS5, "Margin", v5ItemMargin)
XResultSetValue(vRS5, "PriceDate", v5SODate)
XResultSetValue(vRS5, "PricingNoteField", "hello2")
}
}
If @XResultSetTotal(vRS5) = 0 // if there isn't a matching Pricing record, then create a Pricing record and plug in data from SOLines
{
v5ItemCustPriceID = @FormFieldValue("SOLines", "ItemCustPriceID", 0)
XResultSetCreateNewRecord(vRS5)
XResultSetValue(vRS5, "ItemCustPriceID", v5ItemCustPriceID)
XResultSetValue(vRS5, "ClientID", v5ClientID)
XResultSetValue(vRS5, "ClientName", v5ClientName)
XResultSetValue(vRS5, "PriceOrMargin", v5PorM)
XResultSetValue(vRS5, "ItemNum", v5ItemNum)
XResultSetValue(vRS5, "ItemDesc", v5ItemDesc)
XResultSetValue(vRS5, "ItemCat", v5ItemCat)
XResultSetValue(vRS5, "ClientPrice", v5ItemPrice)
XResultSetValue(vRS5, "Cost", v5ItemCost)
XResultSetValue(vRS5, "UoM", v5ItemUoM)
XResultSetValue(vRS5, "Margin", v5ItemMargin)
XResultSetValue(vRS5, "PriceDate", v5SODate)
XResultSetValue(vRS5, "PricingNoteField", "Hello new record")
}
XResultSetClose(vRS5)
}
vTemp = @CreateNewRecord()
ThrowFocus(ItemNum)
}
Pages: 1
Correct coding procedure? (Read 790 times)
