Row Updating lookup table

dh1340 · Post by **dh1340** » Thu Feb 15, 2018 5:49 pm

hello
PHPmaker 2017

So in table fach2018 the proj_id field is a lookup from projects incl. add new project

I need to copy from table fach2018 -> eventname to projects->projectname and start - end when select ADD

in table projects : row updating ???

$MyResult = ew_Execute("INSERT INTO projects (projectname, start, end) SELECT eventname, start, end FROM fach2018 WHERE MyField=XXX");

I dont get the condition...

THX a lot

mobhar · Post by **mobhar** » Thu Feb 15, 2018 11:42 pm

Double check and evaluate your tables schema and your logic for both tables.

It looks strange to use "projects" table as lookup table for "proj_id" field in "fach2018" table, and in the other side, you want to insert again the record into the "projects" table.

dh1340 · Post by **dh1340** » Fri Feb 16, 2018 3:46 pm

the tables are correct, as they come from different systems . naming is a bit weird but ok.

the workflow is: the table "fach2018" will be filled by customer ... in System 1.
Than a employee has to select this - and select or add a new project to the data from table "fach2018"
therefor the data : eventname|start|end from fach2018 needs to be copied into the table "projects"

otherwise they have to type it manually.

many thx

mobhar · Post by **mobhar** » Fri Feb 16, 2018 5:51 pm

To give us more info, please describe us by using a real data example; starting from input data in "fach2018" table, and how the intial data in "projects" table, until how a new record was added into the "projects" table that came from "fach2018" table. Give us the real example.

dh1340 · Post by **dh1340** » Fri Feb 16, 2018 9:55 pm

ok I try

My idea was :
Row updating in table "fach2018" like:

$MyResult = ew_Execute("INSERT INTO MyTable1 (Field1, Field2, Field3) SELECT Field4, Field5, Field6 FROM MyTable2 WHERE MyField=XXX");

exp : $MyResult = ew_Execute("INSERT INTO projects (projectname, start, ende) SELECT eventname, start, ende FROM fach2018 WHERE MyField=XXX");

but I dont know if this is a correct way ? and how to set Where=

mobhar · Post by **mobhar** » Sat Feb 17, 2018 12:50 am

dh1340 wrote:
this needs to be filled by function ADD -> lookuptable projects -> id_project

Did you mean you want to add the "projects" record by using Add-on-the-fly dialog modal from "project_id" field while editing the "fach2018" table?

dh1340 · Post by **dh1340** » Sat Feb 17, 2018 2:18 am

Hi mobhar,
yes correctly...

mobhar · Post by **mobhar** » Sat Feb 17, 2018 12:36 pm

You business logic is still weird to me, becuase, if you want to do that, then it will be like a circular reference table.

You want to insert a new record into "project" table which the information you will get from the current "fach2018" table. Moreover, the same data from "fach2018" you will have to input again in "project" Add-on-the-fly form.

So, the closest approach for this is by using "Row_Inserted" server event. When you insert a new record into "fach2018" table, then run ew_Execute() function to execute your SQL to insert the record into "projects" table.

sangnandar · Post by **sangnandar** » Sun Feb 18, 2018 7:27 am

dh1340 wrote:
the workflow is: the table "fach2018" will be filled by customer ... in System 1.
Than a employee has to select this - and select or add a new project to the data
from table "fach2018"
therefor the data : eventname|start|end from fach2018 needs to be copied into the
table "projects"

I propose a different approach.

On Add/Edit fach2018 run conditions:
Page_Load/Render
if (customer) {
// enable fields: id_fach | eventname | start | ende |
} else if (employee) {
// enable fields: | project_id |, set ReadOnly/Disabled property =TRUE for other fields.
} else {
// none of the above
}

Set fach2018.project_id to lookup projects with allow-add enabled. This way employee can add new project if it's not yet existed.

For the copy thing:
fach2018
Row_Updated() {
if (employee) {
if (| start | ende | is null) { // check if this is a newly added project
ew_Execute("update projects set start=".$rsnew["start"].", ende=".$rsnew["ende"]." where id_project=".$rsnew["project_id"]);
}
}
}

