Help needed to copy one set of tables to another

[Alastair]

I have a DB which is based on Alphasports supplied with Alpha 5 V5 (see attachment).

At the moment I enter the Customers order information into the Customers order form and then enter similar data into the Purchase order form. The data provided is only sample data and some of these orders can contain 40-50 items.

What I would like to do is have a button on one of these forms that I can press which automatically copies the necessary information from the Customers order form, to the Purchase orders form. It must be noted that each of the forms is based on parent/child tables with one to many relationships and I am sure I won't need to copy every field from the first parent/child table to the second as the tables have some field rules set.

I have looked into this using Genies and Xbasic, I have also found some code (from this messageboard) which allows me to duplicate a parent/child record in the same tables, but from there on my lack of knowledge is letting me down. Can anyone point me in the right direction?

The code I have so far can be found in the "on push" event on the "Copy order?" button of the Customer order form.

Thanks,

Alastair

[Tom Henkel]

Re: Help needed to copy one set of tables to another

How about creating 2 simple "append" operations, and then placing these operations under a button on the customer order form?

1st append is from Customer order to Purchase order, appending all necessary fields.

2nd append is from customer line item to purchase line item based on customer #.

The button would go on the customer order form and be titled "create Purchase order" Under the button, you would run 2 saved operations as described above.

There is obviously more to it like maintaining the integrity of the customer number and making sure the line items in the purchase order actually align with the customer in the purchase order, but this should get you started.

Tom

[Alastair]

Re: Help needed to copy one set of tables to another

Tom, thanks for your reply I have looked at an append operation, but it is keeping the relationship between the parent/child tables that is important. Still I will have another look at this, at least using the append command can be done through the Genies!

[Tom Henkel]

Re: Help needed to copy one set of tables to another

What you would do with the append, is to create a purchase order record from the client record (table to table), not within the set. Then,create an append of that customer order line item table to the purchase order line item table. To do this, you may need to find out the PO number, and pass that into the PO append. That will maintain your integrity.

[Alastair]

Re: Help needed to copy one set of tables to another

Tom, the append operation may do this but I am struggling a bit! I only want to append the current order items that are shown on the form. The append operation doesn't seem to let you filter the fields for the currently displayed records on a form, or am I missing something?

[Tom Henkel]

Re: Help needed to copy one set of tables to another

Remember, I said that you will need to identify this order in some way, then pass that identifier into the append filter. This can be accomplished by using ask variables in the append filter.

Filter:

Cust_no = Var->askn_Customer_Number .and.order_date = var->askd_Order_date

[Alastair]

Re: Help needed to copy one set of tables to another

Tom, forget my last post I have this sorted now (the filtering is done from the button), but I have another problem!

The append command works as far as it goes, but for some reason the field rules for the table I am copying to aren't being followed when the append operation is used.

Is there a way around this?

[Tom Henkel]

Re: Help needed to copy one set of tables to another

Append operations don't obey field rules. My guess is that the thinking behind that is that the person building the append knows what they are doing, so no need to inforce.

What isn't happening?

[Alastair]

Re: Help needed to copy one set of tables to another

I am learning fast! I am nearly there with the header table, except for the fact that because the field rules don't work I cannot get it to append the -01, -02 to the job_ref to create the order no. However I have got this to work by using a string expression in the append operation e.g. alltrim(job_ref)+"01", not really what I want but its nearly there!

However, with the field rules not working I cannot use the append query to copy from the invoice_items table to the order_items table because the order_no (which is made up from job_ref+(a count of the orders) e.g. 1234-01 does physically not exist in the invoice_items table as a field! It needs to be copied from the parent table somehow??!!

To see what I mean try manually entering an order in the purchase orders form in the database attached to the first post in this thread - this may explain the problem better than I am doing here!!

Press the second button down from the top of the main menu then fill in each of the header fields most will either auto fill or provide drop down boxes (done by using field rules).

Then fill in a couple of line items for this order in the subform (browse) you don't need to enter item numbers as this is automatically entered (again using field rules) after the rest of the row is filled in.

Is there any other way of doing this? I'm open to any suggestions!

[Tom Cone Jr]

Re: Help needed to copy one set of tables to another

Alastair,

I haven't looked at your sample database so forgive me if these are stupid questions:

1) Why are you assigning new order numbers when copying records from the Customers order form to the Purchases Order form?

2) Why is your order number being used for two purposes? (to uniquely identify the order, and to tell the user a sequence number) This almost always leads to trouble. In my experience database fields should be used (strictly) for a single purpose.

3) Why are you using two tables in the first place? Couldn't the status of an order be tracked using a single field in one table?


Having said all that I hasten to add that you can certainly achieve your goal but it will involve a fair amount of custom coding. Similar examples have been posted within the last month or two.

Later...

Here's the thread I was thinking of:
http://msgboard.alphasoftware.com/al...ad.php?t=67763

-- tom

[Alastair]

Re: Help needed to copy one set of tables to another

Tom,

Thanks for your reply, sorry for my late response.

The Job No is our internal reference for each order we take.

The order number isn't different except for the last 2 digits see below:

e.g. If our Job Number was 1234 the first order for the items we need to order in to fulfill this would be 1234-01 and would increment each time we place another order. 1234-02, 1234-03 etc... The reason for doing this is to distinguish between different orders but still maintain the traceability back to the original job number.

I am not saying this is the best method but this has been developed from the companies methods of ordering before we started using databases.

[Alastair]

Re: Help needed to copy one set of tables to another

Tom,

I have had a look at the example you gave me on the link above, it looks like I am in for some reading up on XBasic! I have only used it for basic tasks before and this code is a mystery to me at the moment!

How do you know how many tables you have open on a form to use statements such as "t2 = table.current(2) 'pointer to SpecRev table"?

Is there any good reference material on the web with example Alpha five code to help us xbasic novices?

My database is different to the one shown in the example, the data is on two seperate forms could this code still work with modifications?

[Alastair]

Re: Help needed to copy one set of tables to another

Can anyone help me with this - still struggling with this Xbasic

[Tom Cone Jr]

Re: Help needed to copy one set of tables to another

Pls confirm that the "Customer order form" you are talking about is the form called "Orders".

Later...

The Purchase Orders form is based on the Orders set. In that set the Vendors table is linked as child to the order_header table. This suggests that no matter how many items are included in an order they all come from the same vendor. Is this right? In the Invoice set, supporting the "Orders" form for your "customer information" the Vendors table is linked to the items (not the header). The two set structures do not seem to be compatible. What am I missing?

Tell us what fields from which tables you want to copy over to the Orders set.

Thanks.

[Alastair]

Re: Help needed to copy one set of tables to another

Hi Tom,

Sorry for the late reply I have had a lot of problems with my PC today! The latest problem is I have just updated Alpha Five to the latest update and all my reports that were working yesterday are all messed up!!!??? Has anyone else got this problem?

Anway back to the original question...

The Order form is the form which details the customers order items to us.

The Purchase Orders form is the form which details our order to our supplier, they don't both hold the same information necesarily due to us stocking some items.

The form called "Order" is based on the invoice_header table this has a subform (browse) based on invoice_items table, this form is virtually identical to the Alphasports form with a few alterations here and there.

The form called "Purchase Orders" is based on the order_header table this has a subform (browse) based on order_items table.

What I would like to do is press a button on the Order form and copy the invoice_items information along with the Job number from the invoice_header table, so it would make a duplicate (almost) entry in the Purchase Orders form.

When this has been duplicated into the Purchase orders form I would then be able to go into this form and edit items e.g. delete items we already have in stock.

Sorry for the long explanation and thanks for looking at this!

Alastair