Subform customization

Subform customization

Postby Ion2 » Fri Feb 12, 2016 1:39 am

Hi,

I am struggling with the subforms. Is it possible to only use it as a lookup? I tried to make it "Read Only":

SubForm01.png
SubForm01.png (47.08 KiB) Viewed 5557 times

and I found the option "Addable" and "Deleteable":

SubForm02.png
SubForm02.png (47.81 KiB) Viewed 5557 times

but I see no difference and are still able to change the field content.

Furthermore I tried to change the layout of the subform. "Tab", "Col", Ord", "Top" and "Left" seems to be the right adjusting options. I thought it might be possible to display one dataset in multiple rows, like it is possible in the report builder. It did not work:

SubForm03.png
SubForm03.png (23.69 KiB) Viewed 5557 times

I am missing the description of the subforms in the wiki. This is what I found something about the Wizard: http://wiki.nubuilder.net/nubuilderv3/i ... orm_Wizard

eg: Where to change the "Width" after closing the Subform Wizard?

I hope to get some help!

Thank you,
Timo
Ion2
 
Posts: 41
Joined: Mon Jan 18, 2016 11:31 pm

Re: Subform customization

Postby admin » Fri Feb 12, 2016 11:35 am

Timo,

What do you mean by this?

I am struggling with the subforms. Is it possible to only use it as a lookup?


I thought it might be possible to display one dataset in multiple rows


You could try this..

Capture.PNG
Capture.PNG (20.75 KiB) Viewed 5555 times


I am missing the description of the subforms in the wiki.


Did you see this? http://wiki.nubuilder.net/nubuilderv3/index.php/Adding_Objects#Subform_Tab

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

Re: Subform customization

Postby Ion2 » Sat Feb 13, 2016 12:12 am

Great! Thank you so much Steven!

This is exactly what I wanted! :D

Thank you also for the "Next Button" reply!

I have one last question (so far): How to do the inner join to the zzzsys_user table? (http://forums.nubuilder.com/viewtopic.php?f=13&t=8866)

Greetings, have a nice weekend and thank you so much for this great piece of software! :)

Timo
Ion2
 
Posts: 41
Joined: Mon Jan 18, 2016 11:31 pm

Re: Subform customization

Postby admin » Tue Feb 16, 2016 7:14 am

Ion2,

Inner joins join 2 or more tables together to create the relation in a relational database, a standard part of SQL http://www.w3schools.com/sql/sql_join_inner.asp

Capture.PNG
Capture.PNG (19.75 KiB) Viewed 5536 times


Code: Select all
SELECT * FROM zzzzsys_object
JOIN zzzzsys_form ON zzzzsys_form_id = sob_all_zzzzsys_form_id


(There is always something new to learn.)

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

Re: Subform customization

Postby Ion2 » Tue Feb 16, 2016 11:15 pm

Dear Steven

I still didn't get it. I know JOINs, but do not know how to integrate them in this case. The reason might be that SQL is new to me, but I am willing to learn.

Let me explain the issue. These columns track the changes in the table (great nuBuilder feature)

  • zzzsys_user_log_added_by
  • zzzsys_user_log_changed_by
  • zzzsys_user_log_viewed_by

by the zzzsys_user_id. This ID is related to the sus_name in the table zzzsys_user. Now I want to display the sus_name instead of the zzzsys_user_id. By doing a JOIN of my table inventory and the zzzsys_user table I could link the rows of the two tables by matching zzzsys_user_log_changed_by with zzzsys_user_id. Calling sus_name will now always give the same result but zzzsys_user_id could be different for the three columns mentioned above.

Can I integrate the SELECT/JOIN statement in the Browse Column Display?
user_01.png
user_01.png (31.74 KiB) Viewed 5534 times

Can I integrate it in the Display Condition of the Form Object?
user_02.png
user_02.png (28.76 KiB) Viewed 5534 times


I very much hope to get a solution for that. :)

Thank you very much,
Timo
Ion2
 
Posts: 41
Joined: Mon Jan 18, 2016 11:31 pm

Re: Subform customization

Postby admin » Wed Feb 17, 2016 8:42 am

Timo,

Sorry but its not my job to teach you SQL.
Part of your problem is that for someone that doesn't know SQL you are trying to do a lot.
Start with something simpler with just a couple of tables, and once you've worked that out try something harder.

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

Re: Subform customization

Postby Ion2 » Thu Feb 18, 2016 1:15 am

Dear Steven,

this was a drastic answer. Of course it is not your job to teach me SQL. What I am saying is just that the "inner join" recommendation you gave (http://forums.nubuilder.com/viewtopic.php?f=13&t=8866) might not be the correct relation on the form level because there is no 1to1 relation between them. You have three columns in one table that link to different rows in the zzzsys_user table:

  • zzzsys_user_log_added_by
  • zzzsys_user_log_changed_by
  • zzzsys_user_log_viewed_by

My simple question was, if it is possible on the display level of the data to make a relation to the sus_name?

An alternative might be to write the sus_name in my table "Before Save" (in Custom Code). I just believe this is not most clever method.

I am very grateful for nuBuilder. I think it is a great product.

Timo
Ion2
 
Posts: 41
Joined: Mon Jan 18, 2016 11:31 pm

Re: Subform customization

Postby admin » Thu Feb 18, 2016 8:33 am

Timo,

To get the names from zzzsys_user_log_added_by, zzzsys_user_log_changed_by and zzzsys_user_log_viewed_by, you need to inner join to the zzzsys_user table 3 times using aliases.

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

Re: Subform customization

Postby Ion2 » Thu Feb 18, 2016 6:37 pm

Steven,

got the hint! This is working in PHP MyAdmin:

Code: Select all
SELECT inv.*
     , zu1.sus_name AS log_added_by_name
     , zu2.sus_name AS log_changed_by_name
  FROM inventory inv
       LEFT JOIN zzzsys_user zu1
          ON inv.inventory_log_added_by = zu1.zzzsys_user_id
       LEFT JOIN zzzsys_user zu2
          ON inv.inventory_log_changed_by = zu2.zzzsys_user_id


The output is a table with all my inventory columns and at the end it adds two columns named log_added_by_name and log_changed_by_name.

By useing this SELECT STATEMENT in the Form and calling log_added_by_name in the Browse Column gives the following error message:

field_not_found.png
field_not_found.png (49.15 KiB) Viewed 5518 times

Do you have a small hint for that?

Also on the details page I was not lucky. Might "Display On" be the issue?

field_not_found_02.png
field_not_found_02.png (41.51 KiB) Viewed 5517 times

Thank you,
Timo
Ion2
 
Posts: 41
Joined: Mon Jan 18, 2016 11:31 pm

Re: Subform customization

Postby Ion2 » Thu Feb 18, 2016 9:53 pm

Small update:

By using the following code it is possible to display log_changed_by_name in the Browse Columns:

Code: Select all
SELECT inv.*
     , log_added_by_name
     , log_changed_by_name
  FROM inventory inv
       LEFT JOIN (SELECT zzzsys_user_id, sus_name AS log_added_by_name FROM zzzsys_user) zu1
          ON inv.inventory_log_added_by = zu1.zzzsys_user_id
       LEFT JOIN (SELECT zzzsys_user_id, sus_name AS log_changed_by_name FROM zzzsys_user) zu2
          ON inv.inventory_log_changed_by = zu2.zzzsys_user_id

Still I didn't find a solution to display log_changed_by_name on the Details page.

Any ideas?

Thank you,
Timo
Ion2
 
Posts: 41
Joined: Mon Jan 18, 2016 11:31 pm

Next

Return to General



cron