Or you can add more flexibility (I assumed this is a many-to-one scenario):
fach2018
Row_Updated() {
if (employee) {
if (| start | ende | is not null) { // this is existed project
$start = ew_ExecuteScalar("select start from fach2018 where id_project=".$rsnew["project_id"]." order by start ASC limit 1"); // first event
$ende = ew_ExecuteScalar("select ende from fach2018 where id_project=".$rsnew["project_id"]." order by end DESC limit 1"); // last event
ew_Execute("update projects set start=".$start.", ende=".$ende." where id_project=".$rsnew["project_id"]);
}
if (| start | ende | is null) { // this is a newly added project
ew_Execute("update projects set start=".$rsnew["start"].", ende=".$rsnew["ende"]." where id_project=".$rsnew["project_id"]);
}
}
}

PS:
if (customer) can be achieved by phpmaker's CurrentUserLevel(). Search this forum for "CurrentUserLevel".
if (| start | ende | is null) can be achieved by php isnull().

sangnandar · Post by **sangnandar** » Sun Feb 18, 2018 8:32 am

if (| start | ende | is null) is against db (not against form) so you need to first fetch the values from projects.
$row = ew_ExecuteRow("select start,ende from projects where id_project=".$rsnew["project_id"]);
then
if ( isnull($row["start"]) && isnull($row["ende"]) )

But I think you need to reconsider your schema for it contains redundant values. Instead of rewrite start,ende values into table projects you better create a join tableview.

dh1340 · Post by **dh1340** » Sun Feb 18, 2018 7:57 pm

hello, first of all - thanks that you all - had a look on this !

I guess I need to explain better....sorry for that...

actually it seems to me a much simpler thing, as it is only a copying of 3 fields from table " fach2018" to table " projects "
thats it.
This need to be done in the lookup function ADD in table "fach2018" field " project_id"

The situation is that on System 1. (this is fach2018) an external customer puts in the data : name/start/end/ (userlevel customer cant see field project_id)
this data will be set on a field "Status" = reservation. ( as this will be allways on status reservation it cant produce a project automatically )

Than a user with userlevel "Manager" will use the field " project_id" (and select the "project_id" from system 2(projects) !! if exists ) OR the ADD function to make a new project -
after that the user "Manager" can set the status on " confirmed" or not. (depending on other information )

I have this running, but it would be much nicer to copy these 3 fields rather than typing it again.

Later on in System 2 - there will be the table "fach2018" a detail table for table projects as there are much more data esp. for workin times, and basic things for building offers,invoices and so on.

Thank you All
david

sangnandar · Post by **sangnandar** » Sun Feb 18, 2018 8:59 pm

As I said above, when "Manager" do EDIT on fach2018.project_id you can use Row_Updated() server event.

Check if it's a newly added project (I assumed you need to copy the fields only if it's a newly added project). You can skip this if your requirement is to do overwrite whether it's already-existed or newly-added project.
Take the values using $rsnew[<field>].
Write those values into projects using ew_Execute().

But before you do that, you need this:
dh1340 wrote:
if exists ) OR the ADD function to make a new project
By enabling allow-add on the project_id lookup field. This is to make sure that step 3 above will not fired into a non-existed project.

Btw,
What do you mean by "System 1" and "System 2"? Are they different apps?

dh1340 · Post by **dh1340** » Mon Feb 19, 2018 3:39 pm

hello sangnandar,

thanks for your help.

yes there are 2 Apps

the selection of an existing project works fine.

Its now only about the ADD ( yes its enabled in fach2018|project_id ) and works fine...

Ok I ve tried in table fach2018:
// Row Updated event
function Row_Updated($rsold, &$rsnew) {
//echo "Row Updated";
ew_Execute("update projects set projectstart=".$rsnew["start"].", projectend=".$rsnew["ende"]." where proj_nr=".$rsnew["projectID"]);
}

no success...

The behavior should be ... if click on ADD -> new popup Window ( that happens ) the fields in popup : eventname|start| end should be filled from fach2018...
->than confirm ADD -> done

If there is no way, than the have to type it manually....
THX
david

mobhar · Post by **mobhar** » Mon Feb 19, 2018 4:00 pm

This should help you, try it, as I've just tried it and it works properly without having to insert a new record manually typing via Add on-the-fly.

