Getting Started Questions

[icoso]

I've created several Database applications using MySQL, SQL Server, MariaDB as backends, and PHP, Javascript, HTMl, front ends along with VB and C++ desktop based frontends, for SQL Server based DBs. However, Im New to NuBuilder. One simple question that I have is in what tables does nuBuilder use to create the forms etc. I kind of assumed that I would create my own tables, ie a customer table, with my own fields and indexes, relational tables, lookup tables, etc. and then use Nubuilder to create the forms, reports, and user interface. to access those tables. Basic functions such as insert/edit/delete, browse, and reports, exports.

Or does nuBuilder not use my own tables that I've created in the database but instead create the tables on the fly or stored the necessary fields in its own tables zzzAbc_Def_??? as you design a form in the form builder?
Im a little confused by this.

From what I've read in the user guide. I need to first create the forms that my end users will see. That was the first hurdle Im having is that I've already created my tables but I dont' see anywhere in the Form Builder where I select or link in the table from the database. Am I missing something here? Any guidance would be helpful.

[icoso]

OK, I see what it did. It created the table for me as I added field names to the Fast Form Builder. It generated an Index Customer_ID automatically and set all the fields at VarChar(1000).
Next Questions:

For fields such as Phone Numbers, Zip Code, SSNs, etc, State (2 Char), Can I adjust these using myPHPAdmin tool on my host? Since these fields are fixed length numeric or 2 char fields. Will that affect the way nuBuilder stores data in those fields?

Should or Can patterns be applied to the format of the fields in the Display, Browse and Edit Forms or should these fields be stored in the Table with the formatting applied? IE: a phone number should appear as (555) 555-5555 or a Zip Code can be: 12345-9999. I typically do not store the patterns of these types of fields in the table but handle the formatting of the field on the forms themselves. IE: a phone umber is stored as 5554443333 but appears as (555) 444-3333 on a form.

Can SSN fields or any other field (address, Birth Date, etc. be automatically encrypted (birth dates would have to be stored as Varchar instead of dates) as its stored in the table? IE: an SSN becomes 09#AJ3$43RJF8J0J!2E09UIJ&DF34949 as it appears in the table. This would would affect the fields being displayed on the forms. They would need to be encrypted before being stored in the table, then after being retrieved from the table but before being displayed on a forms or reports.

What about relational linking with my customer table? I now have a customer table, but I want to add an invoice table that links to the customer table via the Customer_ID field. I would normally create these tables on my on using my own data definitions and then build the relational linkage within my own applications. Does nuBuilder do this for you on Forms? How? or Where in the manual/wiki is this described on how to do it?

Thank you. Any help or guidance is appreciated....

[apmuthu]

Please read the various Tutorials where step by step instructions are in the wiki and as a sticky topic of the forum as well.

There are quite a few Video Tutorials too.

Display and Input formatting are stored in the respective system tables (zzzzsys_%) such as format, form, browse and report tables.

You can start with your own set of tables or have nuBuilder Forte create them on the fly for you with standard field types that you may change (do not change the default ID field). All nuBuilder application tables must necessarily have VARCHAR(25) field type for the primary key ID if auto generated in the globeadmin mode. Existing tables with other field types like AUTO_INCREMENT INT fields must have their ID fields generated for new records with BeforeSave PHP code or use NULL for AUTO_INCREMENT definitions in the DB itself.

Alternatively, the primary key ID (auto generated or manually made) should follow the VARCHAR(25) convention and keep a unique (INT or other) key for what would have been their primary key outside of nuBuilder.

Wikied.

[kev1n]

For fields such as Phone Numbers, Zip Code, SSNs, etc, State (2 Char), Can I adjust these using myPHPAdmin tool on my host? Since these fields are fixed length numeric or 2 char fields. Will that affect the way nuBuilder stores data in those fields?

Yes, you can change the column type, field size.

Should or Can patterns be applied to the format of the fields in the Display, Browse and Edit Forms or should these fields be stored in the Table with the formatting applied? IE: a phone number should appear as (555) 555-5555 or a Zip Code can be: 12345-9999.

Formatting is just applied when a field is displayed. It stays unformatted in the database.

Can SSN fields or any other field (address, Birth Date, etc. be automatically encrypted (birth dates would have to be stored as Varchar instead of dates) as its stored in the table?

Check out this topic
https://forums.nubuilder.cloud/viewtopic. ... ypt#p21542

[icoso]

Check out this topic
https://forums.nubuilder.cloud/viewtopic. ... ypt#p21542

Kev1n, Thanks for the Info. I had read that post earlier. Didn't quite understand what was going on at first. Im getting an error when running your updated SQL script.

The Error is:
INSERT INTO `zzzzsys_object` VALUES ('5f33e61a68a6913','5f33e61a6136434','encrypt_data','input','enc_password','Password','5f33e61a61725e3',0,50,150,100,20,'1','left','0','0','','','','','','','','','','','','','','','','','','','','','','','',0,'','text','','','','','','','','')

MySQL said: Documentation
#1136 - Column count doesn't match value count at row 1

I checked all the columns and it appears that my sys_object table is different from yours. I have two different fields than yours. They are:

`sob_select_2` varchar(1) DEFAULT '0',
which is after : `sob_select_multiple` varchar(1) DEFAULT NULL,

`sob_input_datalist` text DEFAULT NULL,
which is after: `sob_input_javascript` text DEFAULT NULL,

Should I modify your SQL script to input blanks on these fields?

Also in the zzzzsys_php table the script was missing the following field (2nd from last)
`sph_global` varchar(1) DEFAULT NULL,
which is after: `sph_system` varchar(1) DEFAULT NULL,

Should I modify your SQL script to input NULL on this field? What about any of the PHP code that's inserted or any other fields in any other tables that may be affected by these different fields?

Lastly, How do I use these changes to to encrypt specific fields in my tables? Do Need to use the Before Save,Edit,Browse sections to add in some PHP to encrypt/decrypt accordingly?

[icoso]

Thanks....

[kev1n]

I checked all the columns and it appears that my sys_object table is different from yours. I have two different fields than yours. They are:

2 additional columns have been added in v 4.5.

Should I modify your SQL script to input NULL on this field?

Yes, you could do that.

Lastly, How do I use these changes to to encrypt specific fields in my tables? Do Need to use the Before Save,Edit,Browse sections to add in some PHP to encrypt/decrypt accordingly?

That's the way to do it, IMO.