Many to Many - relationship data entry.

[DLJonsson]

Apache 2.4
MySQL 10.1.33-MariaDB
Ubuntu/Linux
Chrome/Firefox
Php 7.0
DaDaBIK version 8.3-Lerici pro


We are creating an artifacts arrowheads database.

Each arrowhead has bibliographic references in various archeological journals documents,
often arrowheads share bibliographic references.

So we have a many to many schema with intermediary table. - See Image.
https://ibb.co/eNzMVy
https://ibb.co/eNzMVy

How does Dadabik handle data entry of many to many type records?

If many-to-many data entry isn't a feature could we code a customer PHP page to handle this?

 

[eugenio]

Hello,
normally for the intermediary table you set the two foreign key fields as select_single lookup (the linked tables will be aw_main and aw_references), you also need a primary key autoincrement field for aw_main_references that DaDaBIK will use as unique field.

Best,

 

[DLJonsson]

My intermediary table data structure is as above.

In my main table I added a references field.

I'm uncertain though what to input from the Form configurator

Here is what I have entered, but it is not producing usable results on the data entry forms.
I suspect I've made an error. Please clarify.

Choose a table:aw_main
Choose a field: aw_references

Items table name: : aw_main_references [intermediary table]
Items table foreign key field name: aw_main_id [foriegn key]

 

[eugenio]

Hello,
I am not sure what you want to get. The parameters "Items table name" and "Items table foreign key field name" are used to create a master/details view, which is probably not what you want (or not only).

If, as I think, you just need to use your intermediary table by selecting, for each of the two fields, the value from a dropdown menu, you have to choose "select_single" as field type for each of the two fields and then fill all the "lookup" parameters. You can find all the details in the chapter "How to configure a drop-down lookup field" of the documentation.

Best,

 

[DLJonsson]

Here is what I see in your instructions, but it does not make complete sense for my scenario.

Field type: select_single
Lookup table name: cities
Lookup table primary key field: ID
Lookup table linked fields: name


Using Forms Configurator
aw_main [our main data talble]

I created a varchar field named aw_references to connect to the reference table.

chose a table: aw_main
chose a field : aw_references

Field Type specific parameters

Field type: select_multiple_checkbox [since this is a many to many situation]
Result : blank

I also tried
Field type: select_single
Result blank select_single icon no data linked.

So I look at my data structure in MySQL which appears as I remember it from your video in regards to connecting fields and datatables.

ERR Diagram
https://ibb.co/ksTPf8

It makes me wonder a couple of things.

I try then to add a "Cascade filter field"
Still though Select Single widget appears empty.

The objective allow from the Edit Screen 2 things.
1. Data entry to a many-to-many selection of existing reference record/row via a search for the reference
2. Add an additional reference if a link to an existing reference can not be found.

It seems this would be feasible, but I'm unable to navigate in Dadabik menus how to make this work.

Any suggestion appreciated.




Lookup table name: aw_main_has_aw_references
Lookup table primary key field: ID [primary ID as Dadabik specifies]
Lookup table linked fields: aw_main_id, aw_references_id [The 2 fields that in the manytomany relation]


When I go to the Edit screen the AW Reference field does not give me any control.
With select single it is blank

 

[eugenio]

Hello,
what I still don't understand is what you want to get as a final result. Can you describe it in details? I mean something like: I want a page containing fields x,y,z and a field z from which I can choose the references form a drop down menu etc etc.

If you just want to be able, from the page related to aw_main_has_aw_references, to "link" a "main" to a "reference" you need to choose, for aw_main_id , the select_single field type and
Lookup table name: aw_main
Lookup table primary key field: id
Lookup table linked fields: any field in aw_main that you want to be displayed in the menu.
The same for the field aw_references_id (in this case, link it to aw_references).

I don't think the cascade option is what you want, I don't see any typical "cascade" situation here (e.g. you choose a country and the "city" menu changes accordingly).

