New nuBuilder User

[mkingston]

Hi. I want to evaluate nuBuilder for a new project. I currently have a huge integrated application, which is an old fat client. I need to redevelop it as a web application. Before I get started with nuBuilder, I have a couple of questions. With nuBuilder, is it possible to join multiple tables for inquiry and reporting? Also, is it possible to use a single form for input, which adds to multiple tables? Finally, when adding an entry to a table, is it possible to populate a field with a generated, sequential, unique value, for example an order number or invoice number?

Thanks,

Merrill Kingston

[steven]

Merrill,
I'm sure you'll find nuBuilder will do everything you want, we have beeen using it for a number of years and we often use it to do all the things that you require, but I'll go through them one at a time and point you in the right direction.

--------- Question 1 ---------
is it possible to join multiple tables for inquiry and reporting?

Yes.

Reporting:Firstly nuBuilder has a built in drag and drop report GUI for building reports which is explained in Tutorial 15 : Reports and Tutorial 18: Report Criteria and Hash Variables. Using the PHP Data Code section, you'll be able to (with PHP and SQL) create a temporary (flat) table that will be used by the report builder to display your report.
If need be, this flat table can be created using a complex combination of SQL INSERTs and JOINs and UPDATEs etc.

So there is very little you can't do with this report writer.

Inquiry:Another great method of creating a way to quickly find a record(s) based on a number of criteria, is to build a Form who's Search Screen holds all of the fields you may need to filter on and then use the Search field at the top of the screen to filter what you want.

This list can be made of a number of joined tables.

--------- Question 2 ---------
is it possible to use a single form for input, which adds to multiple tables?

Yes.

Either a Form will automatically save information on its subform eg Invoice Form with a Subform with Invoice Items.
OR
After saving an invoice, by placing some PHP and SQL in the After Save text area of the Invoice Form other tables can be updated like Ledger tables etc.

--------- Question 3 ---------
is it possible to populate a field with a generated, sequential, unique value

Yes.

We dont use autoIDs (in tables displayed in nuBuilder Forms) for a few reasons but..

You can
(A)
After saving an invoice, update the invoice number field with a AutoID from a table called something like next_number, in the After Save text area of the Invoice Form.

OR
(B)
The Default Value SQL field on the Text Tab of a Object Form can pre-populate a field eg. an invoice number, with SQL.
eg.
SELECT next_number_id FROM next_number WHERE nxn_key = '#TT#'

and to increment this number by 1 before getting it, you can place some SQL code in the SQL Run Before Display text area of the Object Form.
eg.
INSERT INTO next_number SET nxn_key = '#TT#'


The advantage of (A) is that it is unique and sequential and never loses a number.
The disadvantage is that you don't know the number until it has been saved.

The advantage of (B) is that you know the number before its saved.
The disadvantage is that it is unique and sequential and but can skip numbers if the new record never gets saved.

An example of (B) is in the Invoice Number on the Invoice Form, of Sample Debtors Module.

I hope this helps.

regards
Steven Copley

[mkingston]

Steven:

Your reply has helped a great deal! I'm certain I'll have other questions as I move forward. This application is pretty complicated and some of the validation requirements will get a little dicey. Steven, I'm sorry that I'm not technically qualified to contribute much to the project, but I'll do my best to offer ideas and suggestions.

Thank you very much,

Merrill

[steven]

I look forward to it.

Steven