Use added user field(s) as variables for forms sql statement

Questions related to using nuBuilder Forte.

Use added user field(s) as variables for forms sql statement

Postby nub74 » Mon Jul 22, 2019 6:58 am

Hi to all,
I am new to nuBuilder and although I have searched in many topics
I have not found a simple answer to my problem. I added a new field (Department) to the users table and its form and a new field (Level) to the access table and its form.
Now I need to use the values ​​of these fields as variables for the sql statements of some forms by loading them on hash variables or in any other way
that makes this goal possible. How can I get this result?
Thanks to all.
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: Use added user field(s) as variables for forms sql state

Postby admin » Mon Jul 22, 2019 7:28 am

nub74,

If you change system tables (zzzzsys*) you will not be able to do any updates to your system without losing the changes you have made.

You should create separate tables that relate to the zzzzsys_user and zzzzsys_access tables.


Steven
admin
Site Admin
 
Posts: 3224
Joined: Mon Jun 15, 2009 9:53 am

Re: Use added user field(s) as variables for forms sql state

Postby nub74 » Mon Jul 22, 2019 7:49 am

Steven,
thanks for your quick response. I will surely do as you suggested.
Now going back to the my question: how can I use the values of these fields
as variables for the sql statements of my forms?
Thank you.
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: Use added user field(s) as variables for forms sql state

Postby admin » Mon Jul 22, 2019 8:10 am

admin
Site Admin
 
Posts: 3224
Joined: Mon Jun 15, 2009 9:53 am

Re: Use added user field(s) as variables for forms sql state

Postby nub74 » Tue Jul 23, 2019 4:31 am

Hi Steven,
somehow I reached my goal using the #USER_ID# variable within subqueries.
Now I need to ask for another suggestion: I have to fill a field using the variable #USER_ID# as default value;
I tried to follow the following topic viewtopic.php?f=19&t=9549&p=17210&hilit=default+field+value#p17210
but if I replace the word 'hello' with the variable #USER_ID# I get nothing.
How should I proceed?
Thanks a lot.
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: Use added user field(s) as variables for forms sql state

Postby Janusz » Tue Jul 23, 2019 5:45 am

Hi,
The #USER_ID# you can use in PHP.
In JS you can use:
Code: Select all
var x=nuGetProperty('user_id');
$('#con_user').val(x).change();

if you want directly user name you can use:
(for the globeadmin there is empty field so thats why it is used: if (nuUser === null) { nuUser = "admin";})
Code: Select all
function getUser() {
   var nuUser = nuUserName();
   if (nuUser === null) { nuUser = "admin";}
   return nuUser;
}

$('#con_user').val(getUser()).change();


to see the available properties open the Console with F12 and type in:
nuCurrentProperties()

to get the value for given parameter use in JS:
nuGetProperty('user_id')
(you can type in as well in the console to display)
https://wiki.nubuilder.net/nubuilderfor ... Javascript
Janusz
 
Posts: 280
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Use added user field(s) as variables for forms sql state

Postby nub74 » Tue Jul 23, 2019 6:52 am

Hi Janusz,
thank yoy for your response.
The code:

var x=nuGetProperty('user_id');
$('#con_user').val(x).change();

works correctly but:

1) when I save, focus always come back to the updated field (con_user) and save button become red again
2) saved value is not those is saw in add form

how should I proceed?
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: Use added user field(s) as variables for forms sql state

Postby Janusz » Tue Jul 23, 2019 7:15 am

Hi,
Apply this just for new record as following:
Code: Select all
if(nuCurrentProperties().record_id == '-1')
{
var x=nuGetProperty('user_id');
$('#con_user').val(x).change();
}


but not sure for your second question.
please give more details
Janusz
 
Posts: 280
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Use added user field(s) as variables for forms sql state

Postby nub74 » Tue Jul 23, 2019 10:35 pm

Hi Janusz,
now the code for the default values works correctly. Thank you.
I have to return to the matter of the fields used as variables for the slq statements (see my first post).
I thought I could solve with the subqueries but it is not so. I followed the post that Steven proposed to me
(viewtopic.php?f=20&t=9953) but I have to set as property not a fixed value
but one read from a field of other table. How can I do this?
Thanks in advance.
nub74
 
Posts: 20
Joined: Sun Jul 21, 2019 7:39 pm
Location: Italy

Re: Use added user field(s) as variables for forms sql state

Postby Janusz » Tue Jul 23, 2019 11:55 pm

Hi,
I do not know any details of your code so can only show general example how to read a value from any table inside the php code.
You can easilly use any mysql query and get any data you want from your tables.
Maybe it will help somehow.
Code: Select all
$table="rejestr";       // table name
$f1="rej_frozen";       // field from table
$f2="rej_month_closed"; // field from table
$id=$table."_id";       //rekord ID - table index
$r=64;  // just any existing index for example
$t="SELECT ".$f1.",".$f2." FROM ".$table." WHERE ".$id."=".'"'.$r.'"';
$x=nuRunQuery($t);
$y = db_fetch_object($x);
$f1v = $y->$f1;
$f2v = $y->$f2;

nuDebug($f1v,$f2v);  // just see in the debug list
Janusz
 
Posts: 280
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Next

Return to General