How to post data from a single form to several tables?

[fisol]

I work on the following tables:
1.Client table:
Single field:"Name","Alpha","size40","keyed"
2.Product(Stock) table:
Single Name",field:"Product "Alpha","Size40","keyed"
3.Contacts table:
2 fields. (a)"Date","keyed" and (b)"Client name","Alpha, size40","lookup client table","keyed".
4.Line items table:
3 fields. (a)"Date","keyed", (b)"Product Name","Alpha size 40","keyed","lookup Product(stock) table" and (c)"Quantity","Numeric"

I enter data using "Form A". The DataModel consists of "Contact table" and "Line item table". The Date field of Contact Table is linked to the Date field of Line Item table.

Form A(Example)

Date : 4/18/2003
Client : John Doe

---------------------------------
| Product Name | Quantity|
---------------------------------
| Product 1 | 20 |
---------------------------------
| Product 2 | 35 |
---------------------------------
| Product 3 | 100 |
---------------------------------
I want to post the data so that the "date","client name" and "quantity" go to the appropriate product tables so that the stock quantities get updated immediately. Hope I make some sense here. Will appreciate some helps.

 

[footpad]

fisol,

As you might expect, there are several ways you can approach this, depending on your data management needs. The best approach depends on a variety of factors.

For example, do you have a way of "closing" an order to prevent additional edits? For example, many companies will allow an order to be edited until it's shipped to the customer. After that, adjustments to the order need to be handled separately. If your organization has something linke this, then you might be using a ShipDate field to indicate the difference between an an open order and a closed one.

In this case, you would determine the value of the ShipDate field when the Order record is unlocked and update your inventory levels when that field has a value.

One way to do that would be to add code to the action() event of your Order table's record object. Something like this would serve as a start:

switch

   case eventinfo.id() = dataBeginEdit :

      msgInfo( "Can't Edit Order", "Reason: This order " + 
               "has already shipped.  Sorry." )
      eventInfo.setErrorCode( userError )

   case eventInfo.id() = dataUnlockRecord : 

      if not active.action( dataPostRecord ) then 
         eventInfo.setErrorCode( userError )
      endIf

   case eventInfo.id() = dataPostRecord : 

      if not ShipDate.isBlank() then
         updateInventory( OrderNo.Value )
      endIf

endSwitch[code]

In this case, the action() event prevents the user from editing closed order, ensures that the record is unlocked consistently, and calls a custom method for updating the inventory levels.

Your updateInventory() method might look like this:

[code]method updateInventory( liOrderID LongInt )
var
   tcLineItem,
   tcProducts  TCursor
endVar

   tcLineItem.attach( LineItem )
   tcProducts.open( ":WORK:PRODUCTS" )
   tcProducts.edit()
   scan tcLineItem : 

      Message( "Updating inventory for item ", 
               tcLineItem.recNo(), "..." )
      tcProducts.qLocate( tcLineItem."Product Name" )
      tcProducts."Quantity" = tcProducts."Quantity" -
                              tcLineItem."Quantity"
      tcProducts.unlockRecord()

   endScan
   tcProducts.endEdit()
   tcProducts.close()  ;// always close tCursors
   tcLineItem.close()  ;// always close tCursors
   Message( "Inventory updated...Ready for next action." )

endMethod

Now, this code is untested, so it may contain typos or other mistakes. however, I think it demonstrates the basic idea.

As an alternative, it might be better to maintain a StockLevel table designed to help add auditing to the process. The idea is to use the "checkbook register" metaphor to maintain transactions against your stock levels and then to use a summary form to determine the levels you're supposed to have on hand. This helps you when you manually review your inventory and determine why your system says you should have five widgets and you can only find four of them. (OK, that was a nasty sentence. Sorry.)

Hope this helps...

-- Lance