Best,

 

[DLJonsson]

I want a page containing fields
Item ID
Item Type
Item Reference

From which I can * choose multiple bibliographic references from a drop down menu.
from a bibliographic table that contains.
Ref ID
Author
Title
Publication
Publication date

In the end I need a view/report that lists

Item ID : 1
Item Type : Pottery shard
Item Reference/s
AUTHOR TITLE PUBLICATION DATE
============= ============= ====================== =============
James, John Mayan Digs Mayan Archeology Journal 8/8/2009
Smith,Henry Aztec Temple Aztec Archeology Journal 6/6/2003

and so on....

 

[eugenio]

Ok, now it's clear.

There are two ways:

1) Non-normalized structure:
You use just two tables: aw_main and aw_references, a new field (varchar) of aw_main (e.g. you can call it "references") will contain all the reference IDs. To do this, you have to set the field as "select_multiple_menu" in form configurator and fill all the lookup parameters (the lookup table will be aw_reference)

2) Normalized structure:
You keep the current structure, but you add a VIEW main_references which joins aw_main_has_aw_references AND aw_references.
Then, in form configurator, table aw_main, field id, you add such VIEW in master/details (items table name will be main_references)

Best,

 

[DLJonsson]

Thank you, the non-normalized structure you recommend works perfectly for this scenario.

 

[eugenio]

Ok. Please note that with the solution 1) you will see all the references in a field (I recommend to set "User-friendly searchable?" and "Use ajax to load options? " to YES).
If you want to see the list of the references in a results grid under the main item (master/details), you need the solution 2).

 

[larryk]

Hi,
I'm working on a same/similar situation:

1) Non-normalized structure:
when updating/inserting this structure, it seems easy? But what happens when the foreign key is not required? As in not ever record in the main has another related record?

2) Normalized structure:
This is the typical many to many... BUT how is the other table inserted (and then how does the intermediary get populated?)

Not sure how Dadabik handles the updates/inserts?

thanks

 

[eugenio]

Hi,

1) You can just leave the field empty

2) I am not sure if I understood your question: if you have an intermediary table you can just insert records there as you do with the other tables

Best,

 

[larryk]

on 2)

but it needs to be a "transaction"? as in
table1 --> intermediary <-- table2


table2 is the main.
table1 is the other entity.

So when user is creating a new Main record and this new Main record also has the Other entity, Yes, you need to create table1 record first, but then...
HOW is the intermediary table inserted? The 2 primary keys that need to be inserted... who or how is that done via Dadabik? It is not automated or is it? I've not done this with Dadbik... but need to

I'm assuming I have to write the code myself to insert into Table1 AND into the intermediary table?

 

[larryk]

Hi Eugenio,

Hopefully, this question/issue will make sense to you
I'm trying to follow your example (#2) above by making a view

Simple DB Example:

Home Table has
ID
house_name

Home_Offer Table
ID
Home_ID
Offer_ID

Offer Table
ID
Offer_Amount


My goal is to have the Home form configuration table, SHOW all the Offers the house gets AND
you can click/edit the displayed Offer records.

So to do this, I made a view:

Create view HomeOffers as select home.ID, offer.ID, offer.Offer_Amount from home, home_offer, offer
where home.ID = home_offer.Home_ID and Home_Offer.Offer_ID = Offer.ID


SO everything is displayed perfectly...
I go to Home, you see home fields and the MASTER/DETAILS to see the HomeOffers view (works great too)

So I click to see/edit the Offer record... it works

BUT, WHEN I TRY TO Save the Offer data, I get this error:


The DBMS server said: SQLSTATE[HY000]: General error: 1393 Can not modify more than one base table through a join view


Is there a solution?
How can I do the above?
I need to see all the many-to-many records AND be able to edit them.

it seems the view can't do it?

I could just go to the Offer table, BUT THEN I can't see WHICH home the offer belongs too?
That defeats the purpose.