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