I'm very new to Alpha Five, and may be missing something very simple, but I just can't figure out out to get data entry for a M-M relationship to work properly.
I've searched through all the documentation I can find, and this looked the most promising: http://support.alphasoftware.com/alp..._Many_Sets.htm, however I'm sure this example (taken fromm the AlphaSports database) is backwards! The ERD in English reads "Each Customer_Product can be for many customers" and "Each CustomerProduct can contain many products". This is the opposite of the real situation, which is that "Each Product my appear in many Customer_Products" and "Each customer may have many Customer_Products".
In other words, the intersection table in a M-M relationship is the M side, not the 1 side.
All this aside, my problem is as follows:
I have a table called Animals, which has a unique identifier animal_id. I also have a table called Treatment, which has a unique identifer treatment_id. I need to record for any specific date, which animals received which treatment, so I have a table called animal_treatment which contains animal_id, treatment_id and treatment_date. So the relationship is:
animal<--->>animal_treatment<<--->treatment
As far as I can see, I can't create a workable set from the above, due to the problem mentioned in my opening comment. i.e, Alpha Five requires that the primary table be the 1 side of a 1-M, and there can only be one primary table.
I can create a form using the animal_treatment table with lookups for the animal and treatment tables, however this is not functional, as on any given day, a thousand animals may receive up to ten treatments each in different combinations. The above approach would require 10,000 form entries.
What I would like to be able to do is somehow create a form which lists all of the records in the animal table with a means of selecting all, none or some of them, and similarly for the treatment table. So, for example, if I select 20 animals and 3 treatments, and click a button, 60 new records will be added to the animal_treatment table.
If anyone has done something similar, or can offer an example I could study I was be very grateful.
I've searched through all the documentation I can find, and this looked the most promising: http://support.alphasoftware.com/alp..._Many_Sets.htm, however I'm sure this example (taken fromm the AlphaSports database) is backwards! The ERD in English reads "Each Customer_Product can be for many customers" and "Each CustomerProduct can contain many products". This is the opposite of the real situation, which is that "Each Product my appear in many Customer_Products" and "Each customer may have many Customer_Products".
In other words, the intersection table in a M-M relationship is the M side, not the 1 side.
All this aside, my problem is as follows:
I have a table called Animals, which has a unique identifier animal_id. I also have a table called Treatment, which has a unique identifer treatment_id. I need to record for any specific date, which animals received which treatment, so I have a table called animal_treatment which contains animal_id, treatment_id and treatment_date. So the relationship is:
animal<--->>animal_treatment<<--->treatment
As far as I can see, I can't create a workable set from the above, due to the problem mentioned in my opening comment. i.e, Alpha Five requires that the primary table be the 1 side of a 1-M, and there can only be one primary table.
I can create a form using the animal_treatment table with lookups for the animal and treatment tables, however this is not functional, as on any given day, a thousand animals may receive up to ten treatments each in different combinations. The above approach would require 10,000 form entries.
What I would like to be able to do is somehow create a form which lists all of the records in the animal table with a means of selecting all, none or some of them, and similarly for the treatment table. So, for example, if I select 20 animals and 3 treatments, and click a button, 60 new records will be added to the animal_treatment table.
If anyone has done something similar, or can offer an example I could study I was be very grateful.
Comment