Insert Into Help

[TGrahmann]

Hello, all. Some of you may remember that I was having some issues inserting records and updating records in tables a few weeks ago, I have since learned how to use INSERT INTO t (x,y,z) VALUES v1,v2,v3 FROM othertable

BUT, with the use of this command, I need to know how to use a set-value, for instance a reference number that is not included in the first table, to be copied to the other.
What I mean by this is, I have a table called NEXT, and in NEXT are all the "Next Numbers" for the program (invoice, order ref., customer ref., etc...), the place this code is being implemented is in an order entry screen, the order ref # is displayed in a text box on the form, but is not in the temporary table that is used by the user (in a grid) to enter the order. TempOrderProc is the name of the table that is used as a temporary holder for an order that is being created (the one in the grid), users enter the amount to order, etc in this grid. After this process is complete, I need to use the INSERT INTO... statement above that I've written, But I need to include the Order Ref # that is NOT in the temporary table into the second. Any ideas?
P.s. Sorry for the long post!

 

[vgulielmus]

Not sure if I understood exactly your request.
But I believe you can use a subquery, like:

Where thefield NOT IN (SELECT thefield1 FROM tobeecluded)

CREATE TABLE cnext (ii I)
INSERT INTO cnext VALUES (1)
INSERT INTO cnext VALUES (3)
INSERT INTO cnext VALUES (2)
INSERT INTO cnext VALUES (5)

CREATE TABLE cTempToAvoid (jj I)
INSERT INTO cTempToAvoid VALUES (1)
INSERT INTO cTempToAvoid VALUES (2)
INSERT INTO cTempToAvoid VALUES (4)
INSERT INTO cTempToAvoid VALUES (6)

CREATE TABLE cinto (kk I)

INSERT INTO cinto SELECT MIN(ii) FROM cnext WHERE ii NOT in (SELECT jj FROM cTempToAvoid)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Mike Lewis]

I'm not sure I understand it either.

Perhaps you mean that you want to transfer all the values from the temporary table to the orders table. Plus, you also have an Order Ref field in the orders table that has no equivalent field in the temporary table, and you want to fill that field with a value from a textbox.

If so, this will do it:

lcNewVal = THISFORM.Text1.Value
INSERT INTO Orders (Order_Amt, Cust_ID, Order_Ref) ;
  SELECT Order_Amt, Cust_ID, lcNewVal FROM Temp

In other words, the expressions in the SELECT clause can be values other than fields in the table that you are selecting from.

Does that answer the question?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

By the way, are you using the Next table to generate consecutive unique integers to use as primary keys? Where the table holds the next value for a given key (e.g. Order Ref), this being one greater than its previous values?

If so, then you'll find it very much easier to use an Integer (Autoinc) as your primary key or unique identifer. That way, VFP will do the necessary work for you, and you won't need a Next table.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I think you have a different problem than Vilhelm-Ion addressed.

You want to create the order ref no, being the order primary key, right?

Well, that's typically the job of the database and only created with the insert via the default value. The easiest to do is create an inter autoinc field, also this is about buffering changes, you don't need to insert a grid cursor into a table, you use the buffer to buffer not yet complete orders and order items, like this:

* Tables (should already exist of course), here using cursors just for demonstrating the principles
Create cursor Orders (iID I AutoInc, dOrderDate D Default Date(), iClerkID I)
Create cursor OrderItems (iID I AutoInc, iOrderID I, iProductID I, iAmount I Default 1)
* Use Buffering (later this is automatic when you implement form load code doing this general for all workareaas)
Set Multilocks On
CursorSetProp("Buffering",5,"Orders")
CursorSetProp("Buffering",5,"OrderItems")

* App Init
#Define yourappclass "empty" && should be changed to your appllication class
goApp=CreateObject(yourappclass)
* Clerk Login, assume clerk 42 logs in
Addproperty(goApp,"iLoggedinClerkID",42) && this property should be set at the log in of a clerk into your app

* Form Load code (after the form datasession exist and before any tables are opened in the DE, means Form.Load() event)
Set Multilocks On
Cursorsetprop("Buffering",5,0) && all workareas are table buffered (also locked optimistic, that means no locks until writes)


