[Solved] 2 Many to One relationships together in forms

[ps_jayadev]

Dear all,

I am new to this forum as well as using Open Office Org. I am using the latest version of OOo 3.4

I am facing an issue in writing data to the table from a form. I went through many posts in the forums but in vain. Finally I got tired and am writing this post. I would be very grateful if anyone can give me a solution for this issue.

The problem I am facing is:

I am trying a develop a database of rates of items of different projects which has the following tables - fields:

1) type of Project - Project Type
2) Projects List - Project ID, Project Name, Project Type
3) Category List - Category ID , Category Name
4) Items List - Item ID, Category ID, Item Name, Item Unit
5) Rates List - Project ID, Item ID, Rate per unit, comments

The rates are unique for each of the items under different projects.

There is a fixed list of types of projects. I created a form to add and modify projects under those projects. I created a form to add/modify categories and add/modify items.

Now I am trying to create a form to add/modify item rates. I want to select an category & item and Project type & project from the existing list and add a rate to that particular item under that project. And later also be able to modify rates by choosing an item & project. It is a many to one and one to many relation.

I could not find a way to create such a form. I am also fine with creating 2 forms and linking them both but I want to add a rate without the necessity of entering Project Id or Item ID.

I even tried using macros but I could get the required function.

Somebody please provide me a solution for this issue.

thank you in advance.
Jayadev.

[Villeroy]

http://user.services.openoffice.org/en/ ... 00&t=40444
... and many other tutorials and examples under http://user.services.openoffice.org/en/ ... m.php?f=83

[Arineckaig]

ps_jayadev requested:

Somebody please provide me a solution for this issue.

Welcome to the forum. I would generally recommend against resorting to macros even though they could certainly provide a solution: the AOO API is a steep learning curve. I offer a hastily prepared alternative to illustrate some of the Base options to which Villeroy pointed you. The demo assumes that there are not too many 'Projects' that would make selection from the list box in “Rates” data form unwieldy.

I do not offer the demo as a solution but merely as an illustration, so please come back if you require further information. On the other hand please ignore it and accept my apologies, if it offers no assistance.

[ps_jayadev]

Thank you for a promt reply. But the the total number of items and projects are not as less as you assumed. The number of projects would run into hundreds and the number of items would run into thousands. Consequently the rates table would contain records which you be equal to the multiplication of previous two numbers. Hope you understand the magnanimity of the database now.

I would be very grateful if you can provide me a solution. Please give me an idea and I will work out the rest.

I am attaching the file which I created till now.

For adding rates, I created 2 forms - one to select a project and then another to add/modify rates to particulars items in that project. I linked them both but I am not able to link the 3 forms (Projects List, Items List and Rate List).

Please help me. Thank you.
Jayadev

[ps_jayadev]

Also to add

1) The rate must be unique for a given item and given project. So the form should prevent multiple entries for the same item and project.
2) The form should also allow modification of the existing rate for a given item and given project.

[Arineckaig]

Please give me an idea and I will work out the rest.

See Villeroy's post above.

You will need to go the dummy table route that he describes: possibly more than one such table. The idea is to create a "Rates" data form that is filtered by the content of two fields (projectID and ItemID) of a particular record selected in the dummy table. In turn selection of that particular record will require another data form (possibly a parent form) where the choice of Projects will have been filtered by Type and the choice of Items filtered by Category.

Later Edit: Having now downloaded your database you may need to give some thought to its design given its potential size: in particular, the normalization that may be required to maintain its data integrity and avoidance of excess redundancy. Secondly, given the potential size and importance of the databse, it may well be economic to go the macro route and devote time to mastering the AOO API. For guidance, I would recommend the posts of DACM - such as http://user.services.openoffice.org/en/ ... 00&t=42845.

[Arineckaig]

ps_jayadev asked:

I am not able to link the 3 forms (Projects List, Items List and Rate List).
1) The rate must be unique for a given item and given project. So the form should prevent multiple entries for the same item and project.
2) The form should also allow modification of the existing rate for a given item and given project.

To supply these specific requirements, I have added a simple form document, "DEMO Rates Form", to your database file of which a revised copy is now attached.

The added form document is merely a simpler version of the one suggested in my original post. It contains a single data form that is sourced from the "Rates List" table but linked through list boxes to display user friendly data from the other two tables. The form permits updates to fields in the "Rates List" table, but as the latter uses the two ID fields as primary keys duplicates should be prevented: an alternative (and IMHO preferable) method of preventing duplicates would be to apply to that table a 'unique' index comprising those two fields.

[ps_jayadev]

Thank you Arineckaig for your valuable inputs. Where can I get to study about these dummy tables in detail?

I am going through the posts you recommended. I already tried a similar form like DEMO Rates form but considering the number of records, it would be very tough for the end user to use such a form.

I am trying to work with the macros but as you know it is a time taking process. Please let me know if you have a better solution for this. If at all I can get to know the structure which would solve this issue, I will start working accordingly.

Thank you.
Jayadev

[Arineckaig]

Where can I get to study about these dummy tables in detail?

Villeroy is the prime source and expert on this subject - you can do no better than to start with the sites to which he referred you: search also for his frequent posts in this forum on many-to-many relationships. Similarly DACM has submitted several useful and knowledgable posts on this subject: his site to which I referred you is a good starting point.

If you wish to start with a crude introduction, you may find the "Power Filtering" section on page 16 of a PDF tutorial and demo file, that can be downloaded from http://db.tt/BIJeyMQ, provides a possible starting point. Meanwhile I will try to work up an example that I will add in due course to my first demo above.

[Arineckaig]

I have now added a form document, "FilterRates", to my previous demo file. Again Its layout is crude but hopefully illustrates the use of cascading dummy filter tables whereby selection of 'Projects' can be filtered by 'Type' and 'Items' by 'Category'. In turn, selections from these filtered list boxes will determine the content of the 'Rates' sub-form. If a NULL is selected from the initial Type or Category list boxes the effect is to remove that particular filter so that all the Items or Projects will be displayed in the Rates sub-form list boxes.

In this demo I have deliberately used 'Action' buttons to update the various data forms for selection changes so as to illustrate the process. In its final format simple 'Event' macros would be preferable for this purpose.