Can you expand on what you want with quite a bit more detail? People don't even use "many to many relationship" to mean the same thing in all cases.
Do you mean this type of relationship?
And from table 1, you want to see a conditional value list based on values from Table 2 with the relationship filtering the values that you get in the value list?
Yes, that should be possible.
This is my graph. I learned how to make a screenshot, so it is not translated
OPERACJE is my main table, when I track every income and outcome.
KODY PRZYCHODU is my table with categories for incomes and KODY ROZCHODU is table with categories for outcomes. I set it up according to official tutorial (here) but it is many to many relationship. I don't know if it is ok or I did something wrong.
This part of my database I use to track what is real. There is also second part of my database. Babel's name is BUDŻET PRZYCHODY. I use it to plan my budget. There is relationship between OPERACJE and BUDŻET PRZYCHODY where
OPERATION DATE = BUDGET YEAR
OPERATION CATEGORY PRIMARY = BUDGET CATEGORY PRIMARY
OPERATION CATEGORY SECONDARY = BUDGET CATEGORY SECONDARY
OPERATION_CUSTOMER FK = BUDGET_CUSTOMER FK
And with that I have some problems:
1. I couldn't use this tables with categories KODY PRZYCHODU and KODY ROZCHODU with my BUDGET table. I made a new Table Occurrence KATEGORIE PRZYCHODU and KATEGORIE ROZCHODU and now it works.
2. This relation between OPERATIONS (OPERACJE) and BUDGET INCOMES (BUDŻET PRZYCHODY) is many to many. Now everything works but I'm afraid that in the future there can be an error which will make my database useless.
My Apple Pencil is ready, I can repost this with translation
Can you upload a copy of your database?
Did you take a look at my sample files?
I prefer not to create spiderwebs.
It will probably not be useless but when you want more tables and more relationships in your database in the future, I think it will be a big challenge to manage.
I'm still looking at your files. I love this kind of keeping things in order, but it is something new, so I have to figure out everything
Technically, these are not many to many relationships unless you are storing a list of values in one of the match fields used in these relationships.
What the "many" connectors on each end of the relationship line indicate is that you haven't set up a field option on the match field that would prevent duplicate values. Many solutions work just fine with such as long as users don't/can't enter duplicates on the "one" side of your relationship, but it would be safer to add a unique values validation option to the match fields in your category tables.
This may be stupid question, but I have to ask:
I have a TABLE with 3 TABLE OCCURRENCES. If I add a new record in one of them it will be ADDED in all TO but eventually it won't be SEEN in all of TO? Did I understand it properly?
You don't add records to a table occurrence, you add them to a table. The table occurrence just establishes the context and might affect the auto-entry of data into certain fields due to that context--such as entering a foreign key value because you are adding the record though a relationship from a related occurrence--for example. Think of each table occurrence (box in your relationship graph) as a convenient label that refers to a table on the tables tab of Manage | Relationship.
And thus any records added are there no matter which table occurrence. Whether a given record is visible depends on context. If you base a layout on any one of the three occurrences, ALL of the records may be viewed on that record no matter what values are entered into a given field unless you take additional steps in your interface design to exclude them.
But if you access the data in one of these occurrences from the context of a layout based on a table occurrence related to one of these three, that relationship could keep some records from being visible. An example would be if you placed a portal to one of these three table occurrences on a layout based on a different table occurrence.