* A new order starts (eg in form init or in a button "new order")
Insert Into Orders (iClerkID) Values (goApp.iLoggedinClerkID) && other fields are set automatic, order date by default value and iid by autoinc
* The form may display some order attributes (even some I did not mention here, whatever head data)

* Add order item (eg add item button calls a form for choice of a product):
* Do Form productchoice To liChoosenProductID, let's skip this partt and simply set liChoosenProductID=21
liChoosenProductID = 21 && may also be 0 or whatever form choice
If !Empty(liChoosenProductID) && clerk did not cancel product choice (in which case the form returns 0)
   Insert Into OrderItems (iOrderID, iProductID) Values (Orders.iID, liChoosenProductID)
Endif
* Now the clerk may add more items or change items amount, etc.
* The grid may display product details like name and price by a SET RELATION from order items to the product table (don't add redundant infos into the orderitems table)

*Finally save:
liInitialTransactionLevel = TxnLevel()
Begin Transaction
If Tableupdate(1,.f.,"Orders") && save the order head record
   If Tableupdate(1,.f.,"OrderItems") && save the order items
      End Transaction
   Endif
Endif
If TxnLevel()>liInitialTransactionLevel
   * Something went wrong (network error, hdd error, some conflict)
   Rollback
Endif

If you run this, you have one order, then run just the part from *App Init onwards and you have a second (of course similar) order each with one order item.

There are some essential things to understand this:

1. You let the db create primary keys, they are created buffered, not immidately saved into the DBF, yet other clerks won't get the same id, even if it takes many minutes before the order is completed and saved. That's vbery essential aqdvantage of the autoinc fields, their counter is maintained in the dbf header, which is updated immidiately always, even in buffered mode.
2. Set Multilocks On: Needed for table buffering, for buffering multiple record in one table. Actually this sample makes no use of this, but in general you will have more than 1 order item
4. Buffering Setting: I do this for workarea 0, means all future opened tables or cursors or views are table buffered, needs to be done AFTER the private datasession exists and BEFORE tables are opened, so ideally in Form Load.
5. goApp.iLoggedinclerkID: Why? Such an information is often needed in many places in your app: What is the current user? Here it is stored in the order to see which clerk put the order
6. Insert Into Orders: As said in (2) the clerk is mainained here, but you also need the iID for order items. There is no goApp property needed for that, no public nor private variable, you can always address the current order iID, as the Insert also puts the record pointer to this new (and buffered) record, so your Orders table is your "variable" for the Order head data like its ID.
7. Insert Into OrderItems using Orders.iID is making use of the local record pointer positioned on the current buffered order record. You don't needd to be afraid parallel users move this, the record pointer is of your workarea, not of the table file, it's local.

All that said it's surely hard to comprehend all this just from this post, so please ask back.

The general idea is to NOT use a separate grid cursor. Actually in the most advanced version of how to do data entry you use a cursoradapter class which does create a separate cursor you use for the form grid, but that cursor is directly linked to the table (like an updatable view) and the save machanism therefore is exactly the same as when working with the table itself. I won't show this heree, as it adds even more complexity to the example without changing the general way to use buffers, transactions and TABLEUPDATE() to save a full order with all its items or nothing. Including creating the primary key (id, ref no) also needed as foreign key (reference of the order in order items).

That's the way you do it, mainly.
Cursoradapters or more general client/server and N-tier is another topic.
Conflict handling is yet another topic...

The main reason I show this is: You ARE programming buffering and how the buffer is added to the DBF etc yourself, you do what already is implemented in VFP, you do stuff not needed, you waste your valuable time for programming something that the system does for you. With a separate grid cursor you implement the buffering of data until you want to save it, but that's already a feature of workareas, you simply need to use it, not program your own buffering machanism via a separate cursor. Think about what situations you come across when editing an order, some of your cursor records then would need to go back into the DBF as Inserts, others as Updates and delted items also need to be deleted in the DBF, all this is done by the buffer and a single TABLEUPDATE() call. You better know buffers and TABLEUPDATE().

You may say it's fun to do this on my own, I can do it in my way, I have full control etc., but then you refuse to use something that exists for 15 to 20 years already and has proven to work in time and be secure and stable. Use the VFP internal features and learn about them.

What you'd need is more than a tutorial, you'd need a workshop, about a week learning with an instructor, not only about this topic.

Bye, Olaf.