[Solved] SQL defs for junction table

[gkick]

Hi,

With reference to the recent topic below

viewtopic.php?f=39&t=99711

All working perfect, however
I am stuck migrating it all bits to a HSQL2.5 backend. Forms and queries fine, the tables I created manually, but the add edit form now throws up an error message, ref integrity null in non nullable....

Suspect its some index, have compared the systemtables constraints and they are different between embedded and split.
Guess I need to create the junction table via sql, now the embedded db does not feature a script file like the backend where I could grab the relevant statement from.

Would appreciate some guidance how to clone the SQL create table syntax of the junction table tblContactsActivities to ensure all the indexes are correct
The split version is available here:

https://www.dropbox.com/s/v0b0h0n2rosdj ... 5.rar?dl=0
Thanks

[Villeroy]

"ref integrity null in non nullable." The error message tells everything you need to know. You try to save an incomplete record. Some field that is defined as NOT NULL is going to be stored with a NULL value. The database engine rejects this record. This has nothing to do with the HSQL version.

[gkick]

yes I am aware of that, but seem not to be able to create that index using the gui.

[Villeroy]

I'm not sure what you try to achieve. Let's assume you want to filter the activities by contact (parent form) and by activity types.
This would require a many-to-many form where the subform has 2 parents, the contact and the type. However, a subform has only one parent.
I added 2 forms to your Base document. The first one is just the ordinary many-to-many form for the relation between contacts and activities. The activity types are indicated in the activity names as in "Rotary Club (Affiliations)".

The second form has another logical "TopForm" on top of the "MainForm". In the forms navigator I dragged the "MainForm" onto the "TopForm". When using the form, you select a type in the top form, then a contact in its subform and the sub-subform shows only activities of the selected type. How did I do that?

1) TopForm is linked to the types table.

2) MainForm is linked to a SELECT statement which includes the contacts table plus an additional field "ParamType" which is filled up by a parameter value :pType.

Code: Select all

SELECT "tblContacts".*, :pType AS "ParamType" FROM "tblContacts"

This way I include the parent form's selected type into the second form's record set without doing anything with that value.

3) The sub-subform selects all fields from 2 related tables:

Code: Select all

SELECT "tblContactsActivities".*, "tblActivities".* FROM "tblContactsActivities" "tblContactsActivities", "tblActivities" "tblActivities" WHERE "tblContactsActivities"."fk_aid" = "tblActivities"."aid"

This grandchild form is filtered by the parent form's contact-ID and by the field "ParamType" which actually comes from the grandparent form. This parameter trick passes a value through a child form so it can be used by the grandchild.

The same can be done with the help of a filter table which permanently stores the criteria value. Without the extra table I could avoid changing the backend.

[gkick]

@ Villeroy, thank you for your alternative design and my apologies for not been clear enough.
I have an embedded version which I converted to a backend db.

If you look at the attached mockup_embedded frmContacts, then hit the button it will take you to the frmActivityAddEdit form.

Everything on this form is working, you can add, delete, change categories, activities and assignments of activities to contacts.
All working perfectly.

So the purpose is to select a contact first, then select a category and show the activities for the selected contact.
All fine and tested, now I want to put this functionality into my split db

When I move all the forms, queries and tables to external, I get that index problem and I am missing some step somewhere.

The Junction table (embedded version) has a compound pk and two additional sys.... indexes.
First time round copying the table the wizard lost the two sys... indexes, therefor I created the junction table again in design view, first creating the compound pk, save and add the other indexes.
Relationships all the same.

However the only difference is that I changed the contacts cid to nid and applied that change to all instances in forms, queries and sql statements in table controls. No error messages.
So I am at a loss as to why it works in the embedded version but not in the split db. Hope this explains it a bit more.

Looking at your form2 I also end up with an error message albeit a different one (see attached)

[Villeroy]

Best I can do for now. Still not perfect. Base has no built-in tools for this.
[attachment removed]

[Villeroy]

The form for new records was child of the SubForm. It works much better if it is child of the MainForm. I also added a reference to my AutoRefresh.py. Now you pick a type, hit Enter to store it and all forms and list boxes are restricted to the activities of the selected type and person.
If you select an activity which already belongs to the person you get a duplicate error. There is a cancel button to undo form entry.

[chrisb]

@gkick,
with reference to your initial post:
you need to check the linked fields in the internal form 'F_Store_ContactsActivities'.

sometimes it's almost impossible to design a conventional form which is both reliable & user friendly.
base forms demand that row data be altered or added in order to enable a save.
the main issue that you have is that data is not altered or added (the values you wish to save are passed as parameters by parent forms).
as a workaround you have added a boolean field to your table & a checkbox to your form.
the workaround makes the form clumsy to use & may produce orphans (unnecessary data).

i think that using a macro driven solution may be justified here.
the attachment contains two small macros & a filter table. the form is small, clean & simple.
i dropped the field "btrigger" from the table "tblContactsActivities".

[gkick]

Thank you all for your combined efforts, time and help. Two month and a bit into my journey with Base its refreshing to see that there almost always is a solution or a work around to real or perceived shortcomings.
I appreciate the rapid response time, the enthusiasm and willingness to share of you guys. I mark this now as solved.