Programming Developers Diary 17

April 13th, 2017

Hello everyone. Wow has it been a busy month. My last post was on March the 13th and a lot has happened since then. Many of you know I have always said to be successful as a custom application developer you must give the customer more that they expect, which is what I always try to do. Not only does it make you the developer look good, but it make them, (the customer), see the potential for their application. The last thirty days has been a living, breathing example of just that.

One of the upgrades I am doing is in inventory control. A fairly common business model is buy low and sell high which in it self is very straight forward. When you have slow moving inventory, knowing the actual cost of your inventory on hand becomes more complicated. As an example, say you have four parts (fan motors) in stock originally purchased for25.00 each two years ago. You just received an order needing 5 fan motors for a customer job. You contact your supplier and find the motors went up to 50.00 each and the minimum order is 10 for that price point. You place the order and receive the motors now having fourteen in stock. The value of your inventory is

     4 X 25 = 100.00

+10 X 50 = 500.00

Total          600.00

What is your customers cost per unit? Answer is

600 / 14 = 42.86 per motor.

Currently my customer computes the cost by hand and updates the inventory table manually. They want it to happen from the receiving form automatically. In their inventory table they have no field for tracing their cost for new purchases, only an inventory value field. So to give them what they want I am using the lookup function in combination with some variables and a high level posting operation. Since they may be ordering several parts from the manufacturer, I use the form tables method to fetch through the records comprising the specific order. The code below is attached to a button on the form which runs on the onPush event.

parentform.commit()
Post_Incoming_Parts:Tables:Reqdetail.Batch_Begin()
Post_Incoming_Parts:Tables:Reqdetail.Fetch_First()
while .NOT. Post_Incoming_Parts:Tables:Reqdetail.Fetch_Eof()
    Post_Incoming_Parts:Tables:Reqdetail.change_begin()
           Post_Incoming_Parts:Tables:Reqdetail.ON_HAND = lookupn("F",Post_Incoming_Parts:Tables:Reqdetail.Part_Num,"On_Hand","inventry","Part_Num")
    Post_Incoming_Parts:Tables:Reqdetail.change_end(.t.)
    Post_Incoming_Parts:Tables:Reqdetail.Fetch_Next()
end while
Post_Incoming_Parts:Tables:Reqdetail.Batch_End()
Post_Incoming_Parts:browse1.commit()
xbasic_wait_for_idle()
Post_Incoming_Parts:browse1.Activate()
Post_Incoming_Parts:browse1.Resynch()
parentform.Commit()
parentform.Refresh_Layout()

END

Because the inventory is fluid and changes daily the check on hand button looks at each item ordered in inventory and uses the lookup function to to update the current on hand field. Because each record in the detail table has a unique part number I use an absolute reference for the A Key Value portion of the lookup function. This insures each record fetched supplies the correct Part Number thus returning the correct on hand value from inventory.

Next the user clicks Post Receivables.

Req_ord_nbr = Req_No.text
a_tbl = table.open("inventry")
post.t_db = "reqdetail"
post.m_key = "Part_Num"
post.t_key = "Part_Num"
post.m_filter = ""
post.t_filter = "Req_No=Var->Req_ord_nbr"
post.m_count = 6
post.m_field1 = "ACT_COST"
post.m_exp1 = "if(On_Hand=0,@Reqdetail->ACT_Cost,((@Reqdetail->ACT_Cost*@Reqdetail->Qty_Posted+@ReqDetail->On_Hand*ACT_Cost))/(@Reqdetail->Qty_Posted+@ReqDetail->On_Hand))"
post.m_field2 = "Superceed"
post.m_exp2 = "@REQDETAIL->Superceed"
post.m_field3 = "Descript"
post.m_exp3 = "@REQDETAIL->Descrip"
post.m_field4 = "Wholesale"
post.m_exp4 = "@REQDETAIL->Wholesale"
post.m_field5 = "Date"
post.m_exp5 = "@REQDETAIL->Date"
post.m_field6 = "STOCK"
post.m_exp6 = "@REQDETAIL->Stock"
post.t_count = 0
a_tbl.post()
a_tbl.close()

Now you can see that since the on hand is available in reqdetail a simple formula on the Act_Cost field expression averages the part cost on the fly just as the customer requested.

Let’s say you have a customer which builds parts into a finished product. Part prices change as well as the labor to build the finished product. Table sum  function can be used as well.

dim SHARED xCnt as N
dim SHARED xTrC as N
xCnt = tablesum("inventry","Part_Num= '" + Var->xPNbr + "' ","On_Hand")
xTrC = ((tablesum("inventry","Part_Num= '" + Var->xPNbr + "' ","ACT_Cost")*xCnt)+NewTC)/(xCnt+val(xOnHand))
a_tbl = table.open("inventry")
post.t_db = "quotes"
post.m_key = "Alltrim(Part_Num)"
post.t_key = "Alltrim(Model_Num)"
post.m_filter = ""
post.t_filter = "Alltrim(Model_Num) = Var->xPNbr"
post.m_count = 5
post.m_field1 = "ON_HAND"
post.m_exp1 = "ON_HAND+val(Var->xOnHand)"
post.m_field2 = "AVAILABLE"
post.m_exp2 = "AVAILABLE+val(Var->xOnHand)"
post.m_field3 = "ACT_COST"
post.m_exp3 = "Var->xTrC"
post.m_field4 = "WHOLESALE"
post.m_exp4 = "if(Var->NewTC>Wholesale,Var->NewTC,WHOLESALE)"
post.m_field5 = "LIST"
post.m_exp5 = "if(Var->NewTC/.70>List,Var->NewTC/.70,LIST)"
post.t_count = 0
a_tbl.post()
a_tbl.close()

Here I declare two variables xCnt and xTrC and use tablesum to build the values needed from inventory to complete the averaging equation. I then use the posting operation to add the finished product to inventory. Very simple.

Now I have said it before and it’s worth saying again.

Complex problems do not always require complex solutions.

It’s your job as the developer to know when and if a simple solution will work.

That’s all for today. If you own a business and need help with your Alpha Software program, contact us. Are rates are reasonable and our work is guaranteed.

Phone:713 417-6831

EMail: NLawson@cdc-takecharge.com