Subform problem - legacy db tables

[n9yty]

I'm ashamed that I can't figure this out, I have a long DB history, but something here is puzzling and I can't quite get "behind the covers" to see what is going wrong. So I am hoping someone may be able to offer some help.

This is for a legacy database that I am trying to create a simple viewer application for. It has many tables of related data, and unfortunately they used the same field name for the key in all the tables. I did read in the user guide that "If a Subform's Form has an Object with the same ID as the Subform's Foreign Key, Subform records cannot be saved.", but I am not planning on any saving, just viewing, so I thought that may be a non-issue.

However, I can't get anything in the subform to display, and after watching videos I just can't seem to quite figure out how this is supposed to work.

Assume I have one table with the donor name/address information with the donor_id field. I have a from filling a browse form that is searchable, and an "edit" form with all the fields being either marked read-only or Display fields pulling in related data from some other tables.

I want to create a subform that displays the donor giving history. The gifts table uses the same donor_id field name. I think this is the problem, but I see no errors in the debug log, but no data fills in the subform.

I created an object on the form of type subform, and in the Subform section I picked the subform, the foreign key is donor_id and I have set Addable/Deleteable as No, Type as Grid.

In the Forms editor, I have set the gifts table name, the primary key is set to donor_id. For Browse, I have the columns for donor_id (don't really need it for display here), the gift date and gift amount with the database field names for them. In the SQL tab I have a query set to retrieve those fields from the gifts table with a where condition that the donor_id is equal to #RECORD_ID#.

Can someone please tell me just how badly I've messed this up and how to make It work? There are many other tables of related donor information, but they would all follow this same pattern, so if I get this to work the rest of the project should be fairly simple.

[admin]

n9yty,

Can you show us the structure of the tables you are using for
1) The Form
2) The Subform

and any screenshots that might help explain things.


Steven

[n9yty]

I noticed this in the console log:

Uncaught TypeError: SFR.forms[0].tabs[0] is undefined
nuSUBFORM http://127.0.0.1/~n9yty/nubuilder_sga/n ... 22021:1298
nuBuildEditObjects http://127.0.0.1/~n9yty/nubuilder_sga/n ... 022021:451
nuBuildForm http://127.0.0.1/~n9yty/nubuilder_sga/n ... 022021:114
successCallback http://127.0.0.1/~n9yty/nubuilder_sga/n ... 022021:118
success http://127.0.0.1/~n9yty/nubuilder_sga/n ... 9022021:17
jQuery 6
c
fireWith
l
o
send
ajax
nuAjax http://127.0.0.1/~n9yty/nubuilder_sga/n ... 19022021:8
nuForm http://127.0.0.1/~n9yty/nubuilder_sga/n ... 022021:123
nuSelectBrowse http://127.0.0.1/~n9yty/nubuilder_sga/n ... 22021:3789
onclick http://127.0.0.1/~n9yty/nubuilder_sga/index.php:1
nuform.js4


Here are the definitions:

Main Form:
Type: Browse and Edit
Code: DNR_NameInfo
Description: Donor Lookup
Table Name: namf
Primary Key: id_code
Tabs: Main Order=10, Gifts Order=20

Browse:
SQL: SELECT id_code, title, first_name, last_name, suffix, city, state, zip, phone_number, name FROM name
Columns:
ID Code, id_code, Left, , 250, 10
Title, title, Left, , 250, 20
First Name, first_name, Left, 250, 30
Last Name, last_name, Left, , 250, 40
City, city, Left, , 250, 50
Postal Code, postal_code, Left, , 250, 70
Phone Number, phone_number, Left, , 250, 80

Custom Code:
nuSetTitle($('#name').val());
(This was working, stopped when I added the subform, works if I remove the subform and the error shown above goes away)

Gift Form:
Type: Subform
Code: GFT_Listing
Description: Gift Listing
Table Name: hstf
Primary Key: id_code
Tabs: Main Order=10

Browse:
SQL: SELECT id_code,gift_date, gift_amount, type_of_dollar FROM hstf WHERE id_code = #RECORD_ID#

Columns:
ID Code, id_code, Left, , 50, 100
Gift Date, gift_date, Left, dd-mm-yyyy, 100, 20
Gift Amount, gift_amount, Left, $ 1000.00, 100, 30

