Copy Master Detail to another table

ameisfine · Post by **ameisfine** » Fri Oct 27, 2017 11:55 am

Hi,
I have Master/Detail (Proforma Invoice). I need to have a function or button that when I click Copy, it goes to Master/Detail (Real Invoice) with new invoice number.

I have Proforma Invoice table/page
In Master List page, there are buttons

Master/Detail View
Master/Detail Edit
Master/Detail Copy

I want to manipulate Master/Detail Copy to do that current action but automatically copy to Real Invoice page/table. Both tables are having same fields, only the Invoice_Number for Real Invoice should be new and different.

Hopefully my better explanation.

mobhar · Post by **mobhar** » Sat Oct 28, 2017 11:15 am

ameisfine wrote:
I want to manipulate Master/Detail Copy

Then simply put the following code in "ListOptions_Rendering" server event (assume it uses "orders" table from demo project):

$this->CopyUrl = "mypage.php?OrderID=".$this->OrderID->CurrentValue;

dh1340 · Post by **dh1340** » Sat Oct 28, 2017 1:45 pm

Hi, I have a very similar problem :
but I dont have in Server Events..table Specific..List Page.. List Option Rendering ?
There is only ListOptions_Load or ListOptions_Rendered

and is there a way to add another button like Master/detail/copy
like Master/Detail/Copy TO
so I dont loose the normal Copy function.

THX
PhPMaker Version 2017.07

mobhar · Post by **mobhar** » Sat Oct 28, 2017 2:57 pm

dh1340 wrote:
but I dont have in Server Events..table Specific..List Page.. List Option Rendering ?

Make sure you always use the latest version, as of today is version 2018.

dh1340 wrote:
ist there a way to add another button like Master/detail/copy

You may simply use "ListOptions_Load" and "ListOptions_Rendered" server event, for example:

// ListOptions_Load:
// Example:
$opt = &$this->ListOptions->Add("my_new_button");
$opt->Header = "Master/Detail Copy TO";
$opt->OnLeft = TRUE; // Link on left

// ListOptions_Rendered:
// Example:
$this->ListOptions->Items["my_new_button"]->Body = "<button type=\"button\" class=\"btn btn-default btn-sm\" title=\"Master/Detail Copy TO\" onclick=\"window.location='home.php'\">Master/Detail Copy TO</button>";

For more info and example, please read "Server Events and Client Scripts" topic from PHPMaker Help menu.

dh1340 · Post by **dh1340** » Sun Oct 29, 2017 4:54 pm

HI, ok now I am on 2018.04

My structure is:
Source Tables :
Master Table : offers
Detail Table : products1

Target Tables :
Master Table : invoices
Detail Table : products

The Master and the Detail Table have the same structure

What Ive got so far:

table offers :
// ListOptions Load event
function ListOptions_Load() {
// Example:
$opt = &$this->ListOptions->Add("my_new_button");
$opt->Header = "Zur Rg";
$opt->OnRight = TRUE; // Link on left
//$opt->MoveTo(0); // Move to first column

}

// ListOptions Rendered event
function ListOptions_Rendered() {
// Example:
//$this->ListOptions->Items["new"]->Body = "xxx";
$this->ListOptions->Items["my_new_button"]->Body = "<button type=\"button\" class=\"btn btn-default btn-sm\" title=\"Master/Detail Copy TO\" onclick=\"window.location='invoicesadd.php?showdetail=products1&id=43'\">Master/Detail Copy TO</button>";

}

// ListOptions Rendering event
function ListOptions_Rendering() {
//$GLOBALS["xxx_grid"]->DetailAdd = (...condition...); // Set to TRUE or FALSE conditionally
//$GLOBALS["xxx_grid"]->DetailEdit = (...condition...); // Set to TRUE or FALSE conditionally
//$GLOBALS["xxx_grid"]->DetailView = (...condition...); // Set to TRUE or FALSE conditionally
$this->CopyUrl = "mypage.php?id=".$this->id->CurrentValue;

}

Result so far :
The Button "Master/Detail Copy TO" appears, but only y produces a invoices add page ( Master only )

so any ideas would get me lucky ;)

THX

mobhar · Post by **mobhar** » Mon Oct 30, 2017 9:39 am

dh1340 wrote:
but only y produces a invoices add page ( Master only )

Double check your master/detail table relationship. From your previous post above, it seems "invoices" (master) has the relationship to "products" and not to "products1".

So, change this:
invoicesadd.php?showdetail=products1&id=43

to:
invoicesadd.php?showdetail=products&id=43

ameisfine · Post by **ameisfine** » Mon Oct 30, 2017 11:25 am

Thanks Mobhar.
I tried couple ways still no luck. I have add button with action 'copy to ' but how to view (copy) data from the source page?

Source Page = a_proformaadd.php?showdetail=a_proforma_detail&Sales_ID=17
Target Page = a_salesadd.php?

How to add 'showdetail=a_proforma_detail&Sales_ID=17' into 'a_salesadd.php' URL ?

mobhar · Post by **mobhar** » Mon Oct 30, 2017 11:33 am

Please try to make the master/detail relationship also, between "a_sales" (master) table with "a_proforma_detail" (detail) table. If it does not work either, then you should handle the process by using "Grid_Inserted" server event.

Please read "Server Events and Client Scripts" topic from PHPMaker Help menu for more information.