Generate this following sql script to generate the required tables and some data sample inside each table:
/*
Navicat Premium Data Transfer

Source Server : MySQL localhost
Source Server Type : MySQL
Source Server Version : 50617
Source Host : 127.0.0.1:3306
Source Schema : lookuptable

Target Server Type : MySQL
Target Server Version : 50617
File Encoding : 65001

Date: 19/02/2018 14:55:05
*/

-- Begin of tables schema

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for fach2018

DROP TABLE IF EXISTS fach2018;
CREATE TABLE fach2018 (
ID int(11) NOT NULL AUTO_INCREMENT,
Event_Name varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Start datetime(0) NULL DEFAULT NULL,
End datetime(0) NULL DEFAULT NULL,
Project_ID int(11) NULL DEFAULT NULL,
PRIMARY KEY (ID) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- Records of fach2018

INSERT INTO fach2018 VALUES (1, 'First Event', '2018-02-01 14:53:28', '2018-02-02 14:53:28', 1);
INSERT INTO fach2018 VALUES (2, 'Second Event', '2018-02-02 14:53:51', '2018-02-03 14:53:51', 2);

-- Table structure for projects

DROP TABLE IF EXISTS projects;
CREATE TABLE projects (
ID_Project int(11) NOT NULL AUTO_INCREMENT,
Project_Name varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
Start datetime(0) NULL DEFAULT NULL,
End datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (ID_Project) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- Records of projects

INSERT INTO projects VALUES (1, 'First Event', '2018-02-01 14:53:28', '2018-02-02 14:53:28');
INSERT INTO projects VALUES (2, 'Second Event', '2018-02-02 14:53:51', '2018-02-03 14:53:51');

SET FOREIGN_KEY_CHECKS = 1;

-- End of tables schema

Put the following code in "Row_Inserted" server event that belongs to "fach2018" table:

ew_Execute("INSERT INTO projects (Project_Name, Start, End) VALUES ('" . $rsnew["Event_Name"] . "', '" . $rsnew["Start"] . "', '" . $rsnew["End"] . "')");
$Last_ID = ew_ExecuteScalar("SELECT ID_Project FROM projects WHERE Project_Name = '" . $rsnew["Event_Name"] . "'");
ew_Execute("UPDATE fach2018 SET Project_ID = " . $Last_ID . " WHERE Event_Name = '" . $rsnew["Event_Name"] . "'");
And put the following code in "Row_Rendered" server event that belongs to the "fach2018" table:

if (CurrentPageID() == "add" || CurrentPageID() == "edit") {
$this->Project_ID->Visible = FALSE;
}

sangnandar · Post by **sangnandar** » Mon Feb 19, 2018 9:47 pm

Always enable Tools -> Advanced Settings -> Debug when you're running trouble with sql. This is the first place you to look if something goes wrong with your sql.

dh1340 wrote:
ew_Execute("update projects set projectstart=".$rsnew["start"].", projectend=".$rsnew["ende"]."
where proj_nr=".$rsnew["projectID"]);

If start and ende is date datatype you have to enclose them with single qoute, that is: ... set projectstart='".$rsnew["start"]."', ...

dh1340 wrote:
The behavior should be ... if click on ADD -> new popup Window ( that happens ) the
fields in popup : eventname|start| end should be filled from fach2018...
->than confirm ADD -> done

If you want this exact behavior you need to run client-side scripts. Since the NEW ADD is a modal (popup) of the Edit Page, you can catch eventname|start| end from the Edit Page and set that value into allow-add modal box. Use jQuery .val() method. For example:
Startup Script,
$(document).ready(function(){
$("#ewModalDialog").on("shown.bs.modal", function() { // run this code when modal box shown
var val_start = $("#x_start").val() ; // catch start value
$(this).find("#x_start").val(val_start); // set the value into start inside the modal.
});
});

Note:

Have a look which modal container they use to do the allow-add. I'm guessing #ewModalDialog on the above example. In case I'm wrong you should check against browser console (F12) for sure.
You might have id conflict since you have 2 elements with same id="x_start" (1 of the Edit Page, another 1 of the allow-add modal). The this.find("#x_start") should avoid the conflict, but if it doesn't use another element attribute for the $(selector), or change your field name.