Is it possible to import data into a subform from a file?

Unread post by **kev1n** » Tue Jun 09, 2020 8:46 am

That sounds complex. You probably need good PHP and JavaScript skills.

To add a row:
https://wiki.nubuilder.cloud/ ... t#nuAddRow

Unread post by **Janusz** » Tue Jun 09, 2020 1:22 pm

Hi,
Using PHP function works very well if you want to update subform. The update is directly on the mysql level.

Starting with JS on the specific form you prepare some data to be sent to PHP.
In the php function you can update whatever table - access any data from other sources, ...
and at the end of the JS just to perform nuGetBreadcrumb();
and everything is refreshed.

Code: Select all

....
nuSetProperty('CUR_PART_ID',$('#parts_id').val());
nuSetProperty('INSERT_INTO_RAPORTS', q);
nuSetProperty('QTY_OF_RAPORTS', qx);
nuSetProperty('LIST_OF_RAPORTS', arr_uid);
nuSetProperty('CON_PART_ID', id);
...
nuRunPHPHidden('INSERT_INTO_CONNECTIONS','z');
....
nuGetBreadcrumb();

and just an example from one of my DB of the PHP function ('INSERT_INTO_CONNECTIONS')

Code: Select all

$pid=('#CON_PART_ID#');
$rqt=('#QTY_OF_RAPORTS#');
$lista=('#LIST_OF_RAPORTS#');
$cur_id='#CUR_PART_ID#';

$arr_lista = explode(',',$lista);

for ($i = 0; $i <= $rqt; $i++) {
$rap_id=$arr_lista[$i];

$uid=nuID();
$x="INSERT INTO connection (connection_id,con_part,con_rap) VALUES ('$uid','$pid','$rap_id')";

nuRunQuery($x);
}

$q="SELECT sus_name FROM zzzzsys_user WHERE zzzzsys_user_id='#USER_ID#';";
$w = nuRunQuery($q);
$r  = db_fetch_object($w)->sus_name;
if ($r=='') $r='admin';
$q="UPDATE parts SET par_updated_by = '$r' WHERE parts_id='$cur_id';";
$w = nuRunQuery($q);
//nuDebug($q);

unset($pid,$rqt,$cur_id,$s,$t,$start,$i,$x,$q,$w,$r,$uid,$rap_id,$arr_lista);

Unread post by **Janusz** » Tue Jun 09, 2020 1:39 pm

and additionally be carefull with a subform with to many lines
you need to set appropriatelly in the php conf file the max_input_vars
bigger numer of lines slows much the application
max subform lines is probaly 999.

kknm · Unread post by **kknm** » Tue Jun 09, 2020 2:03 pm

Janusz wrote:and additionally be carefull with a subform with to many lines
you need to set appropriatelly in the php conf file the max_input_vars
bigger numer of lines slows much the application
max subform lines is probaly 999.

Thank you for the attention. I learned a little php and already got working code using phpExcell. Stumbled on adding lines to a subform. How to add a line to a subform in php? I would be grateful for the help.

Code: Select all

<!doctype>
<html>
<head>
</head>
<body>
<?php
require_once './Classes/PHPExcel.php';
$tmpfname = '/home/igor/Загрузки/2020_06_08_N_gm.xls';
$excelReader = PHPExcel_IOFactory::createReaderForFile($tmpfname);
$excelObj = $excelReader->load($tmpfname);
$worksheet = $excelObj->getSheet(0);
$lastRow = $worksheet->getHighestRow();
echo '<table border="1">';
for ($row = 13; $row <= $lastRow; $row++) {
	if ($worksheet->getCell('E'.$row)->getValue() <>"") {
		echo '<tr><td>';
		echo $worksheet->getCell('E'.$row)->getValue();
		echo '</td><td>';
		echo $worksheet->getCell('J'.$row)->getValue();
		echo '</td><td>';
		echo $worksheet->getCell('S'.$row)->getValue();
		echo '</td><tr>';
	}	
}
echo '</table>';
?>
</body>
</html>

Unread post by **kev1n** » Tue Jun 09, 2020 2:39 pm

Then how do you want to run this PHP? On button click in your form?

Unread post by **Janusz** » Tue Jun 09, 2020 2:47 pm

First identify the subform mysql table name.
It is composed typically:
subform_row_uniq_id, mainform_id, ... any data...

where mainform_id defines the link with main form
(so all lines from subform table with mainform_id equal to Primary Key of the main form are displayed on the specific form)

to insert from php you can use ex.:
$x="INSERT INTO connection (connection_id,con_part,con_rap) VALUES ('$uid','$pid','$rap_id')";
nuRunQuery($x);

But first you can try with phpMyAdmin/SQL to insert manually one line to the table to see if the subform is updated.
You can copy/duplicate one existing line updating only subform row unique ID
(it must have new subform row unique ID, the same mainform_id to define the link, and other data can be the same or changed)
After doing that go to nuBuilder, refresh, and the new line will be in the subform.

Unread post by **Janusz** » Tue Jun 09, 2020 2:48 pm

and it can be run on buton click for example

kknm · Unread post by **kknm** » Tue Jun 09, 2020 2:51 pm

kev1n wrote:Then how do you want to run this PHP? On button click in your form?

Yes, I have to run php on the main form with a button and get the field value (tab_data = '2020-06-05 08:00:00') on the main form to replace '-' with '_' and '08: 00: 00 ' To 'D' or '20: 00: 00 'to' N ', then I will add the values from the file to the subform.
So I present the script.

kknm · Unread post by **kknm** » Tue Jun 09, 2020 2:55 pm

Janusz wrote:First identify the subform mysql table name.

to insert from php you can use ex.:
$x="INSERT INTO connection (connection_id,con_part,con_rap) VALUES ('$uid','$pid','$rap_id')";
nuRunQuery($x);

It seems to me that working with a table will become more complicated due to the lack of a "new subform row unique ID" in excell.

Unread post by **Janusz** » Tue Jun 09, 2020 3:11 pm

the current form Primary key can be easilly sent from JS to PHP and used in mysql
so you collect data from excel; adding the current record Primary key, inserting data to table and thats all

Code: Select all

//get id of the current main record (JS)
var id=nuGetProperty('record_id');

// prepare to be available in php (JS)
nuSetProperty('CON_PART_ID', id);

//recover and use in PHP (PHP)
$pid=('#CON_PART_ID#');
$uid=nuID();
$x="INSERT INTO connection (connection_id,con_part,con_rap) VALUES ('$uid','$pid','$rap_id')";

and for adding subform unique id use nuID(); (for more records - in the loop - every line - has to have new nuID generated)

nuBuilder Forum

Is it possible to import data into a subform from a file?

Re: Is it possible to import data into a subform from a file

Re: Is it possible to import data into a subform from a file

Re: Is it possible to import data into a subform from a file

Re: Is it possible to import data into a subform from a file

Re: Is it possible to import data into a subform from a file

Re: Is it possible to import data into a subform from a file

Re: Is it possible to import data into a subform from a file

Re: Is it possible to import data into a subform from a file

Re: Is it possible to import data into a subform from a file

Re: Is it possible to import data into a subform from a file