In the Main Form's Form Objects I have the subform defined as:

Form Tab: Gifts - Donor Lookup
Type: Subform
Label: Gifts
ID: gifts_sf
Top: 10 Width: 500. Cloneable: Yes Validation: None
Left: 50 Height: 500 Align: Left Access: Editable

Subform:
Form: GFT_Listing - Gift Listing
Foreign Key: id_code
Addable: No
Deletable: No
Type: Grid

After I added the subform Form Object to the Main Form the Javascript error returned in the console, so clearly something I am doing isn't right.

[admin]

n9yty,

Code: Select all

SELECT id_code,gift_date, gift_amount, type_of_dollar FROM hstf WHERE id_code = #RECORD_ID#

Try removing your WHERE clause (nuBuilder's Subform will create the relationship to the main Form you need automatically by using the foreign key).

fore.png

Steven

[n9yty]

I set up another form and a related subform, created all from scratch (no legacy), and they worked fine. I did note that the browse SQL did not have the WHERE condition, so I removed that, same results and the same (I think) Javascript error in the console:

Uncaught TypeError: SFR.forms[0].tabs[0] is undefined
nuSUBFORM http://127.0.0.1/~n9yty/nubuilder_sga/n ... 22021:1298
nuBuildEditObjects http://127.0.0.1/~n9yty/nubuilder_sga/n ... 022021:451
nuBuildForm http://127.0.0.1/~n9yty/nubuilder_sga/n ... 022021:114
successCallback http://127.0.0.1/~n9yty/nubuilder_sga/n ... 022021:118
success http://127.0.0.1/~n9yty/nubuilder_sga/n ... 9022021:17
jQuery 6
nuAjax http://127.0.0.1/~n9yty/nubuilder_sga/n ... 19022021:8
nuForm http://127.0.0.1/~n9yty/nubuilder_sga/n ... 022021:123
nuSelectBrowse http://127.0.0.1/~n9yty/nubuilder_sga/n ... 22021:3789
onclick http://127.0.0.1/~n9yty/nubuilder_sga/index.php:1
nuform.js4

In this new test case, not based on any legacy tables, I set the foreign key and primary fields as unique and it had no problem. When I changed the primary and foreign keys to a field name that was in both tables (id_code) I do not see the above Javascript error, but I no longer can add entries to either the main form or the subform.

[n9yty]

Back on the primary key... While the main form seems to work fine having specified the existing legacy table primary key, the field is INT 11 in MySQL, if I make a new table (or change the one created by Fast Forms) so that the primary key is an INT 11 it really doesn't seem to like it, it seems it likes a VARCHAR 25 as it creates.

While the main form does seem to work with the legacy data, could it be causing an issue for the subform?

[kev1n]

Back on the primary key... While the main form seems to work fine having specified the existing legacy table primary key, the field is INT 11 in MySQL, if I make a new table (or change the one created by Fast Forms) so that the primary key is an INT 11 it really doesn't seem to like it, it seems it likes a VARCHAR 25 as it creates.

While the main form does seem to work with the legacy data, could it be causing an issue for the subform?

if I'm not mistaken, nuBuilder doesn't like Auto-increment PKs in subforms.

[n9yty]

Just to be clear, everything in this DB will be read-only of legacy data, nothing should be created. I don't know if that matters or not.

I think I found one mistake I made, on the gifts subform I put the id_code as the primary key which isn't right, as that table will have many entries for the id_code. I set It to the rowid which is an auto increment, but again, I am not going to allow any changes to the table so saving/updating shouldn't be an issue.

[admin]

n9yty,

kev1n is correct but it shouldn't matter for something is readonly.

If you can upload a version of your db, we'll take a look.


Steven

[n9yty]

Another strange thing... I changed the example DB I made entirely in nuBuilder... I changed the primary key fields to integers of the same size as my legacy database, and I changed the foreign key fields to have the same names and also the same size INTs as in my legacy DB. It works fine for displaying, but it won't add because it is trying to push a VARCHAR key into the INT column.

That Javascript error is interesting, as I don't see that on the new tables I created, only when the screen with the subform tries to load for the legacy solution, and it saying the form object doesn't exist makes me wonder if something isn't being created properly. I removed the subform object from the main form and deleted that subform entirely, then re-created them, seeing no errors along the way, but with the same end results.