dh1340 · Post by **dh1340** » Mon Oct 30, 2017 5:04 pm

mobhar wrote:
dh1340 wrote:
but only y produces a invoices add page ( Master only )

Double check your master/detail table relationship. From your previous post above, it
seems "invoices" (master) has the relationship to "products" and
not to "products1".

So, change this:
invoicesadd.php?showdetail=products1&id=43

to:
invoicesadd.php?showdetail=products&id=43

Ok tried this, but that takes the data from products ( which is already the data from existing invoices/products) which have ID =43
and not inserting / copying the data from offers/products1 to invoices/products as new data.....
the add of relations to invoices/products1 is not working allthough....

IÂ´m lost for the moment....

mobhar · Post by **mobhar** » Mon Oct 30, 2017 6:26 pm

Then the closest approach for this by using "Grid_Inserted" as I mentioned above.

"Grid_Inserted" for use with Grid-Add for a table and Master/Detail-Add for a detail table, this event will be called after inserting all records. The argument of the event ($rsnew) is array of records inserted (retrieved from database).

For example, put this code in that event:

foreach ($rsnew as $row) {
if (!empty($row[1])) {
ew_Execute("INSERT INTO <your_target_detail_table> VALUES ('".$row[0]."', '".$row[1]."', ... )");
}
}

In other words, to trigger this event, you need to do at least "Grid-Add" action in your origin detail table, so that all the records in that Grid-Add will be inserted into your target detail table.

ameisfine · Post by **ameisfine** » Wed Nov 01, 2017 10:36 am

The problem is the new Sales Number for master/detail of the particular record. By opening a new addpage I can use Row_Rendered to create auto next_salesnumber. Because not all proforma invoices will be real invoice, only some selected.

What I need is just simply like master/detail copy (to other page), but yeah, still not find the way. Connecting master (a_sales) to detail (a_proforma_detail) is also not working.

Thank you again mobhar for kind help.

mobhar · Post by **mobhar** » Wed Nov 01, 2017 11:00 am

ameisfine wrote:
Because not all proforma invoices will be real invoice, only some selected.

It does not matter. If you used "Grid_Inserted" (for Grid-Add) or "Grid_Updated" (for Grid-Edit) before, then you should already have the records in your target detail table.

You may then select some proforma invoices from original table, and then updating only those selected records in the target detail table using "Grid_Updated" that belongs to your origin detail table, afterwards, redirect your user to target Master/Detail Edit page only for those selected records, and you may also create your auto next_salesnumber in that page.

dh1340 · Post by **dh1340** » Wed Nov 01, 2017 3:47 pm

I just changed my workflow ;)

So I use just one table for offers and invoices ( and details _ products) and just add a field with "Status" which is than
rather "offer" or " invoice" (pro_forma_inv or inv )

tHX

ameisfine · Post by **ameisfine** » Wed Nov 01, 2017 5:08 pm

I just think the same. But how to differ between proforma_number and invoice_number ? What is your primary key ? can you share your solution?

mobhar · Post by **mobhar** » Wed Nov 01, 2017 5:37 pm

ameisfine wrote:
But how to differ between proforma_number and invoice_number ?

dh1340 wrote:
just add a field with "Status" which is than rather "offer" or " invoice" (pro_forma_inv or inv )

dh1340 · Post by **dh1340** » Wed Nov 01, 2017 9:59 pm

ameisfine wrote:
I just think the same. But how to differ between proforma_number and invoice_number ?
What is your primary key ? can you share your solution?

First of all : I use a separate id as primary key, cause the INV No needs do be set manually,
an therefore you can take as INV No what you like f.e. INV 0815P for Proforma and 0815 as Real INV.
And than the status field for INV_P or INV. to filter this.
thx

ameisfine · Post by **ameisfine** » Mon Nov 06, 2017 3:08 pm

dh1340 wrote:
First of all : I use a separate id as primary key, cause the INV No needs do be set
manually,
an therefore you can take as INV No what you like f.e. INV 0815P for Proforma and
0815 as Real INV.
And than the status field for INV_P or INV. to filter this.
thx

Now I try to use 1 table, and put this code in Add/Copy Page > Startup to automate the Invoice Number
$("input[name='x_Is_Proforma']").change(function() {
if (this.value == "Y") {
$("#x_Sales_Number").val("PROFORMA");
} else if (this.value == "N"){
$salesnum = GetNextSalesNumber();
$("#x_Sales_Number").val($salesnum);
}
});

But val($salesnum); not working... what's correct syntax? Thx

mobhar · Post by **mobhar** » Mon Nov 06, 2017 3:22 pm

ameisfine wrote:
$salesnum = GetNextSalesNumber();

Is this PHP code? If so, then make sure you have already enclosed it with <?php ... ?> tags.

ameisfine · Post by **ameisfine** » Mon Nov 06, 2017 5:20 pm

Sorry for my low level knowledge of syntax. I tried still not succeed. if (this.value == "N"), I want to call function GetNextSalesNumber() (I use structure and files from 'phpstock' sample). Thanks

mobhar · Post by **mobhar** » Mon Nov 06, 2017 5:53 pm

ameisfine wrote:
I tried still not succeed.

Always post your latest code for more discussion.

Copy Master Detail to another table

Re: Copy Master Detail to another table

}

}

}